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

Topic

New Reply

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.

Reply | Quote

Replies

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

Got the desired results from the table. You guys are great!!! Thanks a lot . The more I learn, the more I realize how little I know.

Reply | Quote

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?

Reply | Quote

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.

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

Reply | Quote

Adding to what Charlotte first said try the Clng option then multiply it by -1 to give the answer you want.
Pat

Reply | Quote

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

Reply | Quote