access: sql statement is not sorting for some reason

Question

my report is generating data that is supposed to be sorted by date and is within a specified date range:

SELECT *
FROM [lab occurrence form]
WHERE ((([lab occurrence form].[occurrence date]) Between [Forms]![Form1]![Text2] And [Forms]![Form1]![Text4]))
ORDER BY [lab occurrence form].[occurrence date] DESC;

i have two textboxes which contain the range of dates: text2 and text4

the report is displaying the data correctly, but it is not sorting it by date

how can i make sure that it will sort it by date?

i did a datasheet view on the query and it works fine, but when i run the report it does not sort it for some reason by date

Answer 1

Order by will sort by the field specified, but if you have not used a datetime data type, it will not sort the way you expect because it will do an alphabetical sort. The best fix for this is to stop storing dates as anything except date datatypes.

Answer 2

Use the report's Sorting and Grouping option to establish the sort order. In Access 2003, with the report open in design view, select "Sorting and Grouping" from the "View" menu. If your Access version is different, look for a similar name in the report design options.