Query of DateValue Field for 10/20 returns 10/2

[HLCruz via AccessMonster.com]

I'm sure there is a simple, and probably obvious, solution to this but I'm
missing it.

I have a date/time stamp field that I'm running a DateValue calculation field
and have a perameter query set up for Between [Enter Start Date] and [Enter
End Date] in that DateValue field.

Interestingly when I enter 10/16/09 for the Start Date and 10/20/09 for the
End Date I get records with the dates 10/2/09 and 10/16/09 through 10/19/09.

Thanks for any help!

Heather

 

[vanderghast]

It seems that the comparison is made * as if * you were using litterals
instead of date_time values.

Be sure your field is really date_time as data type.

Add CDate( ) around your parameters:

Between CDate([Enter Start Date]) and CDate([Enter End Date])



Note that alphanumerically "10/2" is between "10/16" and "10/20" :

? "10/2" >= "10/16", "10/2" <= "10/20"
True True




Vanderghast, Access MVP

 

[John W. Vinson]

I'm sure there is a simple, and probably obvious, solution to this but I'm
missing it.

I have a date/time stamp field that I'm running a DateValue calculation field
and have a perameter query set up for Between [Enter Start Date] and [Enter
End Date] in that DateValue field.

Interestingly when I enter 10/16/09 for the Start Date and 10/20/09 for the
End Date I get records with the dates 10/2/09 and 10/16/09 through 10/19/09.

Almost certainly you have dates *and times* stored in the table field.

Try a criterion

= CDate([Enter start date]) AND < DateAdd("d", 1, CDate([Enter End Date]))

to get all times on the last date of the range.

Note that you should consider using textboxes on an unbound Form, and criteria
referencing [Forms]![formname]![txtStartDate], instead of the "popup prompt"
parameters.