How do I extract a numeric value from a field in Access 2007?
Posted on 2010-09-10
OOps. I closed the following question too early:
"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."
I used SQL code provided by experts that Access translated from SQL as follows::
SELECT tblSales.Office, Val(StrReverse(CStr(Val(StrReverse([Office]))))) AS OfficeNo
The results looked good until I noticed that the final zeros were being truncated. For example, ABC30 became "3" instead of "30" and AB10 become "1" instead of "10".
Can someone help me refine this code to not drop the ending zero? Can the numeric value be formatted as Text instead of a number?