How to sort chronologically 1

[mongous]

I am trying to offer users a combo-box to choose a month for which to report data. I use this SQL:
[red]
SELECT DISTINCT [tblRepData].[Month] FROM tblRepData;
[/red]

This pulls the months, but it sorts them alphabetically in the list. I want them to display in order (Jan,Feb,Mar,etc)

Can anyone help me out? Thanks.

 

[SgtJarrow]

You need something like....

SELECT tblRepData.Month
FROM tblRepData
ORDER BY Month([Month]);

****************************
Computers are possibly the most un-intelligent things ever invented, yet we let them control the world. Possibly a reflection of our own stupidity.

Robert L. Johnson III
MCSA, CNA, Net+, A+
w: rljohnso@stewart.com
h: wildmage@tampabay.rr.com

 

[mongous]

Yeah, but that orders them alphabetically...
I am wanting to keep them in chronological order..

I wonder if I need to convert the text to a numerical value???

 

[SgtJarrow]

the month() function returns the month value of the month field you supply....ie 1, 2, 3, 4...an then these are sorted numerically


****************************
Computers are possibly the most un-intelligent things ever invented, yet we let them control the world. Possibly a reflection of our own stupidity.

Robert L. Johnson III
MCSA, CNA, Net+, A+
w: rljohnso@stewart.com
h: wildmage@tampabay.rr.com

 

[mongous]

Hmmm... but then I get the error: the MONTH clause conflicts with DISTINCT

 

[mongous]

I MEAN it says: The ORDER BY clase conflicts with the DISTINCT

 

[SgtJarrow]

yes....that is true....

I found that also.....can you run the distinct query, then immediately send that to a sort process....basically a two step query...the first to distinct, the second to order.

****************************
Computers are possibly the most un-intelligent things ever invented, yet we let them control the world. Possibly a reflection of our own stupidity.

Robert L. Johnson III
MCSA, CNA, Net+, A+
w: rljohnso@stewart.com
h: wildmage@tampabay.rr.com

 

[PaulBricker]

How about
SELECT DISTINCT Month([tblRepData].[Month])
FROM tblRepData;

Paul

 

[mongous]

Hmmm. I am trying but having trouble with the syntax,

 

[PaulBricker]

It could be the field [Month]. Is that derived from a date field? How are the values in that field stored? Can you rename it dteMonth or something. Month is a reserved word in Access so you may be running into a conflict there.

Paul

 

[mongous]

Oh, well PaulBricker:

Thanks for the reply. And thanks to mstrmage1768 as well.
My last reply was before I saw your FIRST. But I did try that and got the same result I get by doing a two step query. It orders it numerically, (1,2,3,4,5,6,7,8,9,etc..)

I can't seem to format it to display 'mmmm yyyy'...

 

[PaulBricker]

I believe you can order it by the numeric field and then create another field that displays it in the format you want. Then just set the Show check box in the query to No for the numeric field.

Paul

 

[mongous]

But I keep gettin it to display only two values:
December 1899 and January 1900...

 

[PaulBricker]

Well that's probably not much help. Tell us a little more about the query. Maybe even post the SQL so we can look at it.

Paul

 

[mongous]

Ok, so here it is so far:
[red]
SELECT DISTINCT Month([tblRepData].[Month]) AS IndexedMonth
FROM tblRepData
GROUP BY Month([tblRepData].[Month]);
[/red]

Obviously that is without the Format. All i am trying to do is pull the month and year to populate a drop-down box. The [Month] field it is pulling from is a field that I created just to aid in reporting date ranges. The data for the field is added via an append query that I run. I used the Format function to get the output how I want it (i.e. January 2003, February 2003, etc) from the date field (data is imported daily).

Does that help at all? Thanks.

 

[PaulBricker]

So can you try this expression
IndexedMonth:Month(Format([DateField], "mmmm yyyy&quot)

Paul

 

[mongous]

No, that just got me the same result (1-10). But I don't think it would work anyway, cause in the table there are hundreds of entries for each 'January 2003', etc. Because the query pulls the data PER REP there is an entry for every rep every day... So there could be thousands of entries in a month.

Make sense?

 

[PaulBricker]

You won't be able to sort the text field in chronological order so you will need to sort from a numeric result set. Can you get the 1 - 12 values and the formatted January 2003 in the same query? If you can you can sort by the 1-12, set it to invisible and just show the January 2003 formatted field.

Paul

 

[mongous]

Well, that's what I stumbled onto, but I can get it to work within the query itself (click the ! and it shows in order - if I keep it visible), but as soon as I set it invisible the months show alphabetically. Also the form still only shows the numeric values regardless...

 

[PaulBricker]

Is this the Record Source for a Combo box we are working on? Do you have a full date field in tblRepData?

Paul