Prob?????? Query behavs different

[Guest]

Hi!
Here is my problem.
I used #15-12-2003# as criteria for date field for a query. The query
retrive only 2 records, but actually table contans 25 records of same date.
Date Format given for the field as dd-mm-yyyy. My regional settings is also
of same format. The problem is solved after I copy the data value(15-12-2003)
of retrived field and pasted the date in others fields having same data. My
question is why it has happend like this? How to solve this prob? Manual
checking of no. of retrived records is very difficult. And the prob cannot be
solved like the way i did as above. Kindly help me. Thanks.

KRISH

 

[Ken Snell [MVP]]

ACCESS SQL queries expect dates in US format, no matter how you "formatted
the display" of the date. Dates are stored as double-precision numbers, not
as "date strings".

Try using #12/15/2005# for December 15, 2005.

 

[John Spencer]

Does the field have JUST the date in it? Or is there a date and time in the
field?

Quick test. Try searching for records with the criteria
Between #2003-12-15# and #2003-12-16#

If that returns the proper number of records then your field is storing a
date and a time in it. The format of the field is displaying only the date
portion since the format is set to show only dd-mm-yyyy.

How you fix this problem will depend on whether or not you actually need the
time component.