Between [startdate] And [enddate] (1 Viewer)

[wizcow]

Hi
In the criteria line I have entered...

Between [forms]![frmSwitch]![StartDate] And [forms]![frmSwitch]![EndDate]

This works except;
If startdate is 9/7/04 and enddate is 9/9/04
I only get results from 9/7/04 to 9/8/04
No results for 9/9/04

Why is this?

Tom

 

[KenHigg]

This is just a guess but you may have something putting the time pc on this fld, like maybe a now() default value?

???
kh

 

[WayneRyan]

Tom,

No matter how many times I do this I never get it right.

When you miss the last day's entries, it is because your short date for the
last date is: "9/9/04 12:00:00:00 AM"

All of your entries for the last day are definitely later in the day than that.

The easy solution is to make the last criteria:

Date & " & " 11:59:59 PM" or simpler "Date + 1"

Then you will have all of your records returned.

The real explanation is that Pat Hartman devised this scheme and only she
knows how to use DatePart or whatever and make it work right. I hope
that she looks in on us and "this time, I swear I'll pay attention" and learn
the real reason why this happens.

Wayne

 

[wizcow]

Thank you for the replies

I think KenHigg is on to something.
Here's a quote from Ricky Hicks...

you must parse the Date value from the date and time value stored in the field in your table ...
You must create and use a "field expression" in your query.
Then you set the criteria to filter the records in this field expression ...

So I entered this....

mydate: DateValue([date])

Then in the criteria I entered...

Between [forms]![frmSwitch]![StartDate] And [forms]![frmSwitch]![EndDate]

Thanks guys
Tom