Cross-tab Query - Wrong Date Order

[euphonium01]

Hi All. I have a small Student database, simply for records of Student Names, Addresses, Classes etc. I am trying to run a Cross-tab query for a Time Table of forthcoming Classes, FileAs (the Student) as a Row Heading, and the date of the class [ClDt] as a Column Heading. I have asked the Query to Format the class date heading as "mmm-d" (I have to make the date abundantly clear or some Students won't bother turning up!). I put some fictitious classes in to check it worked, and it did, except the Date Column Heading comes out with Apr before Mar, and if I change the formatting to "d-mmm" I get Apr-1 then Mar-25, -26, -27, Apr-4, Apr-5 etc.

With Fortmat set "mmm-d"


And Format set to "d-mmm"




Query Design View


From the results of different Formats, logically it would appear that by Formatting the date it is being read (and therefore sorted) as a string, rather than the date? Assuming I'm right, how would I fix this? The usual method in the Sorting box hardly makes a difference?

Paul
BTW: After a long long stint in hospital, it's good to be back on the Forum

[ranman256]

dates like that are sorted as strings,
so you need a different field format so sort numerically: yy-mm-dd

[euphonium01]

dates like that are sorted as strings,
so you need a different field format so sort numerically: yy-mm-dd

Many thanks ranman for your reply. I've worked on your advice for a couple of days now, but I keep running into brick walls, can I explain a few more details that may help?

The database I am doing is a Student database for a small College, I have been doing a Crosstab Query in order to produce a Time Table for the Students. I wanted to be able to put in a 'Start Date' parameter, then have 7 days of classes, shown by Student as a Row Heading, Class Date as Column Heading, and the Course as a Value... in a Report.

My fields are:-
[StudentID] - Row from StudentT Table
[FilesAs] - Row from StudentT Table
[ClDt] - Column (Class Date) from ClassesT Table
[LARSCode] and [Venue] - Value from CoursesT Table

All the Tables have one to many relationships.

Trying to follow your advice I have the following design query, which in essence works well:-


I included clause 'IN (1,2,3,4,5,6,7)' in SQL view to achieve my 7 days.


As said, it works well and the Course, Class Time (ClTm) and Venue appear perfectly correct... when there's 31 days in the month that is!

Where it falls down is when there's only 30 days (or 28 in February) in the month, the weekdays and dates go haywire. As an example, I requested a Start Date of 29th April (30 days in the month) the Time Table shows 7 days from the Start Date which, puts a Course (scheduled for the 2nd May) on to the 3rd May, a day late, I think it's because it has a fixed *31 in the expression, but I wasn't even close to getting anywhere near the right results unless that was included.

I think I am understanding correctly as to why the issue arises? However, I am totally lost as to how to fix it, 3 days later I'm no further forward. I'm sure it must be possible to correct, maybe even something really simple I'm missing, maybe it means a total rejig using vba and variables instead (that, I WOULD need help with)?

Anything you can throw my way I would appreciate, but please keep it idiot-proof, I've barely got my head around simple vba, and so far only managed this Query with your help, and following blogs on the internet.
Many thanks... Paul
BTW: I can easily put together the relevant objects in a small db to send if it would help? I promise to avoid Crosstab Queries in future!