Results 1 to 3 of 3
  1. #1
    Join Date
    May 2011

    Unanswered: Parsing a Memo Field

    As a newbie to Access, I've made the mistake of creating a memo field which I would like to now parse (if that is the correct term).

    The memo field currently houses exec name, exec job title

    The field reads for example:-

    Fred Smith, Chief Executive Officer
    Tom Huxley, Chief Financial Officer
    Henry Cooper, SVP Americas
    Jane Mangler, SVP Human Resources and Communications

    What I would like to do is parse the field just to extract the job titles

    would return

    Chief Executive Officer
    Chief Financial Officer
    SVP Americas
    SVP Human Resources and Communications

    What I want to do is extract all job titles to the right of the first comma.

    Is this at all possible. Would very much welcome some insight into whether this is possible and ideally how to do it.

    Many thanks for any input, the more monosyllabic the answer the better as I don't have much clue to be honest.

  2. #2
    Join Date
    Mar 2009
    Provided Answers: 15
    With Table1 = name of the table and Field1 = name of the field to parse:

    1. Using SQL (in a query):
    SELECT Trim(Mid([Field1],InStr([Field1],",")+1)) AS Job_Title
    FROM Table1;
    2. Using VBA (DAO):
    Sub Job_Title()
        Dim rst As dao.Recordset
        Set rst = CurrentDb.OpenRecordset("Table1", dbOpenSnapshot)
        With rst
            Do Until .EOF
                Debug.Print "Job title: " & Trim(Mid(!Field1, InStr(!Field1, ",") + 1))
        End With
        Set rst = Nothing
    End Sub
    Have a nice day!

  3. #3
    Join Date
    May 2011
    Thanks, that looks like it makes a lot of sense. Not in front of my database but can't wait to try it out later.

    Many thanks again.

Posting Permissions

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