Between two dates in query criteria 1

[Crevalle]

I have dates in the format of "8/2/2004 10:51 PM" in my tables. I would simply like to allow the user to input two dates, and be able to pull all records within the date range. Here's the odd thing: using "Between [enter beg date] and [enter end date]" does not work. For some reason, it works when I input ">=8/2/2004 and <=9/14/2004"

Is there a way to do this better, or to add the wildcards (>=/<=) to my query? I tried "Between "<=" & [enter beg date] and "<=" & [enter end date]" to no avail.

Thanks--I'll be watching for responses!

 

[Crevalle]

FWIW--The standard "between [enter beg date] and [enter end date] brings back all dates EXCEPT the current date. For example, if I put 8/1/2004 to 9/14/2004, it will pull back all date from 8/1/2004 to 8/13/2004. When I use the >=/<=, it does the same thing. However, if I put 8/1/2004 to 8/15/2004, it will pull back 8/14/2004 records successfully.

This should be so easy, I'm almost embarrassed...

 

[dolfan1873]

Between [Type the beginning date:] And [Type the ending date:].

 

[Crevalle]

Dolfan1873--that's exactly what doesn't work. That's what I mean. It SHOULD work, but it doesn't. So, I'm looking for a work-around. If I put "between [enter beg date] and [enter end date]" it will not pull back the end date, but everything before it.

In other words, if the user input between 8/1/2004 and 9/14/2004, it would bring back 8/1/2004 through 8/13/2004.

Any thoughts?

 

[lespaul]

8/1/2004 and 9/14/2004, it would bring back 8/1/2004 through 8/13/2004.

are you really missing a MONTH of data? 9/14?

Any way, your problem is that you have the time in the field. Your query is being structured like:


8/1/2004 12:00 AM
8/14/2004 12:00 AM

so, the query is returning the correct information because it is returning all the records before midnight on the 14th. So you will not get any records on the 14th.

You need to modify the query to only search the date portion of your field or add 12:00 PM to the end of the last date.

HTH

Leslie



Leslie

 

[Crevalle]

Hate to be a pest, but can you recommend a method to eliminate the time from the query criteria? Or point me in the right direction? Thanks!

 

[Crevalle]

BTW--I mis-typed. I meant the data would return 08/01/2004 though 09/14/2004, not 08/14/2004.

I'll keep checking back for a response. Thanks!

 

[dolfan1873]

set the format in the table for the date to short date this will get rid of the time, if you do not need the time

 

[CosmoKramer]

One trick I often use is to hardcode the times into the between statement. Something like:

Between [Enter beginning date] & " 00:00" And [Enter ending date] & " 23:59"

Hoc nomen meum verum non est.

 

[Crevalle]

Thanks CosmoKramer--Worked like a charm!