Need help using Between...And Operator

[ruthcali]

i am using Access97.

I am using the Between function in a query and I noticed if the date field has a time AND a date, and it falls at the end of my range, it isn’t included.

Example,
Here is a value in my date field: 07/31/2000 10:15:00 AM

On my query, if I do Between #07/01/2000# And #07/31/2000#

The record above doesn’t show up!!!

If I remove the time (remove 10:15:00 AM), then it does appear.

An easy solution would be to change the table structure, but I can’t.

For the query, I went to properties and for that field , set the format to Short date. But, that only visually removes the time, it doesn’t cause the record to appear when doing the Between filter.

HELP!
thanks
ruth
ruth.jonkman@wcom.com

 

[albywalt]

Changing your criteria to Between #12/31/00# And #1/1/01 11:59:59 PM# should work.

Jeff

 

[albywalt]

I just realized my dates are different than yours, but you get the idea.

Jeff

 

[ruthcali]

Hi jeff,
thanks for writing.

I actually have a form called frmEnterDate with 2 unbound text boxes (txtDate 1 and txtDate2) where the user enters his dates.

Then the query uses those dates for its calculations. (IIf([issue_date] Between [Forms]![frmEnterDate]![txtDate1] And [Forms]![frmEnterDate]![txtDate2], ...

Right now, I have a Short Date input mask for the two text boxes. Could I create an input mask that automatically and invisibly adds 11:59:59PM to txtDate2?

thanks,
rut
ruth.jonkman@wcom.com

 

[ruthcali]

I did it!!

My input mask looks like:
99/99/00" 11":"58":"58 PM";0;_

(I couldn’t use 11:59:59 because 9 is a placeholder, so I used 8 instead.)

so now when the user enters a date, the 11:58:58PM is automatically entered as well.

Yeah!!

thanks for your help!
ruth.jonkman@wcom.com