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

    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.

    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:


    Thank you in advance.

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Ottawa, Ontario, Canada; West Palm Beach FL

  3. #3
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    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.
    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.
    FirstThree: FirstThreeGroups([TableName].[FieldName])

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


  5. #5
    amai is offline Novice
    Windows 8 Access 2010 32bit
    Join Date
    Nov 2015
    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 - Senior Forums