My query returns records outside of specified date range.

[Matt_Lavoie]

I'm working on an Access 2007 database that has a report in it that asks you for a start and end date which it plugs into a query to get all the records in that date range, and then it shows you the results in a report.

Problem is that the query is returning extremely inconsistent results.
For example, I was prompted for a start and end date, and entered 9/10/11 through 10/10/11. (MM/DD/YY format).
So the code that is saved in the query is:

SELECT [tblEquipmentFluids].Date, [tblEquipmentFluids].Equipment, [tblEquipmentFluids].Diesel, [tblEquipmentFluids].HydFluid, [tblEquipmentFluids].TransFluid, [tblEquipmentFluids].EngFluid, [tblEquipmentFluids].AntiFreeze
FROM tblEquipmentFluids
WHERE ((([tblEquipmentFluids].Date) Between #9/10/2011# And #10/10/2011#));

HOWEVER, the Datasheet view for the query shows records with dates: 9/1, 9/10, and 10/2 through 10/11
Records exist for every day in the range spcified, and not only are they not showing up, but it is showing records outside the date range.

I have no idea where it's getting these dates from. I can post the results for any other date range you ask for if you want. Any ideas? Thanks!

 

[MSAccessRookie]

I'm working on an Access 2007 database that has a report in it that asks you for a start and end date which it plugs into a query to get all the records in that date range, and then it shows you the results in a report.

Problem is that the query is returning extremely inconsistent results.
For example, I was prompted for a start and end date, and entered 9/10/11 through 10/10/11. (MM/DD/YY format).
So the code that is saved in the query is:

SELECT [tblEquipmentFluids].Date, [tblEquipmentFluids].Equipment, [tblEquipmentFluids].Diesel, [tblEquipmentFluids].HydFluid, [tblEquipmentFluids].TransFluid, [tblEquipmentFluids].EngFluid, [tblEquipmentFluids].AntiFreeze
FROM tblEquipmentFluids
WHERE ((([tblEquipmentFluids].Date) Between #9/10/2011# And #10/10/2011#));

HOWEVER, the Datasheet view for the query shows records with dates: 9/1, 9/10, and 10/2 through 10/11
Records exist for every day in the range spcified, and not only are they not showing up, but it is showing records outside the date range.

I have no idea where it's getting these dates from. I can post the results for any other date range you ask for if you want. Any ideas? Thanks!

First of all, I do not see anything incorrect about your Query. There is one thing that I would change, even though doing so should not affect the outcome.

Your first Field has the name Date. I would change this to a more meaningful name like DateofLastEntry. This is because the word Date is reserved by Access and used as the name of a Function Date() that returns the current date.

I am not aware of any specific problems that will arise from this, but in the event that there are, changing it would eliminate the potential. Let us know how this works out.

 

[kriswork]

I had a similar problem on Access 2003 where a query wasn't returning all the results that it should have. The query hadn't been modified for several months so I knew it wasn't the query. Having ran the query on a different machine, it turned out to be a corrupt install of Access even though there were never any error messages or anything else odd. The machine was swapped out and all is OK again.

The only other thought is the date format could be causing an issue. Can you put some test data in where the day of the month is over 12 so it couldn't then be a month.

Kris

 

[MSAccessRookie]

I had a similar problem on Access 2003 where a query wasn't returning all the results that it should have. The query hadn't been modified for several months so I knew it wasn't the query. Having ran the query on a different machine, it turned out to be a corrupt install of Access even though there were never any error messages or anything else odd. The machine was swapped out and all is OK again.

The only other thought is the date format could be causing an issue. Can you put some test data in where the day of the month is over 12 so it couldn't then be a month.

Kris

I thought about that, but discounted the possibility for two reasons:

• The data that was reported as being found would not have been located based on any valid date format that I am aware of
• The OP did not report any SQL errors.

 

[Matt_Lavoie]

---------------------
EDIT: Solved! In my table, the Date field was set to text, rather than to date.
---------------------

Thanks for the help you two, it's nice to have other brains on this problem.
I tried it on a different computer, I tried importing everything into a new shell, and I tried changing the Date field's name and it's still acting oddly.

It's not giving any errors, and the results are inconsistent no matter what dates I put in.

I have some more clues that might help. It requires some explaination.

This report can be used in 2 ways. If you access it through the "Monthly form" then you are prompted to select a month from a combo-box and then enter a year and hit Open. VBA will compute what the last day of that month was, and enter the correct beginning and end dates into a query. A report is then automatically opened which sums all the fields found in the query. This method is working 100%.

The other way to access the report is through the "Any Range form". A form which just has two text boxes for you to enter any begin and end date of your choice and then hit open. It puts these dates into the same query and opens the same report. This is the one that is causing problems.

So you might think that the "Any Range" thing is the culprit, however this same form and code is used to query other tables and open other reports and it works just fine. And this particular report is kind of off the hook since it works when fed dates by the "Monthly form".