Dated Columns: 2000 & 2001 (1 Viewer)

[Foo Fighter]

People! Help me! Microsoft people can't figure this one out, so they sent me here!

I created a datafile containing the following entries:

Name, department, project, hours on project, date.

Since we are at the end of the year, some of the dates are, for example, 12/18/2000 and some of the dates are projected forecasts, such as 01/15/2001.

Fine. Great. Wonderful. Now, I look at any type of report in a crosstab query, and the report is sorting the column for 01/15/2001 before the column for 12/18/2000, I guess because it's reading the month, not the year.

How do you make the columns for dates in 2000 arrange before the columns for dates in 2001?

I would send you all a copy of the report as an example, but it contains names and projects and hours of US Government aerospace stuff. So you know that if our people can't figure it out, it's tough.

Unless of course someone here can rise to the challenge - HELP!

Thanks,

Foo Fighter

 

[Shep]

I am no Report expert, but I created a report based on a crosstab query (in turn based on a table with a field that contained the two date examples you gave) and it sorted the dates correctly.
However, I created a simple dropdown report and the field sorts incorrectly as you described. I invoked the query builder on the report Control Source (the table) and dropped the date field into the query, sorted Ascending, and it works fine. So, my guess is that the answer is to do your sorting on the query underlying the report. I used Short Dates, by the way. Good luck!

[This message has been edited by Shep (edited 12-05-2000).]

 

[Richie]

Add the following to the query and sort by that
Dte: Year([Tablename].[FieldDate])*12+DatePart("m",[TableName].[FieldDate])-1

 

[Foo Fighter]

Thanks to both Shep and Richie for your solutions. I'll try them both and see what happens.

Foo Fighter

 

[Dreamboat]

Hi Foo! Doh! Are you thinking MSN is MS? Wrong! MSN is just like here. People trying to help other people. Did you see their new format? Way bad in my opinion. Not trying to bash another site, but in the course of about 2 days, they've changed it and it's very difficult to read anything now.

[This message has been edited by Dreamboat (edited 12-07-2000).]

 

[Foo Fighter]

Hi Dreamboat,

I . . . don't . . . know . . . why you're freaking out! Ummm, ok whatever. Everybody in both of the forums was very helpful and gave all kinds of good advice, including you.

I did get a couple of viable solutions here, which are being tested now, so we'll see which one works.

I'm tired and I'm glad it's the weekend, so I'm going out to get a glass of wine tonight and try to have some fun. Everybody have a good weekend, OK?

Happy Holidays,

Foo Fighter