Dates for criteria in 97 give all records

[jedlag]

Hi All,
Am having an interesting problem. In Access 2000 I can put in a criteria of: Between [Enter Beginning Date] and [Enter Ending Date] for a Date field in a query. When I input 08/01/2001 and 08/30/2001, it will return the records between those dates.
In Access 97, using that same criteria, the query will return all records no matter the date.
Now interestingly, when I've put in 08/01/2001 and 08/01/2001, it will give me the correct records for that date! It just seems to have a problem when I put in different dates.
I've tried putting in an Input Mask using the Short Date as well as changing the Input Mask to 99/99/0000. I've tried putting "mm/dd/yyyy" in the Format.
Anybody have any suggestions how I can make this query work for finding records that match more than one day?
Any help would be much appreciated
jedlag

 

[LonnieJohnson]

Is your field a date field? Other than that sounds like you should not be having a problem. If you's like, you can send the query and the table and I will take a quick look at it. ljprodev@yahoo.com
ProDev, MS Access Applications B-)

http://www.galaxymall.com/software/PRODEV

 

[CosmoKramer]

I have experienced the same thing. I would occasionally get records outside the range I entered. This is what I had to do when the range was always an entire month:

Create an expression field in your query something like this:

Format([Trans_Date],"mm/yyyy")

In the criteria box put:

[Enter Month to report - mm/yyyy]

This will return records for that month only.

HTH

 

[jedlag]

Hi LonnieJohnson and CosmoKramer,
I'm gonna try CosmoKramer's suggestion and just have them put in the month. The workaround I found on my own, I love banging my head against a brick wall, was to build the database in 2000 and then convert it to 97.
I'm not real happy with it. You have to put in the Long Date format even though the Short Date format is what shows! If you don't put in the Long Date format, the criteria doesn't recognize the year.
CosmoKramer, I'm glad this wasn't intermittant, otherwise I might not have caught the problem. I'm gonna try your suggestion this afternoon and will give you the results a little later.
Thank You for the suggestion, much appreciate all the help )!
jedlag

 

[jedlag]

Hi CosmoKramer,
Your idea worked! Thank You Thank You! I Greatly appreciate the help!
jedlag