Report not pulling correct dates

[vnfoster]

I have my report set up to pull dates from a form, however the query is not pulling the correct number of records since the New Year, for example when someone enters 10/31/2009-12/31/2009 the report returns 100 records, but when 10/31/2009-01/01/2010 only 3 records are pulled. I can't figure out why this is.

 

[Galaxiom]

Post the sql from the Record Source query of the Report.

 

[vnfoster]

SELECT [PROS Cases].CHARGES, [PROS Cases].[DATE FILED], [PROS Cases].STATION, [PROS Cases].[SPECIAL APPROVAL], [PROS Cases].[OPERATION NAME], [PROS Cases].[CRIM HIST 1], [PROS Cases].[CRIM HIST 2], [PROS Cases].[CRIM HIST 3], [PROS Cases].[CRIM HIST 4], [PROS Cases].[Convicted Sex Offender], [PROS Cases].Juvenile, [PROS Cases].Status, [PROS Cases].DISTRICT, [PROS Cases].[A NUMBER], [PROS Cases]![CONTROL NAME] & "-" & [PROS Cases]![MAMA LAST] & ", " & [PROS Cases]![GIVEN NAME] AS Name, [PROS Cases].[Gang Affiliated], [PROS Cases].[1324 Type], [PROS Cases].[Southern Hold 2]
FROM [PROS Cases]
WHERE ((([PROS Cases].[DATE FILED]) Between [forms]![pros menu]![Start Date] And [forms]![Pros Menu]![End Date]));


Here is the SQL I use the wizards a lot, so this is set up with a command button from the opening menu.

 

[dcb]

What are the dates (DATE FILED) of the 3 records that are returned using the 01/01/2010 mentioned?

 

[vnfoster]

Today I queried 10/01/2009 thru 01/05/2010
The query pulled 09/21/2009,09/28/2009,10/01/2009 and 01/01/2010,01/04/2010

 

[Galaxiom]

Today I queried 10/01/2009 thru 01/05/2010
The query pulled 09/21/2009,09/28/2009,10/01/2009 and 01/01/2010,01/04/2010

This looks consistent with the boxes on the form assuming DD/MM/YYYY formatted entries. Try some unambiguously MM/DD/YYYY (eg 01/13/2009) entries and see what it returns.

However Access uses the system date format. I don't know any way it could be using DD/MM/YYYY for the forms while still using MM/DD/YYYY in the table.

The only place I know where Access will automatically switch date formats is in the date functions, Month and Day when a date is clearly from a different format from the system.

Check that the Format property of the textboxes is Date and Time - Short Date. However I would have expected a type mismatch if it was wrong.

 

[dcb]

Yip
All queries use MM/DD/YY

 

[Galaxiom]

Time to post a sample of the database. Just the form, relevant queries and tables with a few dummy records.

 

[vnfoster]

I took out a lot of un needed info, but the final report is a compilation of many reports so I left them all there. I hope I attached this correctly. Thanks for the help

 

[boblarson]

Your problem is that you are storing Date Filed as TEXT and not DATE format. CHANGE THE field datatype to DATE in the table.

 

[boblarson]

My apologies, that is still not working. Something isn't right here because what SHOULD work isn't working. I'm attempting to find out why.

 

[Brianwarnock]

The format on the form is also text, changing those to shortdate and it appears to work fine.
Date app is also defined as Text, You should check all definitions.

Brian

 

[vnfoster]

Changing the date format on the form worked. Thank you!!!

 

[boblarson]

Changing the date format on the form worked. Thank you!!!

Big Kudos to Brian (who always seems to show up just at the right time to keep me from embarrassing myself too badly. Thanks for the assist Brian )

 

[Brianwarnock]

Big Kudos to Brian (who always seems to show up just at the right time to keep me from embarrassing myself too badly. Thanks for the assist Brian )

Its a pleasure Bob, and thanks for the kudos we older guys need it.

Brian