Selecting A single Month From a Table with Multiple Months

[shane]

The following code is used as the criteria in a selct query for a date field:

=DateSerial(Year(Date()),Month(Date())-1,1) And <=DateSerial(Year(Date()),Month(Date()),0)

It has worked fine until the table included records from January 2010,
database was created in 2009. Is there a fix or a better way to accomplish
the intent?

 

[John Spencer]

Not sure what your problem is the expressions you have return the first and
last day of the prior month. So this is February 3, 2010 and the expression
return 1/1/2010 and 1/31/2010.

What do you want returned? What is the problem you are having?

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

 

[John W. Vinson]

The following code is used as the criteria in a selct query for a date field:


It has worked fine until the table included records from January 2010,
database was created in 2009. Is there a fix or a better way to accomplish
the intent?

What specific problem did you have? what records did you see that you didn't
want, or vice versa?

As written, if you ran it today, it would retrieve records for January 2010;
if you had run it during January, it would retrieve records for December 2009.
Is that not what you're seeing?

 

[Daryl S]

Shane -

The problem would always occur when the current date is in January, because
you were keeping the Year of the date the same in the first part of the
criteria. This would have produced the criteria >= 12/1/10 AND <= 12/31/09,
which would always be false. Try this instead, which subtracts a month from
the first of the current month:

 

[John Spencer]

Daryl,

DateSerial adjusts correctly.

For instance,
DateSerial(2010,1-1,1) returns December 1 2009.

The expressions as posted work to give you the begin and end date of the month
prior to the current month.

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

 

[Daryl S]

John -

My bad. I was focused on the year, even though I know the zeros are handled
properly.

I don't know why this wouldn't work...