A crosstab query always sorts column headers in ascending order -- no way to change it.  See the picture below, where my date column headers are sorted in ascending date order.  By "out of order", do you mean ascending order is not the order you want?

They can also be sorted Descending.

I have a video on this exact topic coming out soon. Stay tuned. And no, Debra, you're not crazy... you can't do this with the wizard, but you can do it with a little change in the design view or SQL.

Kevin - its mixing up the dates.  For instance let's say my range is June 1st to June 15th. At the top of the columns, instead of it counting "up" June 1st, June 2nd, June 3rd, etc,... it goes June 1st, June 2nd, June 5th, June 9th, June 3rd.

If that wasn't bad enough, now it is pulling in dates up to and including today, even though my range is only to the 15th.

Make sure you're using actual DATE values, not text values that you've converted with the Format function. If you are going to use Format, make sure you pick "Short Date" as the format and not "yyyy-mm-dd" or something like that.

Another reason I highly recommend the ISO Date Format because it sorts fine either way.

Let's see a screen shot

Here you go: Crosstab Sort

Here is a screenshot of the design.  It is still sorting out of order- I am wondering if it is because I am trying to use a range in there. I tried using the sort on either date completed column with no effect.

Sort the Column Heading not the criteria.

Hi Kevin.  I did try sorting the column heading- but got the same result. I took the screen shot when I tried to sort the criteria column, when the column heading did not work.  Both yield the same results (see screen shot).

Here are screen shots showing just the column heading attempt

What happens if you build a preceding query sorted by date and then base your Crosstab query off of that?

I just finished doing that and tried sorting the query- no change. Same exact results.

Something else which is odd is when I go to close the query, or the crosstab, it asks me for my parameters before closing. Start and then end date.  Not sure why. Parameters are defined and it pulls the range fine.

On the Ribbon click the Parameters button in the Show/Hide group. Are there any parameters defined?

Does it work without any parameters? The fact that your totals column is in the middle is weird. Did you manually move the columns around? You may have inadvertently made layout changes.

The defined parameters are Start Date and also End Date.  It works without parameters. It sorts correctly, but also lists every date under the sun.

When I say without parameters- I mean I removed the defined parameters and the range as well.

Try using form fields for the parameters instead.

Between Forms!MyForm!StartDate and Forms!MyForm!EndDate

And make sure those are Short Date fields.

Value From a Form

I was able to duplicate what Debra saw.  Debra, you must have manually rearranged column order, and that is what caused this problem.  Every time you do that, the query will show the order you made, and will not sort the column order like it's supposed to.  For a crosstab query that generates columns dynamically, this is a major problem when it happens.

Regarding your "Total" column, since it's a row heading, it is placed on the left by default, as all row headings are.  You must have moved it to the right, since that's the logical place for a total column.  But since you moved it, the query will *continue* to use that column position.  If there are 4 columns of data and you moved the Total column to the 5th column, then later you re-run the query and it gives 9 columns, your Total column will STILL be at the 5th column.  It will REMAIN at the 5th until you move it again!  That is why you keep seeing it in the middle among the other columns and not on the rightmost column.

There is no easy way to reset the order, other than to re-create the query.  Create a new query with a different name, copy and paste the SQL statement over, then rename the query back to the original name.  The column order will be back to default.  From then on, you should not manually change column order again.  If you need your total column to be the rightmost column, there are other ways to do it.

To your other question, when you manually rearrange column order, the query will ask you for the parameters when you close the query.  In fact, whatever changes you make in the query (sort order, criteria, etc.), it will prompt you for the parameters when it closes.  Yes, it seems like a bug, but maybe not.

Yeah... crosstabs are a pain. I do things like this in Excel. LOL

Yes, I did manually move the columns when I first saw that they were out of order to try and fix it.  I will try and recreate it.  Thank you all for your help and thank you Richard for making the video!

In SQL Server, a crosstab query lets you set specific column order in the SQL.  You should keep that in mind in case you need to migrate to SQL Server in the future, because that's what many Access users end up doing when they need more advanced features.  But more advanced features means they are also more complicated to use and set up.

I recreated it and it works perfectly now.  Thank you all for your help!

One last odd question- is there a way to add a calendar icon to a parameter popup box? So you can point and click, rather than typing it in? I know how to do it on a regular form.

Afraid not Debra. You would have to build your own form for this.

Just want to mention why this problem exists in Access but not in SQL Server (I might not have another chance to bring this important topic up).  

In an Access crosstab query, the "PIVOT" keyword determines the column headers, and it only accepts a field:

    ... PIVOT MyTable.MyDateField ...

But in SQL Server, you can specify literal values as the column headers, in the exact order you want them to be shown:

     ... PIVOT ([January], [February], [March], [April], [May]) ...

Thus, with string concatenation, you can construct an SQL that will set up column headers in the exact order you want.

In Access, since only ascending or descending order is allowed, the month names in this example will be alphabetical instead of the proper month order:  April, February, January, July, June, March, May, etc.

Debra, I built a Access Date Time Picker popup, but you'll have to use a form for the parameters, not a query.