sorting numeric data in text field 2

[ChewDoggie]

G'day all!

I've searched the archives but nothing seems to fit my issue (exactly), so I'm posting anew.

I'm trying to sort on a text field (called 'Comp') which contains numbers and/or letters and/or special characters. 'Comp' field length = 4. Right now, I'm using a shaped query to produce the resultset in order of "Comp" but I'd like to modify the query to "properly" sort the 'Comp' text field.

The current sorting creates output like this:

1
2
3
38
39
3A
4
...etc.

which should look like:

1
2
3
3A
4
38
39

Here's what my query looks like currently:

sql = "SHAPE {SELECT trim(ventrylist.fname) + ' ' + trim(ventrylist.lname) as fullName, trim(ventrylist.city) + ', ' + trim(ventrylist.state) as hometown, brands.brandid + IIf(ventrylist.bikesize = '' or ventrylist.bikesize = '0', '', ' - ' + ventrylist.bikesize + ' cc') as bike, ventrylist.sponsors, ventrylist.description, ventrylist.classid, ventrylist.comp, IIF((racer.bdayyy + racer.bdaymm) > 0, Int((DateDiff('d', (bdaymm & '/1/' & bdayyy), Date()) / 365.25)), 'N/A') as RacerAge FROM ((vEntryList LEFT JOIN brands on brands.brandname = ventrylist.brandname) INNER JOIN racer on racer.racerid = ventrylist.racerid) where ventrylist.eventid = " & entId & " and ventrylist.status = 'Y' order by ventrylist.comp} AS vEntryList COMPUTE vEntryList BY 'description', 'ClassID'"

But don't let the above query fool you. I learned how to do shaped queries but really, I'm no good with the Access query syntax, so any help would be greatly appreciated!

AMACycle

American Motorcyclist Association

http://www.ama-cycle.org

 

[Golom]

You could just

order by [COLOR=red]Val([/color]ventrylist.comp[COLOR=red])[/color]

But there's still the issue of
[tt]
3
3A
[/tt]
versus
[tt]
3A
3
[/tt]
because "Val" will produce the value "3" for both and the ordering is indeterminant.

 

[ChewDoggie]

3A" being listed before "3" isn't that much of an issue. The fact that the rest of the data is sorting correctly is a HUGE FIX.

Thanks GOLOM!!



AMACycle

American Motorcyclist Association

http://www.ama-cycle.org

 

[SkipVought]

AMACycle,

As a programmer, surely you understand WHY this is happening, right?

You should enter numeric characters (text) in a way that will collate as you expect for that text field. Otherwase, you end up in the afore stated predicament.

Skip,

_{[red]Be Advised![/red] A man's home is his castle...
in a Manor of speaking.}

 

[ChewDoggie]

text fields are evaluated left to right and numeric fields are evaluated right to left.

AMACycle

American Motorcyclist Association

http://www.ama-cycle.org

 

[SkipVought]

Not really.

Strings are evaluated left to right (byte by byte), but numbers are numbers (one byte, two bytes, four bytes, eight bytes, etc) and the VALUES are ranked.

Skip,

_{[red]Be Advised![/red] A man's home is his castle...
in a Manor of speaking.}

 

[PHV]

And what about this ?
ORDER BY Val(ventrylist.comp),ventrylist.comp

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[ChewDoggie]

AAH...VB (et al) different than assembler language.

Thanks for the input...It's appreciated!!

and special THANKS to PHV who corrected the "3A"/"3" issue. That works like a charm.



AMACycle

American Motorcyclist Association

http://www.ama-cycle.org