Results 1 to 5 of 5
  1. #1
    amai is offline Novice
    Windows 8 Access 2010 32bit
    Join Date
    Nov 2015
    Posts
    12

    how to extract strings in a query

    I am new to Access and would like to perform a query to extract a string. I have read various posts but I cannot find a function that can help me.



    Below is an example of the data.
    N24685-0286-S0132
    N43807-00SD-7020-0001
    N48370-PQ00-1PVK1-00002

    I would like to extract the first 3 groups between the dashes. As in the example, the number of digits between the dashes may vary.

    This is what I want:

    N24685-0286-S0132
    N43807-00SD-7020
    N48370-PQ00-1PVK1

    Thank you in advance.

  2. #2
    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

  3. #3
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    Hmmm, that could get pretty complex. It might be easiest to use a custom function. You could call the function form a query. Paste the following into a new Standard Module.
    Code:
    Public Function FirstThreeGroups(ByRef ValueOfField As String) As String
    
    Dim strRevised As String
    Dim varElements As Variant
    Dim i As Integer
    
    strRevised = ""
    varElements = Split(ValueOfField, "-")
    
    For i = 0 To 2
    
        strRevised = strRevised & varElements(i)
        
            If i < 2 Then
                strRevised = strRevised & "-"
            End If
            
    FirstThreeGroups = strRevised
        
    Next I
    
    End Function
    Then you could call the function from within a query by creating an alias. You can create an alias by naming your alias and typing the name in a new field within the grid of the Query Designer. Something like MyAlias and follow with a colon MyAlias:

    Here is an example of an alias named FirstThree that calls the function above. You would need to change the table name and field name to match your table name and field name.
    Code:
    FirstThree: FirstThreeGroups([TableName].[FieldName])

  4. #4
    CJ_London is offline VIP
    Windows 8 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    if you don't want to use a UDF try

    iif(instr(replace(fullcode,"-","|",,2),"-")<>0,replace(left(replace(fullcode,"-","|",,2),instr(replace(fullcode,"-","|",,2),"-")-1),"|","-"),fullcode)

  5. #5
    amai is offline Novice
    Windows 8 Access 2010 32bit
    Join Date
    Nov 2015
    Posts
    12
    Thank you all for your help. I couldn't work it out so I looked for another way to enter the data in the database.

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

Similar Threads

  1. Replies: 3
    Last Post: 08-11-2015, 05:35 PM
  2. Total Query: Sum of Strings
    By budder8818 in forum Queries
    Replies: 5
    Last Post: 05-10-2014, 07:22 PM
  3. Replies: 1
    Last Post: 09-16-2013, 04:35 PM
  4. Extract Strings from a Dot Notation Field
    By wwalkerbout in forum Programming
    Replies: 17
    Last Post: 02-27-2012, 02:15 AM
  5. Replies: 1
    Last Post: 07-14-2008, 12:15 PM

Tags for this Thread

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