troubleshooting Question

How do I extract a numeric value from a field in Access 2007?

Avatar of thutchinson
thutchinsonFlag for United States of America asked on
Microsoft AccessSQL
18 Comments1 Solution1017 ViewsLast Modified:
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
FROM tblSales;
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?

Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 18 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 18 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros