none
Extracting number from string RRS feed

  • Question

  • I have a field in access that has data like this:

    23

    23 Days

    2 Weeks

    Net 14 Days

    I need to get the number from all these lines

    I need

    23

    23

    2 (although best would be 14 because 2 * 7 days)

    14 

    The Val function works fine for all the above except for the net 14 days which results in 0 since it stops when it sees the first non numeric character which is "n". Is there a way to get the position of the first numeric character and then do the val function on that, or any other ideas?


    Debra has a question

    Tuesday, May 31, 2016 3:53 PM

All replies

  • The Val function works fine for all the above except for the net 14 days which results in 0 since it stops when it sees the first non numeric character which is "n". Is there a way to get the position of the first numeric character and then do the val function on that, or any other ideas?

    Hi Debra,

    You could make a function, and pass the data through a parameter.

    Within the function you use the Split-function using the space as separator.

    The loop through the array elements from 0 to UBound(array).

    The first array element that passes the test IsNumeric, gives you the numeric value. If the next array element is available, and has a value of "weeks" or "week" or "w", then you multiply the found value with 7.

    Finally return the final value as result of the function.

    Imb.

    Tuesday, May 31, 2016 4:16 PM
  • Thank you for your quick response. I'm new to access I'm really importing data from access to sql and I would like to import it with the proper numeric value since the column in sql is an integer column. When importing from access to sql you have to make a query in visual basic I think so I'm not very familiar and I'm just looking around for a simple in line statement that can be used within the query. Would this do the trick and would you be able to spell out the exact function for me? 

    Debra has a question

    Tuesday, May 31, 2016 4:21 PM
  • Add the following function to a standard module in the database:

    Function GetDays(var)

        Dim n As Integer
        Dim intDays As Integer
       
        If Not IsNull(var) Then
            For n = 1 To Len(var)
                If IsNumeric(Mid(var, n, 1)) Then
                    intDays = Val(Mid(var, n)) * IIf(InStr(var, "weeks") > 0, 7, 1)
                    Exit For
                End If
            Next n
            GetDays = intDays
        End If

    End Function

    You can then call it wherever necessary in the database, e.g. in a computed column in a query or a computed control in a form or report, or in VBA code.  You can see how it works in the debug window:

    ? GetDays("23")
     23
    ? GetDays("23 Days")
     23
    ? GetDays("2 Weeks")
     14
    ? GetDays("Net 14 Days")
     14

    Ken Sheridan, Stafford, England

    Tuesday, May 31, 2016 5:52 PM
  • You can use this sub as a starting point:

    Option Explicit
    Const theField = "23 23 Days 2 Weeks Net 14 Days"
    Sub test()
        Dim i As Integer
        Dim fieldLength As Integer
        Dim num As Integer
        Dim start As Integer
        Dim c As String
    
        fieldLength = Len(theField)
        For i = 1 To fieldLength
            c = Mid(theField, i, 1)
            If IsNumeric(c) Then      'Number found, extract it
                start = i
                Do While IsNumeric(c)
                    i = i + 1
                    c = Mid(theField, i, 1)
                Loop
                '*
                '* Get this number into an integer
                '*
                num = Mid(theField, start, i - start)
                '*
                '* Show our extracted number (debug only)
                '*
                Debug.Print num
            End If
        Next i
    End Sub
    

    Will print out:
    23
    23
    2
    14


    Best regards, George


    Tuesday, May 31, 2016 6:08 PM
  • You can also try using the Mid and Val functions together in a query expression as below:

    =IIf(Val([YOUR FIELD NAME) = 0,CDbl(Mid([YOUR FIELD NAME], 4, 4)),Val([YOUR FIELD NAME]))

    This assumes that the Length parameter is 4 (Net + the space before 14). If it is different, you will need to adjust the Length parameter.

    Tuesday, May 31, 2016 6:31 PM
  • With all respect, Lawrence, but how do you dynamically adjust how long a number is in her data fields with your method?

    Best regards, George

    Tuesday, May 31, 2016 6:53 PM
  • You can't George. She asked how it might be used in a single line in a query. But as I said, unless the length is fixed as 4 (as in Net+space), she would need to adjust the formula.

    Tuesday, May 31, 2016 8:41 PM
  • This code will allow you to directly use an SQL string to query the database and return the results in a Collection:

    Option Compare Database
    Option Explicit
    
    Const DELIMITERCHAR = "," ' Set to the delimiter between numbers that you want
    
    Sub showMe()
        Dim db As Database
        Dim rs As Recordset
        Dim myNumbers As New Collection
        Dim tempNums As Variant
        Dim i As Integer
    
        Set db = CurrentDb
        Set rs = db.OpenRecordset("SELECT myTable.StrNum, GetNum([StrNum]) AS theNumbers FROM myTable;")
        Do While Not rs.EOF
            '*
            '* Extract the numbers to an array of integers (example only)
            '* Optionally you can include a row identifier
            '*
            tempNums = Split(rs!theNumbers, DELIMITERCHAR)
    
            For i = 0 To UBound(tempNums)
                myNumbers.Add (tempNums(i))	  'Insert the numbers into the Collection
            Next i
            rs.MoveNext
        Loop
        '*
        '* Show result
        '*
        For i = 1 To myNumbers.Count    'Collections start index is 1, not 0
          Debug.Print myNumbers(i)
        Next i
    End Sub
    Function GetNum(theField As String) As String
        Dim i As Integer
        Dim fieldLength As Integer
        Dim num As Integer
        Dim start As Integer
        Dim c As String
        Dim theNumbers As String
        Dim delimiter As String
    
        delimiter = ""
        theNumbers = ""
        fieldLength = Len(theField)
        For i = 1 To fieldLength
            c = Mid(theField, i, 1)
            If IsNumeric(c) Then      'Number found, extract it
                start = i
                Do While IsNumeric(c)
                    i = i + 1
                    c = Mid(theField, i, 1)
                Loop
                '*
                '* Get this number into a string
                '*
                num = Mid(theField, start, i - start)
                theNumbers = theNumbers & delimiter & num
                delimiter = DELIMITERCHAR    'To avoid delimiter first in string
            End If
        Next i
        GetNum = theNumbers
    End Function

    Will display:
    23
    23
    2
    14


    Best regards, George




    Wednesday, June 1, 2016 10:26 AM
  • This is working fine in access itself, but I don't know how to integrate it into the sql import wizard (I don't think it can use functions). 

    Debra has a question



    Wednesday, June 1, 2016 4:36 PM
  • This is to specified for a specific example but if there is a way to make such an inline statement in the query without functions I would really appreciate it.

    Debra has a question

    Wednesday, June 1, 2016 4:37 PM
  • This is to specified for a specific example but if there is a way to make such an inline statement in the query without functions I would really appreciate it.

    Debra has a question


    Hi Debra. Pardon me for jumping in, but if you have to manipulate the data, then you'll need to use a function. Val() itself is a function, although a built-in one. So, if you can use Val() in your import process, then you can also use any of the functions provided above. Just my 2 cents...
    Wednesday, June 1, 2016 4:59 PM
  • We have never used SQL Server, so I have no experience of its import capabilities, but if it will only import from a base table, not a query, which would surprise me, then you could call the function in an 'append' query in Access to insert rows into a predefined base table, and then import from that.

    As regards returning 3 by default you simply need to initialise the intDays variable to 3 before looping through the string expression:

    Function GetDays(var)

        Dim n As Integer
        Dim intDays As Integer
        
        intDays = 3

        If Not IsNull(var) Then
            For n = 1 To Len(var)
                If IsNumeric(Mid(var, n, 1)) Then
                    intDays = Val(Mid(var, n)) * IIf(InStr(var, "weeks") > 0, 7, 1)
                    Exit For
                End If
            Next n
            GetDays = intDays
        End If

    End Function

    Ken Sheridan, Stafford, England

    Wednesday, June 1, 2016 5:00 PM
  • It doesn't only import from a base table it does work also with a query. But, the query is in access language but not actually in access so I don't know how to call the function in the query. But I can fiddle around with a table field using the Mid, val, instr ... That is why I was wondering how to do this with an in line query. If you would know of a way to use the function within the query I would greatly appreciate it since it is working beautifully in access. How would I be able to call the function in an 'append' query in Access to insert rows into a predefined base table?

    Debra has a question


    Wednesday, June 1, 2016 5:37 PM
  • By 'query' I was really referring to an Access querydef object, rather than to the use of the term generically.

    Ken Sheridan, Stafford, England

    Wednesday, June 1, 2016 6:01 PM
  • Hi Debra,

    >> But I can fiddle around with a table field using the Mid, val, instr ... That is why I was wondering how to do this with an in line query. If you would know of a way to use the function within the query I would greatly appreciate it since it is working beautifully in access.

    Where do you want to use this function, in SQL Server? Based on your description, it works in Access, could you share us more about where it did not work? If you mean you want to use this function in SQL Server, I am afraid it is impossible, you will need SQL Server function instead of Access function. Or, I would suggest you get the right data in Access first with Access function, and then import the data to SQL Server again.

    If I misunderstood you, please feel free to let us know where you want to achieve this function and more information about your situation.

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Thursday, June 2, 2016 5:28 AM