Change Report Chart X Axis Order

[duece7]

Hi everyone,

I have a chart like the one below where I want to change the sort order of the bottom data to be custom so I can make it 1, 2, 3, and not 1, 10, etc.

Any ideas?

[June7]

Options:

1. convert the values to number type, either change the field type or use a function to CAST the data to a number type - Val(fieldname)

2. format the values to have leading filler zeros, like: 01, 05, 10 - Format(fieldname, "00")

[duece7]

Thanks for the advice, only problem is there is text in this field as well. Some of the charts it populates has text that was counted as well as numbers. Any other ideas?

[June7]

Try option 2 anyway. If the value is non-numeric, like "ABC", it should be unaffected by the Format() function.

Use however many zeros are needed for the largest number value to be encountered. Digits will always sort before alphas.

[duece7]

Nothing happened.

Here's my row source data/query for the charts.

[June7]

The CInt() calculated field does not have CROSSTAB parameter. What is it for? Should remove it?

[duece7]

Yeah I think I put it in there a while back to help sort something else that wasn't sorting properly. I took it out and it doesn't look like it messed anything up but the numbers on the chart still go 1, 10, etc..

[June7]

Are the leading zeros in the query datasheet view?

The function works for me in a normal SELECT query. AFAIK, should work in the CROSSTAB.

If you want to provide db for analysis, follow instruction at bottom of my post.

[duece7]

For some reason I couldn't get it to work with just formatting but doing the following worked. Thanks for your help and getting my noodle going to put this together.

=iif(len(fieldname)<=2,right("00" & fieldname,2),fieldname)

That resorted the chart headers and kept the long text headers when they weren't numbers