de**********@yahoo.com wrote:
I have a unique identifier consisting of regions, districts, a road
class value and then sequential numbers, in that order, eg:
GA-GAD-C-005 The thing is that the sequential values don't follow onto
each other as it does the sorting on the road class value first (which
could be A, I or C).
Warning - this is how I do it - if anyone sees a better way, for the
love of all that's holy, please dress me down and correct me!!!!
I didn't understand what you were saying after this, but in the example
you give, I'm assuming C is the road class, district is GAD and region
is GA. I'm also going to assume that any of these identifiers can vary
in length (it's easier if they are constant) and that they are always
separated by dashes and that road class will always be *AFTER* the
*SECOND* dash.
The following makes use of the instr() and mid() functions. Look them
up and familiarize yourself with them. The following may be hard to
follow, but it is (one line, no breaks mean to be in place:
mid((mid([Identifier],instr(1,[Identifier], "-")+1)),instr(1,
(mid([Identifier], instr(1,[Identifier], "-")+1) ),"-")+1)
Which will, for the above example, give "C-005" as a result. You can
put this as a calculated expression somewhere in the query builder and
sort by it.
To ease the confusion the above may cause, look at how it's built.
mid takes a portion out of a specified string and instr returns a number.
1. the first instance of a dash: instr(1,[Identifier], "-") will
return the number 3
2. to use this number to get stuff coming after that dash, we want to
take the example and start at position 4, so add one to expression #1
and use it as the start position in mid():
mid([Identifier],instr(1,[Identifier], "-")+1)
This will return "GAD-C-005"
3. Let's repeat step #1 to find the location of the second dash in
what's remaining, "GAD-C-005":
instr(1, (mid([Identifier], instr(1,[Identifier], "-")+1) ),"-")
This results in a 4 which is the position of the "-" in "GAD-C-005".
4. You want to start at position 5, so add 1 to the expression in #3
for 5. Now, use the mid expression to find what results in position 5
and onward of the expression derived in #2:
mid((mid([Identifier],instr(1,[Identifier], "-")+1)),instr(1,
(mid([Identifier], instr(1,[Identifier], "-")+1) ),"-")+1)
This sort of thing can be confusing with all the parenthesis.
IS there a better way? Probably, but I've been doing this sort of thing
this way for years...
--
Tim
http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me