Find Greater Alphanumeric Character (1 Viewer)

[Djblois]

I have a sql query where I need to do a DSUM to create a running total. So it will add the value of the previous with its own. The issue is the field I comparing is an alphanumeric character it can be 200, 201, 202 but also 200A, 200B, etc.
So I cannot do a simple > symbol. how do I determine that 201 is greater than 200A and 200B is greater than 200A, etc.


Here is the sql that I need to modify:

RunningTotal: DSum("TotalAmount","qryRunningTotalForDailyCash","RouteID<=" & [RouteID])

btw RouteID is the one that is the alphanumeric field.

 

[Gasman]

? "200B" > "200A"
True
? "200" > "200A"
False

 

[theDBguy]

And...

?"201" > "200A"
True

 

[Gasman]

However unless you have set length values

? "50" > "200A"
True

so what does your data look like.?

 

[Micron]

if always numbers followed by letters, check out use of Val function. Maybe like
"RouteID<=" & Val([RouteID]))
https://www.techonthenet.com/access/functions/numeric/val.php

EDIT - aside from maybe learning something new, forget my suggestion. I overlooked that you need to sort 200A vs 200B and not just 200A vs 300A

 

[gemma-the-husky]

you can do a > with letters - it's just a dictionary sort which is what you want.

The problem is with numbers of different lengths then sort "incorrectly" as numbers. Is that wnat you mean?

so 1,2,3,4... `100,101,102 sort as
1,101,102,2,3,4

 

[rpeare]

I think there's a fairly easy solve here.

If you break your route number into the numeric portion and the alpha portion in your query, then sort on those two fields you should be golden.

i.e.

RN: left([Route_ID], 3)
RA: right([Route_ID], len([Route_ID]) -3)

This assumes the numeric portion is *always* 3 characters long. If it's not how is the routeID getting generated, is it possible for you to get the numeric portion independently and/or the alpha portion independently of looking at the Route_ID?

If neither of those is possible you can probably create a custom function to return the position of the last numeric value in your string and use that in the same formulas above.