Month order in a chart

[jmc014]

Hi,

I'm trying to make a real simple chart from a table in a report, the problem being that the month order changes automatically. This is driving me crazy...

The feilds are:

Month (Text)
Total (Number)

I've tried about everyting but I cannot stop access from alphabetically ordering my month field in the chart.

I've looked out loads of threads but cannot find the solution that I'm looking for.

 

[MazeWorX]

For months I create a tblMonth

Fields

MonthID Primary Key - Numeric
MonthDescription - Text

1 - January
2- February

Etc.

I write the ID field to my records and display the description for my users then sort on the ID field

HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work" <<Edison>>

 

[jmc014]

Thanks but I still get the same results in the chart.
Gotta be doing something wrong as the first month is not Jan but Aug.

?

Thanks,

 

[MazeWorX]

sort the query by monthID

HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work" <<Edison>>

 

[jmc014]

Already did that but the chart still begins with the Aug, just as I said, this is driving me nuts.

 

[dhookom]

jmc014,
The sort order depends on the Row Source of the chart. You need to provide us with the Row Source and the data types of the fields.

IMO, storing a month value with the data type of text is never a good idea.

Duane
Hook'D on Access
MS Access MVP

 

[MazeWorX]

post your month table as well

HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work" <<Edison>>

 

[SkipVought]

Strongly agree that data related to dates, like month, ought to be stored as DATE, and then FORMATTED if a custom DISPLAY is required.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[MazeWorX]

I like the month table because a user can select a month because it uses a month id it can easily search a Date field but imo solves the sort issue. I wasn't suggesting he store dates as text the ID is numeric 1 -12 and he then uses MonthDescription to display to his users. Date after all is simply a numeric field so with a Month table we are comparing numeric to numeric

HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work" <<Edison>>

 

[Gammachaser]

Maybe I am missing something here because most of you are way better at Access than I am -- but why not just store the month as a number (assuming you really don't want to store a date which I agree is preferred) and then use the built in function MonthName?

Monthname(1, False) or just Monthname(1) 'returns 'January'
MonthName(1, True) 'returns 'Jan'

If you store a date, you can still get the name using:

MonthName(Month([datefield]),True) 'returns the abbreviation
MonthName(Month([datefield]),False) 'returns the long name
OR
Format([datefield],"mmm") 'returns the abbreviation
Format([datefield],"mmmm") 'returns the long name.

No table required for either case. Order by the month number and display MonthName(MonthNumber)

N'est-ce pas?

 

[MazeWorX]

I think we are all on the same page here more or less so until jmc014 posts his code we really cant solve his sort issue

HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work" <<Edison>>