Formatting Charts in a Form

[denveromlp]

Hello,

I have a form that creates a chart for me that displays the number of occurances (y-axis) by month (x-axis). The data is retrieved from a make-table. When the chart is created it plots the months on the x-axis in alphabetical order (Apr-Sep). How do I change it so it orders the months correctly (Jan-Dec).

(In the make-table, the data is listed properly from Jan to Dec)

Thanks!

[NeoPa]

Convert your month strings into dates (all for the same year - it doesn't matter which one) then order by that value rather than the strings.

Use CDate() to convert from a string to a Date.

[denveromlp]

Convert your month strings into dates (all for the same year - it doesn't matter which one) then order by that value rather than the strings.

Use CDate() to convert from a string to a Date.

I think that is what I am doing in a round about way. I have a field for Month([DATE]) which returns values 1-12. That field is ordered ascending and is not shown. Then I have a field consisting of successive "IIF" statements to convert the Month([DATE]) into words.

Ex. IIF(Month([DATE])=1, "Jan", IIF(Month([DATE])=2, "Feb",.......IIF(Month([DATE])=12, "Dec"

When I run the make-table it orders by the unshown numeric field and displays the text field. The table is then ordered correctly, but when I send it to the graph form it alphabetizes the text months.

[NeoPa]

When you "send it to the graph" ensure that it has the already calculated month number to work from.

When converting from a numeric month (Assume it is in variable intMonth) simply use :

Format(CDate(intMonth & "/21/2000"), "mmm")

NB. Using a day of 21 means this should work whether the standard is d/m/y or m/d/y. Also, the CDate() part is not strictly necessary, but it makes the code clearer.

[denveromlp]

Format(CDate(intMonth & "/21/2000"), "mmm")

This is definitley a better way to convert month (1-12) into (Jan-Dec), but it still does the same thing as before. When the make table is generated, it successfully lists the months Jan - Dec. But when the chart is created from the table in a form, it still aranges the months on the x-axis alphabetically (apr-sep)

[NeoPa]

When you "send it to the graph" ensure that it has the already calculated month number to work from.

My first sentence is the one to focus on for that problem.

I know nothing about charts per se, yet it's obvious to me, as someone who deals with other database entities, what needs doing in general terms.

I can't give more detailed instruction at this stage, although I would expect I could if you had posted the code you're currently using. Then it might be obvious what small thing needs to change.

Generally posting questions is about helping us to help you. If you put in the bare minimum, then you're less likely to get what you need.

[denveromlp]

SELECT Format(CDate(Month([Date_NextOH]) & '/21/2000'),'mmm') AS Monthtext,
       Count(Month([Date_NextOH])) AS OHMonth,
       Year([Date_NextOH]) AS Year
 
INTO tbl_OHbymonth
 
FROM tbl_totatOHDates
 
GROUP BY Format(CDate(Month([Date_NextOH]) & '/21/2000'),'mmm'),
         Year([Date_NextOH]),
         Month([Date_NextOH])
 
HAVING (((Year([Date_NextOH]))=[Forms]![frm_Main]![tb_graphyear]))
 
ORDER BY Month([Date_NextOH])

That is the SQL. The query is generating the make-table correctly, the months are listed Jan-Dec. Then a form runs this make-table and graphs it, with months along the x-axis. However, it lists the months alphabetically instead of retaining the order they are listed in the make-table.

I'm honestly trying to be as specific as possible, but that's really all there is to it. If there is anything that could be clearer, please let me know.

[NeoPa]

I'm honestly trying to be as specific as possible, but that's really all there is to it. If there is anything that could be clearer, please let me know.

OK. I can work with that.

You keep referring to a Make-Table query, yet the SQL posted is a SELECT query. Is the table [totatOHDates] the one created by the MT query?

Is the SQL posted what your graph is built on?

[denveromlp]

You keep referring to a Make-Table query, yet the SQL posted is a SELECT query.

I'm not sure why it says SELECT in the SQL, it is definitely a successfully functioning make-table query, qry_graphbymonth generates tbl_graphbymonth.

Is the table [totatOHDates] the one created by the MT query?

No, totatOHDates is a seperate table that this query pulls raw dates from before counting the number of occurences in each month.

Is the SQL posted what your graph is built on?

No, that SQL is just the a query that counts how many date data points fall within each month of the year. The year is specified from the form by the criteria "[Form]![frm_main]![tb_graphyear]"

Then a form uses a command button to run the above query (generateing the make-table) and run a seperate form to graph the make-table. The code for that is as follows:

Private Sub tb_graphyear_Exit(Cancel As Integer)
DoCmd.SetWarnings False
DoCmd.OpenQuery "qry_OHbymonth"
DoCmd.OpenForm "frm_graphbymonth"
DoCmd.SetWarnings True
End Sub
 

Finally, the code for "frm_graphybymonth" is just a graph that was created with the chart wizard to plot the months on the x-axis, and the number of data points occuring in that month on the y-axis. This form also operates correctly except it orders the months alphabetically.

[NeoPa]

Now I have clarified the SQL it is clear that it actually IS the Make-Table query.

Let me start simply by posting some equivalent SQL that should be a little neater, but have the same effect :

SELECT Format([Date_NextOH],'mmm') AS MonthText,
       Count(Month([Date_NextOH])) AS OHMonth,
       Year([Date_NextOH]) AS Year
 
INTO tbl_OHbymonth
 
FROM tbl_totatOHDates
 
WHERE (Year([Date_NextOH])=[Forms]![frm_Main]![tb_graphyear])
 
GROUP BY Format([Date_NextOH],'mmm'),
         Year([Date_NextOH])
 
ORDER BY Month([Date_NextOH])

Grouping by the month value again was redundant.
The HAVING clause should really have been a WHERE clause (You probably have Access to blame for that - It routinely uses HAVING for WHERE in GROUP BY queries).

[denveromlp]

Yeah, this was created in design view, then I just copied the SQL.

I'm concerned the problem lies in some setting the chart wizard uses that tells it to alphabetize any text field it graphs, the order seems to stay correct until that point.

[NeoPa]

Finally, the code for "frm_graphybymonth" is just a graph that was created with the chart wizard to plot the months on the x-axis, and the number of data points occuring in that month on the y-axis. This form also operates correctly except it orders the months alphabetically.

I need to see what you use as the Record Source of this form.

In here will need to be the correct Ordering By. I'm a little out of my depth with the form itself, but if I can see the SQL of that query then I'm confident I can provide one similar except that it sorts in the way you want it.

[denveromlp]

Figured it out...thanks to your guidance.

The make-table was passing the numerical month that was sorted ascendingly to the graph, but it was not selected in the row source of the graph, only the text month. Got it fixed.

Thanks for your help!

[NeoPa]

My pleasure, but just to clarify in case someone else views this :

The order of the make-table query is entirely irrelevant. The order it goes into the form (controlled either explicitly by a setting in the form or, if that's absent, in the order of the record source of the form) is what needs to be maniplulated to effect this change.