Setting a Month Criteria

I have a table (tblData) with dates as one of the fields.
I have another "control" table (tblControl) into which I have recorded a
date, e.g. 12/31/09.
In a query I am trying to set a criteria that will use the date in
tblControl and retrieve data from tblData that has a date anytime in the
month following the date in tblControl. The following works okay until we get
to December. Then I assume that it is not working because it is looking for
month "13."
How do I change the criteria to retrieve January data if December is in the
tblControl?

Here is what is curently in my criteria row:

Month(DLookUp("dteRptDate","tblControl",1))+1

SELECT
FROM tblData
WHERE
(((Month([dteOrderDate]))=Month(DLookUp("dteRptDate","tblControl",1))+1)) ;

--
Ken Hudson

"Ken Hudson" wrote in message
...
I have a table (tblData) with dates as one of the fields.
I have another "control" table (tblControl) into which I have recorded a
date, e.g. 12/31/09.
In a query I am trying to set a criteria that will use the date in
tblControl and retrieve data from tblData that has a date anytime in the
month following the date in tblControl. The following works okay until we get
to December. Then I assume that it is not working because it is looking for
month "13."
How do I change the criteria to retrieve January data if December is in the
tblControl?

Here is what is curently in my criteria row:

Month(DLookUp("dteRptDate","tblControl",1))+1

SELECT
FROM tblData
WHERE
(((Month([dteOrderDate]))=Month(DLookUp("dteRptDate","tblControl",1))+1)) ;

DateAdd("m",1, DLookUp("dteRptDate","tblControl",1))

This adds one month onto the date and rolls over if the year changes.

Tom Lake

On Fri, 15 Jan 2010 09:12:01 -0800, Ken Hudson
wrote:

I have a table (tblData) with dates as one of the fields.
I have another "control" table (tblControl) into which I have recorded a
date, e.g. 12/31/09.
In a query I am trying to set a criteria that will use the date in
tblControl and retrieve data from tblData that has a date anytime in the
month following the date in tblControl. The following works okay until we get
to December. Then I assume that it is not working because it is looking for
month "13."
How do I change the criteria to retrieve January data if December is in the
tblControl?

Here is what is curently in my criteria row:

Month(DLookUp("dteRptDate","tblControl",1))+1

SELECT
FROM tblData
WHERE
(((Month([dteOrderDate]))=Month(DLookUp("dteRptDate","tblControl",1))+1)) ;

Try using the DateSerial function instead. No calculated field is needed; you
can use a criterion directly on the date field of

= DateSerial(Year([tblControl].[dteRptDate]), Month([tblControl].[dteRptDate]-1, 1) AND DateSerial(Year([tblControl].[dteRptDate]), Month([tblControl].[dteRptDate], 1)

--

John W. Vinson [MVP]

If tblControl only has one record, you could use:

SELECT *
FROM tblData, tblControl
WHERE tblData.dteOrderDate
BETWEEN tblControl.dteRptDate
AND Dateadd("m", 1, tblControl.dteRptDate)

or you could use a non-equi join (although you can only do this in SQL view)

SELECT *
FROM tblData
INNER JOIN tblControl
ON tblData.dteOrderDate = tblControl.dteRptDate
AND tblData.dteOrderDate Dateadd("m", 1, tblControl.dteRptDate)

Dale

"Ken Hudson" wrote in message
...
I have a table (tblData) with dates as one of the fields.
I have another "control" table (tblControl) into which I have recorded a
date, e.g. 12/31/09.
In a query I am trying to set a criteria that will use the date in
tblControl and retrieve data from tblData that has a date anytime in the
month following the date in tblControl. The following works okay until we
get
to December. Then I assume that it is not working because it is looking
for
month "13."
How do I change the criteria to retrieve January data if December is in
the
tblControl?

Here is what is curently in my criteria row:

Month(DLookUp("dteRptDate","tblControl",1))+1

SELECT
FROM tblData
WHERE
(((Month([dteOrderDate]))=Month(DLookUp("dteRptDate","tblControl",1))+1)) ;

--
Ken Hudson

You'll also need to compare the years as otherwise you'll get the rows for
the previous January in December. If the query does not need to be updatable
a JOIN can be used;

SELECT tblData.*
FROM tblDataINNER JOIN tblControl
ON (tblData.dteOrderDate BETWEEN
DATESERIAL(YEAR(tblControl.dteRptDate),
MONTH(tblControl.dteRptDate)+1,1)
AND DATESERIAL(YEAR(tblControl.dteRptDate),
MONTH(tblControl.dteRptDate)+2,0));

or for an updatable query:

SELECT tblData.*
FROM tblData
WHERE YEAR(dteOrderDate) = DLOOKUP(
"YEAR(DATEADD(""m"",1,dteRptDate))","tblContro l")
AND MONTH(dteOrderDate) = DLOOKUP(
"MONTH(DATEADD(""m"",1,dteRptDate))","tblControl") ;

or:

SELECT tblData.*
FROM tblData
WHERE YEAR(dteOrderDate) = DLOOKUP(
"YEAR(DATESERIAL(YEAR(dteRptDate),
MONTH(dteRptDate)+1,1))","tblControl")
AND MONTH(dteOrderDate) = DLOOKUP(
"MONTH(DATESERIAL(YEAR(dteRptDate),
MONTH(dteRptDate)+1,1))","tblControl");

Ken Sheridan
Stafford, England

Ken Hudson wrote:
I have a table (tblData) with dates as one of the fields.
I have another "control" table (tblControl) into which I have recorded a
date, e.g. 12/31/09.
In a query I am trying to set a criteria that will use the date in
tblControl and retrieve data from tblData that has a date anytime in the
month following the date in tblControl. The following works okay until we get
to December. Then I assume that it is not working because it is looking for
month "13."
How do I change the criteria to retrieve January data if December is in the
tblControl?

Here is what is curently in my criteria row:

Month(DLookUp("dteRptDate","tblControl",1))+1

SELECT
FROM tblData
WHERE
(((Month([dteOrderDate]))=Month(DLookUp("dteRptDate","tblControl",1))+1)) ;


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/201001/1

SELECT *
FROM tblData
WHERE (((Month([dteOrderDate]))=
(Month(DLookUp("dteRptDate","tblControl")) Mod 12)+ 1

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Ken Hudson wrote:
I have a table (tblData) with dates as one of the fields.
I have another "control" table (tblControl) into which I have recorded a
date, e.g. 12/31/09.
In a query I am trying to set a criteria that will use the date in
tblControl and retrieve data from tblData that has a date anytime in the
month following the date in tblControl. The following works okay until we get
to December. Then I assume that it is not working because it is looking for
month "13."
How do I change the criteria to retrieve January data if December is in the
tblControl?

Here is what is curently in my criteria row:

Month(DLookUp("dteRptDate","tblControl",1))+1

SELECT
FROM tblData
WHERE
(((Month([dteOrderDate]))=Month(DLookUp("dteRptDate","tblControl",1))+1)) ;

Hi John,
I got a wrong number of arguments error with that criteria. I tried to add
closing parens (looks like we need two of them) but I still got the error.
Maybe I put them in the wrong place?

--
Ken Hudson


"John W. Vinson" wrote:

On Fri, 15 Jan 2010 09:12:01 -0800, Ken Hudson
wrote:

I have a table (tblData) with dates as one of the fields.
I have another "control" table (tblControl) into which I have recorded a
date, e.g. 12/31/09.
In a query I am trying to set a criteria that will use the date in
tblControl and retrieve data from tblData that has a date anytime in the
month following the date in tblControl. The following works okay until we get
to December. Then I assume that it is not working because it is looking for
month "13."
How do I change the criteria to retrieve January data if December is in the
tblControl?

Here is what is curently in my criteria row:

Month(DLookUp("dteRptDate","tblControl",1))+1

SELECT
FROM tblData
WHERE
(((Month([dteOrderDate]))=Month(DLookUp("dteRptDate","tblControl",1))+1)) ;

Try using the DateSerial function instead. No calculated field is needed; you
can use a criterion directly on the date field of

= DateSerial(Year([tblControl].[dteRptDate]), Month([tblControl].[dteRptDate]-1, 1) AND DateSerial(Year([tblControl].[dteRptDate]), Month([tblControl].[dteRptDate], 1)

--

John W. Vinson [MVP]
.

Thanks Dale.
Seems to work perfectly.

--
Ken Hudson


"Dale Fye" wrote:

If tblControl only has one record, you could use:

SELECT *
FROM tblData, tblControl
WHERE tblData.dteOrderDate
BETWEEN tblControl.dteRptDate
AND Dateadd("m", 1, tblControl.dteRptDate)

or you could use a non-equi join (although you can only do this in SQL view)

SELECT *
FROM tblData
INNER JOIN tblControl
ON tblData.dteOrderDate = tblControl.dteRptDate
AND tblData.dteOrderDate Dateadd("m", 1, tblControl.dteRptDate)

Dale

"Ken Hudson" wrote in message
...
I have a table (tblData) with dates as one of the fields.
I have another "control" table (tblControl) into which I have recorded a
date, e.g. 12/31/09.
In a query I am trying to set a criteria that will use the date in
tblControl and retrieve data from tblData that has a date anytime in the
month following the date in tblControl. The following works okay until we
get
to December. Then I assume that it is not working because it is looking
for
month "13."
How do I change the criteria to retrieve January data if December is in
the
tblControl?

Here is what is curently in my criteria row:

Month(DLookUp("dteRptDate","tblControl",1))+1

SELECT
FROM tblData
WHERE
(((Month([dteOrderDate]))=Month(DLookUp("dteRptDate","tblControl",1))+1)) ;

--
Ken Hudson


.

On Fri, 15 Jan 2010 14:35:01 -0800, Ken Hudson
wrote:

Hi John,
I got a wrong number of arguments error with that criteria. I tried to add
closing parens (looks like we need two of them) but I still got the error.
Maybe I put them in the wrong place?

Or I did: it should be

= DateSerial(Year([tblControl].[dteRptDate]), Month([tblControl].[dteRptDate])-1, 1) AND DateSerial(Year([tblControl].[dteRptDate]), Month([tblControl].[dteRptDate]), 1)

Sorry!

--

John W. Vinson [MVP]

Thanks John.
I needed to modify that function a bit.
I wanted to find dates that were in the month following the date in
tblControl, so I changed the -1 to +1 and added a +2 to the second part of
the function.

--
Ken Hudson


"John W. Vinson" wrote:

On Fri, 15 Jan 2010 14:35:01 -0800, Ken Hudson
wrote:

Hi John,
I got a wrong number of arguments error with that criteria. I tried to add
closing parens (looks like we need two of them) but I still got the error.
Maybe I put them in the wrong place?

Or I did: it should be

= DateSerial(Year([tblControl].[dteRptDate]), Month([tblControl].[dteRptDate])-1, 1) AND DateSerial(Year([tblControl].[dteRptDate]), Month([tblControl].[dteRptDate]), 1)

Sorry!

--

John W. Vinson [MVP]
.