BETWEEN function on Text field with alphanumeric string

[simon1974]

I will try to explain this a clearly as I can. Hopefully someone can help me in my task. I have a "rollup" table that contains individuals, each with a low field and a high field, which define the range of employee numbers that report to each of the individuals. I have another table with all of the employees of the organization, each with a unique code, which falls within the various high/low ranges defined in the first table. My problem is this, I am working from mainframe data and the SAS programmers have used alphanumeric combinations for the rollup. An example is as follows:

from table 1:
Name Low High
John Smith 146CAA000000 1460AA0000000

An employee with ID 146DAA000000 would fall in this range if it calculated correctly.

The problem is that the SAS mainframe environment evaluates alpha before numeric, however it appears access is evaluating numeric before alpha which makes the above range not make much sense. Is there a setting or switch I can manipulate to change that? Any suggestions?

 

[clientuser]

What you could do is do a left function to take out the 146 which appears to be the standard before any alpha charcters, do the between function on the strings you just took out and go from there...

heres a example to the left function(taken from ms access help file):

Dim AnyString, MyStr

AnyString = "Hello World" ' Define string.

MyStr = Left(AnyString, 1) ' Returns "H".
MyStr = Left(AnyString, 7) ' Returns "Hello W".
MyStr = Left(AnyString, 20) ' Returns "Hello World".

So in your case you could do:

Left(Anystring, 3) to take out the 146, now you can also do another function to find the first alpha in a string if you dont like to have it set to take only the first 3 from it...

hope this helps...

 

[simon1974]

Thanks... even if I take out the 146, though, I am still left evaluating the next three characters, which in the low is DAA and the high is 0AA (that first character is a zero). Even here, access would evaluate the 0AA to come before the DAA, but the SAS environment evaluates it and has set up the tables the other way, with DAA being first and 0AA coming after. I wish there was a way to ask Access to conform to the alpha first schema. Thanks, let me know if you have anymore ideas.

 

[MichaelRed]

w/o knowing the sequence assignment, any attempt to casually decode the string has a high probability of failure -at least at some point. The range "asignmets" are probably set up w/ some pattern, such as EmpId & DeptId & ReportID.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[Shortie]

You could try transposing the data from SAS

0=A
1=B
2=C
3=D
...
9=J

A=01
B=02
C=03
....
Z=06

SAS ACCESS
"146AAD00000" = "BEG010104AAAAA"
"1460AD12345" = "BEGA0104BCDEF"
"1461AD12345" = "BEGB0104BCDEF"
"123ABC98765" = "BCD010203JIHGF"
"146ZYX24680" = "BEG262524CEGIA"
"644AAA83843" = "GEE010101IDIED"