Report Question

[gianina]

Hi All,

I have a problem with a report.
A insrted a chart into a report which shows total ECN's by MONTH.
The problem is that the chart contains the MONTH not in order (ex: January, February, March, etc).
It shows like (February, January, March).

This cannot be sorted (ascending or descending) in the query !!!

Any sugesstions how to make this work ?

Thanks.

 

[AlexCuse]

Is your month a text field? If so, I would add some kind of field to your query to sort on (or go through the process of building a string off of the Month, then converting it to date, then ordering by that).

Can you post the query you are using in your report?

Good Luck,

Alex

Professor: But what about your superintelligence?
Gunther: When I had that there was too much pressure to use it. All I want out of life is to be a monkey of moderate intelligence who wears a suit. That's why I've decided to transfer to Business School.
Professor: NOOOOOOOOOOOO.

 

[gianina]

Hi Alex,

Here's the query:

SELECT Count([ECN Database].[ECN #]) AS [CountOfECN #], [ECN Database].[Submit Date MONTH]
FROM [ECN Database]
GROUP BY [ECN Database].[Submit Date MONTH];

Yes, the month field is a text field.
Can you tell me how can I add a field to the query to sort on ? I've never done it before (I'm a beginer)

Thanks.

 

[AlexCuse]

If there is no date field that you can take datepart from to sort on, try this:

Format("January" & " 1, " & DatePart("yyyy",Date()),"mmyyyy")

the only change you want to make is replace my "January" with your [Submit Date MONTH].

So your query would be

SELECT Count([ECN Database].[ECN #]) AS [CountOfECN #], [ECN Database].[Submit Date MONTH],[COLOR=red]
Format([Submit Date MONTH] & " 1, " & DatePart("yyyy",Date()),"mmyyyy") AS SortField[/color] 
FROM [ECN Database]
GROUP BY [ECN Database].[Submit Date MONTH]
[COLOR=red]ORDER BY Format([Submit Date MONTH] & " 1, " & DatePart("yyyy",Date()),"mmyyyy")[/color];

Your table only contains data for one year, correct (or is year irrelevant to your question). I ask because you will need to bring in "Submit Date Year" somehow if you want to sort by month and year.

You also may need to group by the sort field expression, but I doubt it.

hope this helps,

Alex

Professor: But what about your superintelligence?
Gunther: When I had that there was too much pressure to use it. All I want out of life is to be a monkey of moderate intelligence who wears a suit. That's why I've decided to transfer to Business School.
Professor: NOOOOOOOOOOOO.

 

[gianina]

I replaced the query with the one you suggested and I have the following message:
"Undefined function "Format" in expression"

What should I do ?

 

[WildHare]

An undefined function message usually means a broken reference - open any module in the VBA editing window, click TOOLS / REFERENCES and make sure the "Microsoft DAO 3.6 Object Library" is checked ON.

Close and reopen the db and you should be ok.

This points out the problem with treating dates as text - as you found out, the sorting of TEXT months starts with APRIL...



--------------------------------------
"For a successful technology, reality must take precedence over public relations, for Nature cannot be fooled." - Richard P. Feynman

 

[AlexCuse]

An undefined function message usually means a broken reference - open any module in the VBA editing window, click TOOLS / REFERENCES and make sure the "Microsoft DAO 3.6 Object Library" is checked ON.

I could've sworn I posted this yesterday but Tek-Tips crashed on me as I was submitting. I should've come back to check. Thanks for following up!

Professor: But what about your superintelligence?
Gunther: When I had that there was too much pressure to use it. All I want out of life is to be a monkey of moderate intelligence who wears a suit. That's why I've decided to transfer to Business School.
Professor: NOOOOOOOOOOOO.