Free Lessons
Courses
Seminars
TechHelp
Fast Tips
Templates
Topic Index
Forum
ABCD
 
Home   Courses   Index   Templates   Seminars   TechHelp   Forums   Help   Contact   Join   Order   Logon  
 
Home > Courses > Access > Expert > X18 > < X17 | X19 >
Back to Access Expert 18    Comments List
Dates Appearing Out of Order Upload Images   Link   Email  
Debra Triolo         
11 months ago
When I run my crosstab query it is pulling the dates as column headers, but they are all out of order. How can I fix this?
Kevin Yip       
11 months ago
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?
Kevin Yip       
11 months ago

Kevin Robertson            
11 months ago
They can also be sorted Descending.
Richard Rost             
11 months ago
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.
Debra Triolo         
11 months ago
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.
Richard Rost             
11 months ago
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.
Richard Rost             
11 months ago
Let's see a screen shot
Richard Rost             
11 months ago
Here you go: Crosstab Sort
Debra Triolo         
11 months ago
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.
Debra Triolo         
11 months ago

Debra Triolo         
11 months ago

Kevin Robertson            
11 months ago
Sort the Column Heading not the criteria.
Debra Triolo         
11 months ago
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).
Debra Triolo         
11 months ago
Here are screen shots showing just the column heading attempt
Debra Triolo         
11 months ago

Debra Triolo         
11 months ago

Kevin Robertson            
11 months ago
What happens if you build a preceding query sorted by date and then base your Crosstab query off of that?
Debra Triolo         
11 months ago
I just finished doing that and tried sorting the query- no change. Same exact results.
Debra Triolo         
11 months ago
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.
Kevin Robertson            
11 months ago
On the Ribbon click the Parameters button in the Show/Hide group. Are there any parameters defined?
Richard Rost             
11 months ago
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.
Debra Triolo         
11 months ago
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.
Debra Triolo         
11 months ago
When I say without parameters- I mean I removed the defined parameters and the range as well.
Richard Rost             
11 months ago
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
Kevin Yip       
11 months ago
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.
Richard Rost             
11 months ago
Yeah... crosstabs are a pain. I do things like this in Excel. LOL
Debra Triolo         
11 months ago
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!
Kevin Yip       
11 months ago
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.
Debra Triolo         
11 months ago
I recreated it and it works perfectly now.  Thank you all for your help!
Debra Triolo         
11 months ago
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.
Kevin Robertson            
11 months ago
Afraid not Debra. You would have to build your own form for this.
Kevin Yip       
11 months ago
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.
Richard Rost             
11 months ago
Debra, I built a Access Date Time Picker popup, but you'll have to use a form for the parameters, not a query.

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Expert 18.
 

 
 
 

The following is a paid advertisement
Computer Learning Zone is not responsible for any content shown or offers made by these ads.
 

Learn
 
Access - index
Excel - index
Word - index
Windows - index
PowerPoint - index
Photoshop - index
Visual Basic - index
ASP - index
Seminars
More...
Customers
 
Login
My Account
My Courses
Lost Password
Memberships
Student Databases
Change Email
Info
 
Latest News
New Releases
User Forums
Topic Glossary
Tips & Tricks
Search The Site
Code Vault
Collapse Menus
Help
 
Customer Support
Web Site Tour
FAQs
TechHelp
Consulting Services
About
 
Background
Testimonials
Jobs
Affiliate Program
Richard Rost
Free Lessons
Mailing List
PCResale.NET
Order
 
Video Tutorials
Handbooks
Memberships
Learning Connection
Idiot's Guide to Excel
Volume Discounts
Payment Info
Shipping
Terms of Sale
Contact
 
Contact Info
Support Policy
Mailing Address
Phone Number
Fax Number
Course Survey
Email Richard
[email protected]
Blog RSS Feed    YouTube Channel

LinkedIn
Copyright 2024 by Computer Learning Zone, Amicron, and Richard Rost. All Rights Reserved. Current Time: 5/21/2024 8:08:59 PM. PLT: 1s