How to determine order of two fields?

[MsAxes]

I have a table with field CustA and CustB, both fields are text/number combination (see example below). There are times (rare) where a field can be blank.

CustA CustomB
asd123456egfpl 21564zsdfaweq
pk21654sw5121 plsad15646512
12wasd1456712 0odsafa131asd1
54dwesa564646

I also have a table named Sort Order. The purpose of this table is to identify which customer is first based on the order number the character has.

order Character
1 z
2 y
3 x
4 w
5 v
6 u
7 t
9 s

So I would like a new field (Flag) to tell me which customer is first based on the Sort Order table. Example, if CustA has the letter z before CustB then CustA is first based on the table. If they both have Z in the same occurrence, then continue looking at the Sort Order table until one has a character in the order before the other.



CustA CustomB Flag
asd123456egfpl a1564zsdfaweq CustA First
uk21654sw5121 wlsad15646512 CustB First
12wasd1456712 0odsafa131asd1 CustA First
54dwesa564646 One record is null

I tried to Google and look for examples and can't find anything to get started.

[June7]

I think this will need a VBA custom procedure.

[Gicu]

How\why is CustA first in the first row example? B has "z" in position 6.

[June7]

Maybe letter must be in both strings. First letter in both strings of record 1 is "s" and it is in 2nd position in CustA, 7th in CustB. Dang, that falls apart in 3rd record.

[CJ_London]

why does this feel like an exercise that has nothing to do with customers, the data is nothing like what is being shown and there will be all sorts of other issues not covered

explain how you have determined which customer is first - on the first line you said

Example, if CustA has the letter z before CustB then CustA is first based on the table.

So CustA does not have a z, but is first- why?

Does case matter? ('Z' v 'z')
are numbers to be ignored?
are strings always of the same length - or doesn't it matter?