Solved: Crosstab Query- Column Headings Help

[CreganTur]

I've got a crosstab query where the column headings are dates- specifically short dates. My issue is that the dates are not in chronological order. In fact, here's what the order of one looks like now:

8/1/2008 8/12/2008 8/4/2008

As you can see, it's out of order.

What can I do to enforce the column headings to group in chronological order?

Here's the full SQL for the query- don't know if it's needed... but it can't hurt:whip

PARAMETERS [What month to view?] Text ( 255 );
TRANSFORM Count(tblLetters.LtrCreated) AS CountOfLtrCreated
SELECT tblLetters.Specialist, Count(tblLetters.LtrCreated) AS [Total Letters Sent]
FROM tblLetters
WHERE (((Format([tblLetters].[LtrSent],"mmm"))=Left([What month to view?],3)) AND ((tblLetters.LtrStatus)= _
"Approved") AND ((tblLetters.LtrSent) Is Not Null) AND ((tblLetters.LSISubmitted) Is Not Null))
GROUP BY tblLetters.Specialist, tblLetters.LtrStatus, tblLetters.LtrSent, tblLetters.LSISubmitted
PIVOT Format([LtrSent],"Short Date");

[FrymanTCU]

When I was in the Query builder it put an ORDER BY statement between the GROUP BY and PIVOT Statements... I know captian obvious but I'm trying to help.

[OBP]

Randy, the problem may well be that your Date Field does not actually contain valid dates. i.e.
8/1/2008 should be 08/01/2008
8/12/2008 should be 08/12/2008
and
8/4/2008 should be 08/04/2008

Was the data "Imported", if not you should use a date field with an Input Mask of 00/00/0000 to control the user input.

I would do some String manipulation to turn them in to the correct format.

[CreganTur]

Was the data "Imported", if not you should use a date field with an Input Mask of 00/00/0000 to control the user input.
Hate to say, but this didn't work. I used your input mask, and setup the Field Format to Short Date. If I entered 8/01/2008 the field would autocorrect to 8/1/2008. The leading zero would not stay.

[OBP]

What have you got as your Table Format and Input mask?
Or is this in the table?

[OBP]

Can you post an example?

[CreganTur]

What have you got as your Table Format and Input mask?
Field Data Type: Date/Time
Field Format: Short Date
Input Mask: 00/00/0000


Or is this in the table?
This is when inputting data directly into the table. It will chage "08/07/2008" into "8/7/2008"- it removes leading zeros.


Can you post an example?
Example db attached using avobe cited formatting.

[OBP]

That comes out exactly as I said, see attached Screen Print.

[CreganTur]

That comes out exactly as I said, see attached Screen Print.
I noticed that you're running Excel 2000... I'm running 2003. I wonder if there's something different between the 2 that could be causing this?

*wanders off to do some research*:reading:

NinjaEdithttp://img293.imageshack.us/img293/9060/ninja3od8.gif: Google proved its worth again. This isn't an Access issue- it's a Regional Settings issue. Short Date formatting on my computer was setup as M/d/yyyy. Changing it to M/dd/yyyy fixed the issue- it automatically adjusted my table values to show the leading zeros I needed.

This, in turn, corrected the issue I was having with my Crosstab query.

[OBP]

My version is actually Access XP (2002), running as Access 2000 as that is the lowest version that I can use for compatability with Posters, I post on the Tech Guys Forum most of the Time now as you and a couple of others are doing such a good job on here.

[CreganTur]

My version is actually Access XP (2002), running as Access 2000 as that is the lowest version that I can use for compatability with Posters, I post on the Tech Guys Forum most of the Time now as you and a couple of others are doing such a good job on here.

Thanks :)

I've found that I learn more by helping others solve their problems.

[OBP]

Me too. :beerchug: