Setting a Month Criteria

[Ken Hudson]

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));

 

[Tom Lake]

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

 

[John W. Vinson]

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)

 

[Dale Fye]

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

 

[KenSheridan via AccessMonster.com]

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))","tblControl")
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

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));

 

[John Spencer]

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]

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

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)

 

[Ken Hudson]

Thanks Dale.
Seems to work perfectly.

--
Ken Hudson

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

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));

.

 

[John W. Vinson]

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!

 

[Ken Hudson]

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

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]

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.

Sorry... I misread "following" for "preceding"! Glad you got it working.