Sort By Month

[jonnymenthol]

Hello, I have the following query which gives me a count by day.

However, it sorts by day instead of day/month.
For example:

01/01/05
01/02/05
01/03/05
02/01/05
02/02/05 and so on...

Can I sort so it will show me all of January, then all of Febuary etc?

Thanks.

J.

 

[MStef]

In your Table for this field put;
Data Type = Date/Time,
Format = ShortDate.

 

[jonnymenthol]

Thanks for the suggestion, but unfortunately I cant do that.

I have only read only access to the tables (they are linked in my db), and I want to run queries on the data.

J.

 

[MStef]

Ok Look at "DemoSortManthA2000.mdb",
I think it can help you.

 

[Mile-O]

My guess is that you have SQL like this:

SELECT Format([MyDate], "dd/mm/yyyy") As DateField
FROM MyTable
ORDER BY Format([MyDate], "dd/mm/yyyy");

When what you want is this:

SELECT Format([MyDate], "dd/mm/yyyy") As DateField
FROM MyTable
ORDER BY [MyDate];


Formatting something returns it as text so text numbers are sorted differently to actual numbers.

 

[jonnymenthol]

Thank you, that works a treat.

I appreciate all your help.

J.