Access query will not evaluate the date

  • Thread starter Thread starter Danka
  • Start date Start date
D

Danka

I have a linked Sql table in an Access database and I created a query on
that table and assigned with a range on a date field as follows:
=#1/1/2007# And <=#1/31/2007#
Click to expand...

I know for sure there are records with the date 1/31/2007 on that table,
however the recordset comes back with the records excluding those records
with the date 1/31/2007. Thanks.
 
Probably the records have a time component in addition to the just the date

Try this criteria
=#1/1/2007# And <#2/1/2007#
Click to expand...



--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
I bet that you do have some records dated 1/31/2007. I also bet that they are
dated after midnight something like 1/31/2007 11:11. In other words, they
have a time component.

Try this:
 
Access doesn't have Date fields, it has Date/Time fields. #1/31/2007# is
translated as 1/31/2007 12:00 AM (start-of-day). If you aren't getting
known records from 1/31/07 returned then my guess would be that the value
stored in the field also includes a time component which puts the record
after midnight, therefore falling outside your current criteria expression.

Possible solutions might be to change the query to <= #2/1/2007# or <=
#1/31/2007 11:59 PM#

HTH,
 
Yes they do have the "time" component on it as it is a Sql date/time field.
However if I change the range to as follows:
=#1/112007# AND < #2/1/2007#
Click to expand...

the recordset comes back with the records including those with the date
Jan-31-2007. It seems to me that the Access query not able or will not
evaluavte "=" on the last condition on the range I assigned.

This range will return the same recorset as above range:
=#1/112007# AND <= #2/1/2007#
Click to expand...

If I assign the condition as follows, it will not return any record

=#1/31/2007#

or any specific date

Thanks
 
At 2:31 PM Access stores the date/time for today's date something like
39174.6051273148. At midnight it's just 39174.

Is 39174.6051273148 <= 39174 ? Nope.

Is CDbl(39174.6051273148) <= 39174 ? Yes it is.

You have to deal with the time element unless you put the date in one field
and the time in another. I would NOT recommend doing that.
 
Back
Top