Results 1 to 9 of 9
  1. #1
    Star Lounger
    Join Date
    Apr 2002
    Location
    Onekama, Michigan, USA
    Posts
    77
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Query to copy data from a field (A97/SR2)

    I have a table that includes a text field. Within this text field of data there is a six digit number enclosed in parentheses. I would like to copy this six digit number to another field in the table to allow sorting and manipulation of the table records. Have tried many things with an update query but have not been able to get the desired results. Any help would be appreciated.

  2. #2
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Maidstone, Kent, England
    Posts
    398
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query to copy data from a field (A97/SR2)

    the expresion
    Mid([test],InStr([test],"(")+1,6)
    Where [test] is you text field
    should strip out your numbers, you can use this for sorting in a standard query with out having to add to a table.

    HTH

    Peter

  3. #3
    3 Star Lounger
    Join Date
    Feb 2001
    Location
    NYC,USA,Earth
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query to copy data from a field (A97/SR2)

    I seem to remember there is a builtin function that returns the numeric portion of a string but I can't seem to find it. Anyone?

  4. #4
    Star Lounger
    Join Date
    Apr 2002
    Location
    Onekama, Michigan, USA
    Posts
    77
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query to copy data from a field (A97/SR2)

    I entered the expression in the criteria for the query. Of the 2684 records in the table, the query only returned one record. That record was the only one that had 6 characters total in the field.

  5. #5
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Query to copy data from a field (A97/SR2)

    Is this the first part of the field value or is it buried in the text somewhere? If it's at the beginning, it should be fairly easy to extract by looking for the parens. Your problem will be handling the parens themselves. Parens can actually have a numeric meaning, so if you convert "(12345)" using the Clng() function, you'll discover that it returns a negative 12345, which may or may not be what you want. If it isn't, here's a function I built a couple of years ago to return the numeric portion of a string. If you pass it the starting position (which should be the position of the left paren + 1, it will extract and return the numeric portion up to the next non-numeric character. You could probably rewrite it as a recursive function, but that's harder for novice programmers to understand and since the Lounge is here to help as many people as possible, I'm posting a straight, non-recursive version. Feel free to improve upon it. <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    <pre>Public Function ExtractNumFromStr(ByVal strIn As String, _
    Optional intStartPos As Integer) As Variant
    'Created 2/16/2000 by Charlotte Foust
    On Error GoTo ExtractNum_err
    Dim varNum As Variant
    Dim strNum As String
    Dim strTemp As String
    Dim intLoop As Integer
    ' Get the number portion of the string

    If intStartPos = 0 Then
    intStartPos = 1
    End If ' intStartPos = 0

    strTemp = Right(strIn, 1 + Len(strIn) - intStartPos)
    If IsNumeric(strTemp) Then
    strNum = strTemp
    Else 'IsNumeric(strTemp)
    For intLoop = intStartPos To Len(strIn)
    If IsNumeric(Mid(strIn, intLoop, 1)) Then
    strNum = strNum & Mid(strIn, intLoop, 1)
    ElseIf Len(strNum) > 0 Then
    'If you've already extracted a number, exit
    Exit For
    Else 'IsNumeric(Mid(strIn, intLoop, 1))
    'If you haven't extracted a number
    'keep testing
    End If 'IsNumeric(Mid(strIn, intLoop, 1))
    Next intLoop ' = intStartPos To Len(strIn)

    If Len(strNum) = 0 Then
    strNum = "0"
    End If 'Len(strNum) = 0
    End If 'IsNumeric(strTemp)

    varNum = CLng(strNum)
    ExtractNum_exit:
    On Error Resume Next
    ExtractNumFromStr = varNum
    Exit Function
    ExtractNum_err:
    Resume Next
    End Function 'ExtractNumFromStr(ByVal strIn As String, _
    Optional intStartPos As Integer) As Variant</pre>

    Charlotte

  6. #6
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Query to copy data from a field (A97/SR2)

    Adding to what Charlotte first said try the Clng option then multiply it by -1 to give the answer you want.
    Pat <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  7. #7
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Query to copy data from a field (A97/SR2)

    Or, if all the numbers are supposed to be positive, use the Abs function to always return a positive number.
    Charlotte

  8. #8
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Maidstone, Kent, England
    Posts
    398
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query to copy data from a field (A97/SR2)

    The formula was not meant to go in the criteria but as an expresion in a query to pull out the numbers so that you could then use the new field to sort/filter on

    HTH

    Peter

  9. #9
    Star Lounger
    Join Date
    Apr 2002
    Location
    Onekama, Michigan, USA
    Posts
    77
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query to copy data from a field (A97/SR2)

    Got the desired results from the table. You guys are great!!! Thanks a lot <img src=/S/thumbup.gif border=0 alt=thumbup width=15 height=15>. The more I learn, the more I realize how little I know.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •