Date Order

[MaleNurse325]

Hi guys,

So, the situation is that I have a table which has analysis dates as one of the fields. The table lists all the dates in date order nicely. If I then run a query on the table and have them put in date order, they sort, ascending and descending nicely. However..... In my report I cannot convince Access that the 2dn of January 2016 comes AFTER 1st of January 2016.

I have tried to run the report directly from the table and from a querie and although both are in date order, once it gets to the report is scrambles the dates.

The report is sperated by month and has date headers and footers if that is relevant.

Picture attached

Thanks
Steve

 

[pbaldy]

Sorting in a report needs to be set up in Group & Sort, on the ribbon.

 

[MaleNurse325]

Hi Paul.
I have done it there too. I have checked that the dates are input correctly and re-input them to ensure they are not stored as 11th January 1916 etch but to no avail :banghead:

 

[pbaldy]

The dates are in a date/time field rather than a text field? Can you attach the db here?

 

[JHB]

Could you post a stripped down version of your database with some sample data, zip it because you haven't post 10 post yet + name of the report.

 

[MaleNurse325]

I will give it a go

 

[MaleNurse325]

Hi,
I have emptied out all the other irrelevant stuff and attached the problematic bit.

Thanks guys :banghead:

 

[MaleNurse325]

They are in a Date/Time Field and I have experimented with the formatting to see if that makes any difference

 

[pbaldy]

Try changing the field name to something other than "Date", which is a reserved word that Access can confuse with the function of the same name. I did that and it appears to sort correctly now. Note also that you may want to add a sort after the group to affect the detail records.

 

[MaleNurse325]

nope, it still does it, I have changed the field name and everything

 

[JHB]

Hi Paul.
I have done it there too. I have checked that the dates are input correctly and re-input them to ensure they are not stored as 11th January 1916 etch but to no avail :banghead:

You haven't set up any sort order - try it now.

 

[pbaldy]

Isn't this working? I changed the field name, updated the textboxes that use it and Group & Sort.

 

[MaleNurse325]

I have set the order in the query which works fine and on the sorting and Grouping section of the report I have set the sort order to Descending