Select Query, Criteria not working

[DaveMoore]

My problem is the selection criteria in my query.
It is a date field I must select from.

Between #01/09/2003# and #30/09/2003# and I get nothing

#31/08/2003# and I get everything

I have checked the field properties and they are set to 'short date'
There are approximately 100 records that have a field value of September
2003
I have another Access database and the above date selections work fine

What else can I check?
I am using OfficeXP professional

Many thanks in advance,
Dave Moore

 

[Jeff Boyce]

Dave

A datetime data type stores a number representing the days since some
arbitrary date in the late 1800's (which someone will know exactly) as the
"whole number", and the portion of 24 hours as a decimal fraction (e.g.,
..5000 = noon).

What you described is the FORMAT displayed (i.e., "short date"; by the way,
"September, 2003" is NOT a "short date" format).

Consider temporarily removing the formatting on the table and see what
underlying values are stored, in the way of continuing your troubleshooting.

Consider testing the US date layout in your criteria (i.e., mm/dd/yy).

Good luck!

Jeff Boyce
<Access MVP>

 

[DaveMoore]

Jeff,
thanks for your response.

I did interrogate Google
http://groups.google.com/advanced_group_search?q=group:*Access*&num=100
entered criteria dates, and picked up the following tip from September 2001
which worked for me.

Set up a 'prompt' as a 'true' parameter
In my case - Between [FROM date] And [TO date]
Click on parameters in the Query menu, enter 'FROM date' and 'TO date' in
the dialogue type box and select Date/Time as the data type.

As I said, this worked for me although I still don't know what was wrong
before.

Thanks again,
Dave Moore