Access 2000: Sort Dates by the Month and the Year

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

bigceedee

Thread Starter
Joined
Sep 10, 2004
Messages
36
I am having a problem sorting by the date which contains the month and the year. Here is my Field Description:

Format$(Invoices.[Processing Date],'mm yyyy')

I know you can sort ascending and descending in the sort field, but this does not sort correctly as it starts with the month. Can anyone give me any ideas?

Thanks,
:rolleyes:
 

OBP

Joined
Mar 8, 2005
Messages
19,896
In a query you can use this as a "field" in the column Heading

Expr1: Format([Processing Date]],"mmm")

and

Expr2:DatePart("yyyy",[Processing Date])

as another field and then sort the year column first and then the month column
 

bigceedee

Thread Starter
Joined
Sep 10, 2004
Messages
36
ODP,
I am trying to sort the dates so that when my crosstab query runs, my month/year header will populuate in order.. Such as:

01-2004 02-2004 03-2004 01-2005 02-2005 03-2005
 
Joined
Aug 5, 2005
Messages
3,086
How is it sorting incorrectly?

My first impression is that the culprit is Format; not because you're using it incorrectly but rather because I think it is implicitly converting your dates to strings. Am I correct in that the sort is currently following this pattern:

01-2004
01-2005
02-2004
02-2005
03-2004
03-2005

? I'm not sure I'll be able to suggest a solution, but at least we can pinpoint the problem.

Post back please.

chris.
 
Joined
Aug 5, 2005
Messages
3,086
[bump]

However, let's see...

The Format expression is a calculated field and corresponds to an alias, like FormattedDate or something, a la

Format$(Invoice.[Processing Date], 'mm yyyy') As FormattedDate

So as long as you specify the unformatted date field as the ORDER BY, you should be fine.

Code:
SELECT Format$(Invoice.[Processing Date], 'mm yyyy') As FormattedDate
FROM tblSomeTable
ORDER BY [Processing Date]
Give that a shot.

By the way, if you ever port your database to anything other than Access, you'll really regret having spaces in your object names. You should remove them.

HTH

chris.
 

OBP

Joined
Mar 8, 2005
Messages
19,896
For crosstabs you are better off enforcing the month Year otherwise it is possible to have gaps in the header.
Go in to the crosstab query design and in the "Query properties" enter the values that you want in the order that you want them in to the "Column Headings" in this type of format -
"01-2004","02-2004","03-2004","04-2004"
 

bigceedee

Thread Starter
Joined
Sep 10, 2004
Messages
36
Chris,
You are correct in your assumption on how the data sorts... It takes the month and sorts without taking the year into account so many results are in this format:

01-2004
01-2005
02-2004
02-2005

Thanks,
CD
 

bigceedee

Thread Starter
Joined
Sep 10, 2004
Messages
36
ODP,
Here is the original SQL:

TRANSFORM Sum(Invoices.Amount) AS SumOfAmount
SELECT Invoices.[Cost Center], Invoices.[GL Account], Budget_2005.[Line Item Description], Budget_2005.[2005 Budget Amount], Sum(Invoices.Amount) AS [Total for Year], Budget_2005.[2005 Budget Amount]-Sum(Invoices.Amount) AS [FY Budget Remainder], Sum(Invoices.Amount)/Budget_2005.[2005 Budget Amount] AS [Budget Used (%)]
FROM Invoices INNER JOIN Budget_2005 ON (Invoices.[GL Account] = Budget_2005.[GL Account]) AND (Invoices.[Cost Center] = Budget_2005.[Cost Center])
WHERE (((Budget_2005.[Line Item Description])<>'Below the line - Manager BB Deployment') AND ((Invoices.[Cost Center])='456400' Or (Invoices.[Cost Center])='456799' Or (Invoices.[Cost Center])='456435'))
GROUP BY Invoices.[Cost Center], Invoices.[GL Account], Budget_2005.[Line Item Description], Budget_2005.[2005 Budget Amount]
ORDER BY Format$([Invoices].[Invoice Date],'mm yyyy')
PIVOT Format$([Invoices].[Invoice Date],'mm yyyy');
 

bigceedee

Thread Starter
Joined
Sep 10, 2004
Messages
36
This is what I get:
01 2004 01 2005 02 2005 03 2005 04 2005 05 2005 06 2002 06 2004 06 2005 07 2004 07 2005 08 2004 08 2005 09 2004 09 2005 10 2004 10 2005 11 2004 11 2005 12 2004 12 2005

When I want:
06 2002 01 2004 06 2004 07 2004 08 2004 09 2004 10 2004 11 2004 12 2004 01 2005 02 2005 03 2005 04 2005 05 2005 06 2005 07 2005 08 2005 09 2005 10 2005 11 2005 12 2005
 

OBP

Joined
Mar 8, 2005
Messages
19,896
I can see what you get and can probably fix it for you, did you read my previous posts?
 

bigceedee

Thread Starter
Joined
Sep 10, 2004
Messages
36
ODP,
You mean about:
Expr2atePart("yyyy",[Processing Date]) or changing the query properties?

CD
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Members online

Top