Hel with "Between" criteria

[Maggie23]

I have a query using a date field to return records for date range. In the date field I am using the "between - and" critieria. It works fine except when I use the current date for my beginning and ending dates. The report returns errors in the date range with no other data. I am getting around it by typing the current date i.e. 5/11/03 for first date criteria and tomorrow's date 5/12/03 for second date. It then returns only the records for 5/11/03 since there are no records for tomorrow yet.

Is there something I need to add to the between criteria when I want records for the current date?

Thanks for any help,
Maggie

 

[soundsfishy]

Heres 2 ways of using the between


between #eg05/11/03# and #eg05/12/03#

between [Date from] and [DateTo] This

The above work for me.

 

[raskew]

Maggie-

The problem is most likely that your date field includes both date and time. A date/time data type is stored as a double precision, floating-point number (up to 15 decimal places). The integer portion of the double-precision number represents the date. The decimal portion represents the time as a portion of a whole day.

It's the difference between date() and now(). To better understand, from the debug window type (your times will vary):

x = now()
? x
5/12/03 6:41:29 PM
y = date()
? y
5/12/03

'to see how Access stores date/time use the cdbl() function.

? cdbl(x)
37753.7777546296
? cdbl
37753

When you specify in your query: ...between Date() and Date() or
...between #5/12/03# and #5/12/03#

you're really saying to Access
...between 37753 and 37753

obviously, if there’s a time attached to your date field the whole amount is going to be something greater than 37753, e.g. 37753.7777546296 as shown above.

Alas—no records returned.

If time is not a factor in your application, a practical way to resolve the situation would be to replace the contents of the date/time field with strictly date. You could do this with an Update query, replacing MyDate with int(MyDate).
Then you need to find any instances of Now() in your code/queries and replace them with Date().

If there’s a need to retain thedate/time combination then you’ll need to change your:
...between Date() and Date()
to
...between Date() and Date() + 1

which has the effect of saying:
...between 37753 and 37754
and would pick up all of date/times during 37753.

HTH,

Bob