Ascending dates in MS Access reports

[Anotherchris]

Hi,

I have some simple reports in MS Access that sort the date alphabetically instead of ascending order. All reports that are date by month are okay but all that are date by day go alphabetically no matter what format I use. Can anyone shed some light on this.
Thanks

[Jim Doherty]

Hi,

I have some simple reports in MS Access that sort the date alphabetically instead of ascending order. All reports that are date by month are okay but all that are date by day go alphabetically no matter what format I use. Can anyone shed some light on this.
Thanks

Because depending on what you are doing a text day will sort as text.
You need to look at representing the date numerically

To format a date using the format function would give you a textual representation of the date ie Monday,Tuesday and so on
ie Format([YourDateField],"dddd") This however this will sort alphabetically so to represent it numerically and set out which day of the week your week starts.so.......

Weekday([YourFieldName],0) will return a numeric value indicating the day of the week 1 - 7 where Monday is your start day

Weekday([YourFieldName],1) will return a numeric value indicating the day of the week 1 - 7 where Sunday is your start day

You merely then need to sort on the result of that call to the WEEKDAY function

Regards

Jim