Solved

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

Posted on 2010-09-10
18
976 Views
Last Modified: 2013-11-27
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?


0
Comment
Question by:thutchinson
  • 7
  • 4
  • 3
  • +2
18 Comments
 

Author Comment

by:thutchinson
Comment Utility
The SQL code that I used came from hnasr (with help from MathewsPatrick) as
SELECT txtFld, Val(StrReverse(CStr(Val(StrReverse(txtFld)))))
FROM theTABLE;
Substituting my field and table names I entered:
SELECT Office, Val(StrReverse(CStr(Val(StrReverse(Office)))))
FROM tblSales;

Ref: ID # 26463615
0
 
LVL 119

Accepted Solution

by:
Rey Obrero earned 500 total points
Comment Utility
place this  function in a regular module (module 1)

Function getNumeric(s as string)
if len(s & "")=0 then exit function

dim j, nStr as string, vNum
nStr="1234567890"

for j=1 to len(s)
       if instr(nStr,mid(s,j,1))>0 then
          vnum=vnum & mid(s,j,1)
      end if
next
getNumeric=vNum
end function


to use in your query

SELECT tblSales.Office,getNumeric([Office]) AS OfficeNo
FROM tblSales;
0
 
LVL 92

Expert Comment

by:Patrick Matthews
Comment Utility
thutchinson,

If you try the RegExp-powered approach I described in your previous question, you will find that it works.  Repeating it here...

-------------------

This may be a good slot for Regular Expressions.  Please see my article:

http://www.experts-exchange.com/Programming/Languages/Visual_Basic/A_1336-Using-Regular-Expressions-in-Visual-Basic-for-Applications-and-Visual-Basic-6.html

From that article, add the RegExpFind function to a regular VBA module (source code below).

You can then use it in a query like this:

SELECT OriginalColumn, RegExpFind(OriginalColumn, "\d+", 1) AS DigitsOnly
FROM SomeTable

Please see the attached file for an example.
Function RegExpFind(LookIn As String, PatternStr As String, Optional Pos, _
    Optional MatchCase As Boolean = True, Optional ReturnType As Long = 0, _
    Optional MultiLine As Boolean = False)
    
    ' Function written by Patrick G. Matthews.  You may use and distribute this code freely,
    ' as long as you properly credit and attribute authorship and the URL of where you
    ' found the code
    
    ' For more info, please see:
    ' http://www.experts-exchange.com/articles/Programming/Languages/Visual_Basic/Using-Regular-Expressions-in-Visual-Basic-for-Applications-and-Visual-Basic-6.html
    
    ' This function relies on the VBScript version of Regular Expressions, and thus some of
    ' the functionality available in Perl and/or .Net may not be available.  The full extent
    ' of what functionality will be available on any given computer is based on which version
    ' of the VBScript runtime is installed on that computer
    
    ' This function uses Regular Expressions to parse a string (LookIn), and return matches to a
    ' pattern (PatternStr).  Use Pos to indicate which match you want:
    ' Pos omitted               : function returns a zero-based array of all matches
    ' Pos = 1                   : the first match
    ' Pos = 2                   : the second match
    ' Pos = <positive integer>  : the Nth match
    ' Pos = 0                   : the last match
    ' Pos = -1                  : the last match
    ' Pos = -2                  : the 2nd to last match
    ' Pos = <negative integer>  : the Nth to last match
    ' If Pos is non-numeric, or if the absolute value of Pos is greater than the number of
    ' matches, the function returns an empty string.  If no match is found, the function returns
    ' an empty string.  (Earlier versions of this code used zero for the last match; this is
    ' retained for backward compatibility)
    
    ' If MatchCase is omitted or True (default for RegExp) then the Pattern must match case (and
    ' thus you may have to use [a-zA-Z] instead of just [a-z] or [A-Z]).
    
    ' ReturnType indicates what information you want to return:
    ' ReturnType = 0            : the matched values
    ' ReturnType = 1            : the starting character positions for the matched values
    ' ReturnType = 2            : the lengths of the matched values
    
    ' If you use this function in Excel, you can use range references for any of the arguments.
    ' If you use this in Excel and return the full array, make sure to set up the formula as an
    ' array formula.  If you need the array formula to go down a column, use TRANSPOSE()
    
    ' Note: RegExp counts the character positions for the Match.FirstIndex property as starting
    ' at zero.  Since VB6 and VBA has strings starting at position 1, I have added one to make
    ' the character positions conform to VBA/VB6 expectations
    
    ' Normally as an object variable I would set the RegX variable to Nothing; however, in cases
    ' where a large number of calls to this function are made, making RegX a static variable that
    ' preserves its state in between calls significantly improves performance
    
    Static RegX As Object
    Dim TheMatches As Object
    Dim Answer()
    Dim Counter As Long
    
    ' Evaluate Pos.  If it is there, it must be numeric and converted to Long
    
    If Not IsMissing(Pos) Then
        If Not IsNumeric(Pos) Then
            RegExpFind = ""
            Exit Function
        Else
            Pos = CLng(Pos)
        End If
    End If
    
    ' Evaluate ReturnType
    
    If ReturnType < 0 Or ReturnType > 2 Then
        RegExpFind = ""
        Exit Function
    End If
    
    ' Create instance of RegExp object if needed, and set properties
    
    If RegX Is Nothing Then Set RegX = CreateObject("VBScript.RegExp")
    With RegX
        .Pattern = PatternStr
        .Global = True
        .IgnoreCase = Not MatchCase
        .MultiLine = MultiLine
    End With
        
    ' Test to see if there are any matches
    
    If RegX.Test(LookIn) Then
        
        ' Run RegExp to get the matches, which are returned as a zero-based collection
        
        Set TheMatches = RegX.Execute(LookIn)
        
        ' Test to see if Pos is negative, which indicates the user wants the Nth to last
        ' match.  If it is, then based on the number of matches convert Pos to a positive
        ' number, or zero for the last match
        
        If Not IsMissing(Pos) Then
            If Pos < 0 Then
                If Pos = -1 Then
                    Pos = 0
                Else
                    
                    ' If Abs(Pos) > number of matches, then the Nth to last match does not
                    ' exist.  Return a zero-length string
                    
                    If Abs(Pos) <= TheMatches.Count Then
                        Pos = TheMatches.Count + Pos + 1
                    Else
                        RegExpFind = ""
                        GoTo Cleanup
                    End If
                End If
            End If
        End If
        
        ' If Pos is missing, user wants array of all matches.  Build it and assign it as the
        ' function's return value
        
        If IsMissing(Pos) Then
            ReDim Answer(0 To TheMatches.Count - 1)
            For Counter = 0 To UBound(Answer)
                Select Case ReturnType
                    Case 0: Answer(Counter) = TheMatches(Counter)
                    Case 1: Answer(Counter) = TheMatches(Counter).FirstIndex + 1
                    Case 2: Answer(Counter) = TheMatches(Counter).Length
                End Select
            Next
            RegExpFind = Answer
        
        ' User wanted the Nth match (or last match, if Pos = 0).  Get the Nth value, if possible
        
        Else
            Select Case Pos
                Case 0                          ' Last match
                    Select Case ReturnType
                        Case 0: RegExpFind = TheMatches(TheMatches.Count - 1)
                        Case 1: RegExpFind = TheMatches(TheMatches.Count - 1).FirstIndex + 1
                        Case 2: RegExpFind = TheMatches(TheMatches.Count - 1).Length
                    End Select
                Case 1 To TheMatches.Count      ' Nth match
                    Select Case ReturnType
                        Case 0: RegExpFind = TheMatches(Pos - 1)
                        Case 1: RegExpFind = TheMatches(Pos - 1).FirstIndex + 1
                        Case 2: RegExpFind = TheMatches(Pos - 1).Length
                    End Select
                Case Else                       ' Invalid item number
                    RegExpFind = ""
            End Select
        End If
    
    ' If there are no matches, return empty string
    
    Else
        RegExpFind = ""
    End If
    
Cleanup:
    ' Release object variables
    
    Set TheMatches = Nothing
    
End Function

Open in new window

Q-26463615.mdb
0
 

Author Comment

by:thutchinson
Comment Utility
Capricorn1,
That's a beautiful thing!  Seems to work great.  I will test today before I close this ticket just in case I need followup.  Thanks again.
 
0
 

Author Comment

by:thutchinson
Comment Utility
MathewsPatrick,
Your solution probably works great however it may be a little over my head.  Forgive me for trying the simpler suggestions first.  Thanks for your help. For now, Capricorn's code seems to be working.
0
 
LVL 92

Expert Comment

by:Patrick Matthews
Comment Utility
thutchinson,

No worries.  Just a couple of words of caution:

1) The RegExp function is extremely flexible :)  While the simpler function hit the spot here, once things get complicated, it can really be a godsend

2) If you ever have an entry like A1B2, my function will return "1" while cap1's returns "12".  I have no idea if that is a problem or not.

Cheers,

Patrick
0
 
LVL 44

Expert Comment

by:GRayL
Comment Utility
An extension of your original logig

v = "AB120"
? Mid(v,instr(v,strreverse(val(strreverse(v)))))
120
0
 
LVL 44

Expert Comment

by:GRayL
Comment Utility
logig should be logic ;-)
0
 
LVL 30

Expert Comment

by:hnasr
Comment Utility
Try the expression as:

Int(Val(StrReverse(CStr(Val(StrReverse(strFld & "9")))))/10)
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:thutchinson
Comment Utility
For clarification, if I use the hnasr code on the latest post then I don't need the Function proposed by Capricorn?
0
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
hnasr code, does not have anything to do with the function i posted.
0
 
LVL 44

Expert Comment

by:GRayL
Comment Utility
Did you try my formula at http:#a33648398?  I assumed from your question explaining how the zero(s) got dropped that you wanted the result as a string as all your examples were string.
0
 

Author Comment

by:thutchinson
Comment Utility
Capricorn's solution worked.  
Since I'm not a programmer, I'm not prepared to evaluate the different alternate solutions proposed by other experts.
I am, however, very interested in understanding the differences between the solutions offered.  I anyone would like to explain, I would be grateful. Otherwise, I will close this ticket (with my sincerest thanks to all) within 24 hours.
 
0
 
LVL 92

Expert Comment

by:Patrick Matthews
Comment Utility
thutchinson,I'll leave it to the others to describe their solutions, as they know their work best.  So, I'll focus on my own.My solution used Regular Expressions, a very powerful class for a variety of text parsing and replacement chores.  I like to refer to my RegExpFind and its sister functions from my article http://www.experts-exchange.com/Programming/Languages/Visual_Basic/A_1336-Using-Regular-Expressions-in-Visual-Basic-for-Applications-and-Visual-Basic-6.html as "Swiss army knife" functions because they are insanely adaptable to new situations.Basically, that RegExpFind function can find a huge variety of substrings within larger strings, so long as you are able to specify a set of rules governing what constitutes a match.  Those rules are passed to RegExp as a pattern string.In the example I provided above, RegExpFind(OriginalColumn, "\d+", 1)...a) The text being searched is OriginalColumnb) The pattern is "\d+"; \d stands for any digit 0-9, and the + indicates "1 or more of the preceding item".  Thus, \d+ matches 1 or more consecutive digit charactersc) The position is 1That means:1) We look in the value for OriginalColumn2) In that column, we are look for matches (1 or more consecutive digit characters)3) Because we passed a 1 for the position argument, the function returns the first such match it finds.This will thus grab the string "17" if the original string is:17A17AB17ABC17ABCD foo 17ABCD17hhhAB 17AB-17AB17-fooet ceteraPatrick
0
 

Author Comment

by:thutchinson
Comment Utility
Very cool, MathewsPatrick.  I really appreciate the explanation.  I'm going to spend some time with this.  Sounds like a very useful tool that I can use often.  Many thanks.
0
 
LVL 30

Expert Comment

by:hnasr
Comment Utility
I prefer working with direct query expressions for easy maintenance, unless a repetitive use of such an expression, where I make use of user defined functions.

This was introduced to address a problem in a solution to a related problem.
This solution works for 10  and A10, A 10, A-10, and 10A10 but not for A10A.

Explanation:

Int(Val(StrReverse(CStr(Val(StrReverse(strFld & "9")))))/10)

Example:
AB10

append 9 to it ==> AB109  ' to take care of the 0, it can be any digit 1 to 9
Reverse it  ====> 901BA
Numeric value ==> 901
Change to string=>901
Reverse it =====> 109
Numeric value===> 109
Integer part dividing by 10 ===> 10

strFld = "AB10"
Int(Val(StrReverse(CStr(Val(StrReverse(strFld & "9")))))/10) ===> 10
0
 
LVL 44

Expert Comment

by:GRayL
Comment Utility
AB120 - the string
021BA - reverse it
21 -  apply Val() function
12 -  reverse it
Take the Mid substring at the position where '12' is in the string 'AB120' using InStr()
Mid('AB120',InStr('AB120','12'))
------------------------^-evaluates to 3
Mid('AB120',3) = '120'
without the second qualifier in Mid(), the function returns the substring from position 3 to the end of the string.
0
 

Author Comment

by:thutchinson
Comment Utility
Messrs hnasr and GrayL,
Thanks for the explanations.  Very helpful.  
my regards,
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now