Setting a Month Criteria

  • Thread starter Thread starter Ken Hudson
  • Start date Start date
K

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));
 
Ken Hudson said:
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));
Click to expand...

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

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

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));
Click to expand...

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)
Click to expand...
 
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
 
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
 
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 said:
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));
Click to expand...

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)
Click to expand...
Click to expand...
 
Thanks Dale.
Seems to work perfectly.

--
Ken Hudson


Dale Fye said:
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 said:
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));
Click to expand...


.
Click to expand...
 
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?
Click to expand...

Or I did: it should be
= DateSerial(Year([tblControl].[dteRptDate]), Month([tblControl].[dteRptDate])-1, 1) AND < DateSerial(Year([tblControl].[dteRptDate]), Month([tblControl].[dteRptDate]), 1)
Click to expand...

Sorry!
 
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 said:
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?
Click to expand...

Or I did: it should be
= DateSerial(Year([tblControl].[dteRptDate]), Month([tblControl].[dteRptDate])-1, 1) AND < DateSerial(Year([tblControl].[dteRptDate]), Month([tblControl].[dteRptDate]), 1)
Click to expand...

Sorry!
Click to expand...
 
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.
Click to expand...

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