1. Registered User
Join Date
Aug 2007
Posts
10

Hi,

im sure this must be a common mistake yet I cant find the solution anywhere so forgive me if this has been asked. I just cant find the answer.

Basically, I have a table and within the table a field called REF

This field contains a cod for example G007, G009, G095, G103, G1000 and so on so forth.

now when I query this and try to order this ascending or descending.

The G1000 is listed before G103. Can anyone explain?

Also this problem is the same if I take the "G" away.

jmcall10

2. Registered User
Join Date
Jul 2003
Location
Amsterdam, Nederland
Posts
450

## Re

The G1000 is listed before G103. Can anyone explain?
1st. its sorting is set to string
so it will compare ,on the 3th character, with eachother and the 0 is les then the 3
so G1000 wil come before g103
2nd . cast it to an integer after "deleting" the G, it will work for you

3. Registered User
Join Date
Aug 2007
Posts
10
Thank you so much for your response.

I would like to keep the G prefix to be honest.

Is there no way of changing the 3rd character thing when it sorts?

jmcall10

4. L33t Helpa Munky
Join Date
Nov 2007
Location
Posts
4,049
The basic problem is that you're sorting a text field so 1000 comes before 2 just as surely as AAAA comes before B.

The only way around it is to do as Marvels suggests. Make a calculated field that strips the G out, converts the result to be numeric and then sort by this calculated field. You don't have to show this calculated field, just sort by it. That way you will have what you want.

5. Registered User
Join Date
Aug 2007
Posts
10
I like this solution

How do I create this calculated field Stripping out the prefix G?

Thanks

jmcall10

Join Date
Nov 2004
Location
out on a limb
Posts
13,692
well you could try coercing the column to an integer type....
I don't know if it will work but try say

order by cint(mid\$(<mycolumnname>,2,4)

if the G applies to all fields then personally I'd get rid of it.. you can always add it back on reports, forms etc......

NUT you would still have chaneg the datatype of the column to a number to differentiate between 10000 and 11

7. www.gvee.co.uk
Join Date
Jan 2007
Location
UK
Posts
11,445
Healdem,
Wouldn't
Code:
`CInt(Mid\$(<col>, 2, Len(<col>)) )`
Be a bit better?

8. L33t Helpa Munky
Join Date
Nov 2007
Location
Posts
4,049
Or, using the Access interface, just put a new column in the query that takes out the first character:

SortField: CLng(Right([fieldname], Len([fieldname] - 1))

I like to try to keep things simple wherever possible!

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•