How do I extract targeted characters from a field value in a Microsoft Access query?
I need to run a query and extract a numeric value from a field in a transactions table using Access 2007. The value that I need is part of a larger string of alpha-numeric characters. The new query results, therefore, will contain the extracted value that I will use to look up other values in other tables.
Here are the details:
1) The value in the source field is variable length and alphanumeric in the form of ABC123, ABC12, ABC1, AB123, AB12, or AB1.
2) I need to extract ALL the numeric characters from right to left. In the examples above, for example, the query returns 123, 12, 1, 123, 12, 1, respectively.
In query design mode, what should I enter in the calculated FIELD: and CRITERIA: to return the value that I need?
I would sincerely appreciate any help offered. I'm a rookie trying to learn on the job!
Here are the details:
1) The value in the source field is variable length and alphanumeric in the form of ABC123, ABC12, ABC1, AB123, AB12, or AB1.
2) I need to extract ALL the numeric characters from right to left. In the examples above, for example, the query returns 123, 12, 1, 123, 12, 1, respectively.
In query design mode, what should I enter in the calculated FIELD: and CRITERIA: to return the value that I need?
I would sincerely appreciate any help offered. I'm a rookie trying to learn on the job!
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
BTW, I think hnasr's formula can be reduced:
SELECT txtFld, Val(StrReverse(Val(StrReve
FROM theTABLE;
SELECT txtFld, Val(StrReverse(Val(StrReve
FROM theTABLE;
ASKER
Thanks guys. I'm getting a syntax error. Will you please take a look. See attached pdf.
ScreenShot-extract-office-no.pdf
ScreenShot-extract-office-no.pdf
You need to switch to SQL view, and then paste in hnasr's code (changing for actual column/table names, of course).
ASKER
It worked! That is so cool. Thank you gentlemen. I really appreciate the help.
That is positively *brilliant*.
Won't work if the string is like ABC123H, as mine will, but then again the Asker did not indicate that the string may go <letters><digits><letters>
Patrick