dates in queries

[Paul]

Hello
I have an Access DB with a table in it with a 'Short Date' date/time field
defined among others. I am running a query against it to return data between
#7/1/09# and #7/31/09#.

However, it is missing several records all on the last day of the month
(7/31). It appears the records the query is missing have a time stamp in them
in addition to the date.

Is there a function I can use to return all the correct records within a
given time period or change the field to another type so that the query works?

Paul

 

[John W. Vinson]

Hello
I have an Access DB with a table in it with a 'Short Date' date/time field
defined among others. I am running a query against it to return data between
#7/1/09# and #7/31/09#.

However, it is missing several records all on the last day of the month
(7/31). It appears the records the query is missing have a time stamp in them
in addition to the date.

Is there a function I can use to return all the correct records within a
given time period or change the field to another type so that the query works?

Paul

I'd suggest using a criterion like

= #7/1/09# AND < #8/1/09#

or (better) creating a little unbound form frmCrit with textboxes txtStart and
txtEnd, and using a criterion

= IIF(IsDate(Forms!frmCrit!txtStart), DateValue([Forms!frmCrit!txtStart], #1/1/100#) AND < IIF(IsDate(Forms!frmCrit!txtEnd), DateAdd("d", 1, Forms!frmCrit!txtEnd), #12/31/9999#)

This will return all records before txtEnd if txtStart is NULL or is not a
date, and all records after txtStart if txtEnd is NULL or is not a date - if
you want non-dates handled differently, adjust the IIF arguments.

 

[Paul]

Thanks John. That will do it.

Hello
I have an Access DB with a table in it with a 'Short Date' date/time field
defined among others. I am running a query against it to return data between
#7/1/09# and #7/31/09#.

However, it is missing several records all on the last day of the month
(7/31). It appears the records the query is missing have a time stamp in them
in addition to the date.

Is there a function I can use to return all the correct records within a
given time period or change the field to another type so that the query works?

Paul

I'd suggest using a criterion like

= #7/1/09# AND < #8/1/09#

or (better) creating a little unbound form frmCrit with textboxes txtStart and
txtEnd, and using a criterion

= IIF(IsDate(Forms!frmCrit!txtStart), DateValue([Forms!frmCrit!txtStart], #1/1/100#) AND < IIF(IsDate(Forms!frmCrit!txtEnd), DateAdd("d", 1, Forms!frmCrit!txtEnd), #12/31/9999#)

This will return all records before txtEnd if txtStart is NULL or is not a
date, and all records after txtStart if txtEnd is NULL or is not a date - if
you want non-dates handled differently, adjust the IIF arguments.

 

[Paul]

Thanks for the info Ken. Both methods appear to work for the usual month time
frames I am interested in for the query.

Paul

Paul:

As John has described, the bullet-proof way to return rows within a date
range is to define the range as on or later than the start date, and before
the day following the end date. The reason for this is that there is no such
thing as date value per se in Access, only a date/time value, so #7/31/09#
means midnight at the start of 31 July 2009. Consequently any rows with a
date/time value of that date with a non-zero time of day will not be returned.


As you appear to be returning rows for a particular month, however, another
way would be:

WHERE YEAR([YourDateField]) = 2009
AND MONTH([YourDateField]) = 7

In query design view you'd do this by entering Year([YourDateField]) in the
'field' row of a blank column in the query design grid and 2009 in its
'criteria' row, and Month([YourDateField]) in the 'field' row of another
blank column and 7 in its 'criteria' row. Uncheck the 'show' check box in
each case.

Ken Sheridan
Stafford, England

Hello
I have an Access DB with a table in it with a 'Short Date' date/time field
defined among others. I am running a query against it to return data between
#7/1/09# and #7/31/09#.

However, it is missing several records all on the last day of the month
(7/31). It appears the records the query is missing have a time stamp in them
in addition to the date.

Is there a function I can use to return all the correct records within a
given time period or change the field to another type so that the query works?

Paul