Query to determine individual values within a range

[mpdsal via AccessMonster.com]

I am relatively new to Access. I can join tables and run queries but I am
stumped on this one. I have a SAP table downloaded called Roles that contain
a range of values for transactions. There is another table with all the
transaction values. The Roles table has a value From and To that can contain
wildcards such as "*". The "F-52,54,57" etc. type transactions are actual
values. So, F-54 to F-57 would be F-54, F-55, F-56, F-57. My dilemma is I
need to decode the From To so that I can get the appropriate values from my
Transaction table. This has to be clever enough to decode the range and
lookup any values that fall within that range. Here is what one role has for
ranges. All transaction codes are alphanumeric. Any ideas? Thanks

ROLE FROM
TO
50000060 0
AL09
50000060 AL11
AL13
50000060 AL16
CKMD
50000060 DB05
DB07
50000060 DB12
F-52
50000060 F-54
F-57
50000060 F-59
F109
50000060 F111
FD00
50000060 FD03
FD31
50000060 FD33
FK00
50000060 FK03
FY1R
50000060 FY1R
F109
50000060 FYLT
HQ*
50000060 HS*
KP05

 

[Guest]

Hi mpdsal,

I think you are going to struggle to come up with any code to decifer that
lot, for example what comes next between FY1R and F109, if every alphanumeric
is used in order of a letters then number, there are thousands of *possible*
values in between.

Your best bet is to create another table with all the possible values in
order, and then compare your data to that. Even then, you've punctuation and
wildcards to work around.

good luck, you'll need it!!!!

TonyT..