Date Format

[Vinnie via AccessMonster.com]

Hi,

I have a query that reads in a date field from a table.

I've put 'Month: Format([Date Entered],"mmm-yy"); around the data, as I'm
calculating a monthly sum.

But when I go to sort the data by ascending date, it does it alphabetically.
The same occurs when I export it to excel.

Any ideas how to ensure that when I sort the data by date, it does so?

Thanks!

 

[Allen Browne]

It will sort correctly if you put the year before the month number:
TheMonth: Format([Date Entered],"yyyymm")

A better solution would be to use 2 fields for the year and month, and sort
on that:
TheYear: Year([Date Entered])
TheMonth: Month([Date Entered])
These fields will then be numbers (instead of Text like the Format()
function generates), so you will be able to do more with them.

I have also suggested a name other than "Month", so that Access does not
misunderstand the name. (There is a function named Month, so Access can get
confused in a form if you have a field with that name in a form.)

 

[Guest]

The problem really is that you are asking for the month in text format.
Format(Date,"mmmm-yy") will return
August-06

If you need to display the month in text, you probably what to add a
calcuated field to present that. Also, I would suggest changing to:
Format(Date, "yyyy-mm")
Which will be 2006-08
Putting the month first means you will get all the January records for all
the years followed by Feb, etc.

01-2005
01-2006
02-2005
02-2006

It will sort correctly if you put the year before the month number:
TheMonth: Format([Date Entered],"yyyymm")

A better solution would be to use 2 fields for the year and month, and sort
on that:
TheYear: Year([Date Entered])
TheMonth: Month([Date Entered])
These fields will then be numbers (instead of Text like the Format()
function generates), so you will be able to do more with them.

I have also suggested a name other than "Month", so that Access does not
misunderstand the name. (There is a function named Month, so Access can get
confused in a form if you have a field with that name in a form.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have a query that reads in a date field from a table.

I've put 'Month: Format([Date Entered],"mmm-yy"); around the data, as I'm
calculating a monthly sum.

But when I go to sort the data by ascending date, it does it
alphabetically.
The same occurs when I export it to excel.

Any ideas how to ensure that when I sort the data by date, it does so?

Thanks!