Results 1 to 5 of 5
  1. #1
    tozey is offline Novice
    Windows XP Access 97
    Join Date
    Aug 2010
    Posts
    14

    extract something from a field

    hello, we have a table of records where a number starting with 67 shows up in the "account" field in each record. We need to pick up these numbers in a query without picking up anything that is in these fields.

    example in "account" field: hello how are you 6775654 today

    we only want to return the 67 number, the number could be anywhere in the field.

    Thanks for looking

    Alex

  2. #2
    alansidman's Avatar
    alansidman is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Apr 2010
    Location
    Steamboat Springs
    Posts
    2,529
    We need to pick up these numbers in a query without picking up anything that is in these fields.

    Not sure what you mean by this. Do you just want to put 67 in a new field? Identify only the records that have 67 in it? Perhaps an example of what you want the end result to look like. Do all the records contain 67? Your request is a little confusing.

  3. #3
    tozey is offline Novice
    Windows XP Access 97
    Join Date
    Aug 2010
    Posts
    14
    Quote Originally Posted by alansidman View Post
    Not sure what you mean by this. Do you just want to put 67 in a new field? Identify only the records that have 67 in it? Perhaps an example of what you want the end result to look like. Do all the records contain 67? Your request is a little confusing.[/COLOR]
    thanks for the reply.

    We have a table that has been used for data entry, the field "accounts" in the table is a text field which people have used to input information. lets say I have 100 records. In each record the field "accounts" has had some data added to it, the data consists of words and a number starting with 67 (which is 7 digits long).

    So to answer your question, I want to be able to run a query on the table to see all of the information on the table but I also want to return the 67##### number in its own column.

    Example containing 3 columns in the table and the new 4th column that is the result from the query

    Name | this is my note 6745124 | 24/02/2012 | 6745124
    Name | this is 6741234 my note | 24/02/2012 | 6741234

    The last column being the result of pulling out the 67####### out of column 2 (column 2 being the "accounts" field as mentioned above).

    Hope that makes more sense.

    Alex

  4. #4
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Here's a function that should extract a string of 7 digits beginning with 67 from a field.
    Code:
    Function get67(sfld As String) As String
    
          'chk field not null
          'chk field contains 67
          'chk 7 chars for numeric
          'chk for trimmed length = 7
    
    10       On Error GoTo get67_Error
    
    20    get67 = vbNullString
    30    If Not IsNull(sfld) Then
    40       If InStr(sfld, "67") > 0 Then
    50         If IsNumeric(Mid(sfld, InStr(sfld, "67"), 7)) Then
    60           If Len(Trim(Mid(sfld, InStr(sfld, "67"), 7))) = 7 Then
    70            get67 = Mid(sfld, InStr(sfld, "67"), 7)
                    'Debug.Print "acct number is " & Mid(sfld, InStr(sfld, "67"), 7)
    80            End If
    90         End If
    100      End If
    110   End If
    
    120      On Error GoTo 0
    130      Exit Function
    
    get67_Error:
    
    140       MsgBox "Error " & Err.number & " (" & Err.Description & ") in procedure get67 "
    End Function
    If the string is not found, the default value returned by get67() is vbNullString

    Try it using get67(accounts)

  5. #5
    tozey is offline Novice
    Windows XP Access 97
    Join Date
    Aug 2010
    Posts
    14
    Quote Originally Posted by orange View Post
    Here's a function that should extract a string of 7 digits beginning with 67 from a field.
    Code:
    Function get67(sfld As String) As String
    
          'chk field not null
          'chk field contains 67
          'chk 7 chars for numeric
          'chk for trimmed length = 7
    
    10       On Error GoTo get67_Error
    
    20    get67 = vbNullString
    30    If Not IsNull(sfld) Then
    40       If InStr(sfld, "67") > 0 Then
    50         If IsNumeric(Mid(sfld, InStr(sfld, "67"), 7)) Then
    60           If Len(Trim(Mid(sfld, InStr(sfld, "67"), 7))) = 7 Then
    70            get67 = Mid(sfld, InStr(sfld, "67"), 7)
                    'Debug.Print "acct number is " & Mid(sfld, InStr(sfld, "67"), 7)
    80            End If
    90         End If
    100      End If
    110   End If
    
    120      On Error GoTo 0
    130      Exit Function
    
    get67_Error:
    
    140       MsgBox "Error " & Err.number & " (" & Err.Description & ") in procedure get67 "
    End Function
    If the string is not found, the default value returned by get67() is vbNullString

    Try it using get67(accounts)
    Thanks for this I will give it a go, I was hoping it would be a simple string instruction in a query field. however I will give this a go.

    Thank you again.

    Alex

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Extract Strings from a Dot Notation Field
    By wwalkerbout in forum Programming
    Replies: 17
    Last Post: 02-27-2012, 02:15 AM
  2. Extract certain values from field
    By tylerg11 in forum Programming
    Replies: 19
    Last Post: 09-23-2011, 03:27 PM
  3. Extract text from middle of a field
    By AccessNubie in forum Access
    Replies: 3
    Last Post: 11-02-2009, 01:13 PM
  4. Extract numbers from text string strored in a field.
    By khabdullah in forum Programming
    Replies: 2
    Last Post: 12-23-2007, 06:55 PM
  5. How to extract names from 1 large field
    By Tepin in forum Access
    Replies: 1
    Last Post: 12-04-2006, 01:14 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums