Question Sorting Issue

[Johnrg]

I have a report based on a simple query that lists product numbers and there associated details.

I have set the sorting option to ascending based on the product number.

The list looks like this:

52917
52918
52919
5292
52920

As you can see 5292 is a lower number than the others but is getting misplaced when sorted.

Is there anything I can do to fix this?

Thanks

JohnG

 

[HiTechCoach]

JohnG,

It is sorting properly for a text data type. To sort as a numeric data type you can try converting the field to a numeric value.

Try sorting on:

Val([YourFieldName])

 

[Johnrg]

Thanks for that answer.
Excuse my lack of knowledge but where do i put that code?
Thanks
JohnG

 

[Johnrg]

Anyone help me out on this one?

 

[Rabbie]

Just put it as a column in your query and sort on that column

 

[Brianwarnock]

In the sorting and grouping of the reort you can put
=Val([YourFieldName]) and select ascending

Brian

 

[Rabbie]

Sorry I missed it was report. Brian as usual has given the correct answer.

 

[Johnrg]

Thanks Guys,

I forgot to mention that I have a set of product codes that start with a alphabetical notation MSG:

If I sort with the =Val([Pack Number]) in the sorting and grouping options I am now getting:

MSG6234
MSG5332
MSG6235 so these product codes are now out of order followed by
4271
4273
51100
51101
51102 the straight numbers are now in order.

Not sure what to do here.

I need the numbers starting with MSG to sort ascending first in the report followed by the straight numbers to sort ascending afterwards

Any ideas?

 

[boblarson]

Thanks Guys,

I forgot to mention that I have a set of product codes that start with a alphabetical notation MSG:

If I sort with the =Val([Pack Number]) in the sorting and grouping options I am now getting:

MSG6234
MSG5332
MSG6235 so these product codes are now out of order followed by
4271
4273
51100
51101
51102 the straight numbers are now in order.

Not sure what to do here.

I need the numbers starting with MSG to sort ascending first in the report followed by the straight numbers to sort ascending afterwards

Any ideas?

Any other things you forgot to mention? If not, then you can get it with:

=Val(Replace([YourFieldName],"MSG", ""))

 

[Brianwarnock]

Bob, wont that sort the MSG fields amongst the pure number fields or am I missing something.

I thing that its back to Rabbie's suggestion of doing the sort in the query, it should stay the same for the report.
However you are going to have to build an artificial sort field, probably with a vba function, if the field statts MSG then stripping the MSG away as per Bobs method, else adding say 1 million to it if not, then the MSG1234 will be sorted in order ahead of the numbers sorted in order.

Brian

 

[Johnrg]

Guys,
Heres how the report looks at the moment.
JG

 

[boblarson]

Bob, wont that sort the MSG fields amongst the pure number fields or am I missing something.

No, I was missing the part about wanting all of those in a different location (thought they wanted it interspersed, but my bad).

 

[boblarson]

Guys,
Heres how the report looks at the moment.
JG

Nothing came out.

 

[Brianwarnock]

You want this in a query and sort ascending on this

Expr1: IIf(IsNumeric(Left([yourfield],1)),Val([yourfield])+100000,Val(Replace([yourfield],"msg","")))

You could try it in the report sorting ? remember the syntax there is =formula

Brian