Splitting a complex string

[peedee]

Hey Everyone,

I am relatively new to access so please bear with me.

I have a field in a table named "Name". The data in the field is a string that I would like to take part of the information out of.



Example:
LOUISIANA STATE U-BATON ROUGE Combustion Engineering 2014 Fall Service Prospect
BATON ROUGE COMM COLLEGE Anatomy & Physiology 1-Semester 2014 Fall Service Qualified

There is another column in the table that already has the Account Name: (LOUISIANA STATE U-BATON ROUGE), Year, Season, ect. The one part of the column that I need is Course Name (i.e. Combustion Engineering/Anatomy & Physiology 1-Semester).

I assume that I can use a query to split this field into parts, however I am not experience enough to know how to do this. Any and all suggestions will be extremely appreciated. I am not tied to doing this in a query, but please remember that I am a newbie and will need you to dumb it down for me.

[ItsMe]

If you have a list of course names, exactly how the literal text appears in your field, you can use the instr() function to determine if a given "Search" is in within the text.

Where is this data coming from? It may be better to parse the data at the source. There may be something you can go after like XML elements.

Also, Name is a reserved word in Access. Avoid reserved words for names of objects like, Forms, tables, queries, and names of fields, variables, etc. Employ a naming convention for objects and variables. Recommend using camelback and combine two condensed versions of words for field names eg CustName

[peedee]

I do have a table that has the course names exactly how I want it to appear. I have a table called course that has a column called course.
Thanks for the suggestion on naming...I have renamed it to be CourseName.

How would the instr() function look?

The data is coming from a company wide excel that is updated routinely. I currently do this in excel, but it is time consuming and looking (hopefully) to have access automate it

[ItsMe]

I would approach it with DAO and loop through Recordsets of the table that has the field you provided an example of. As you .MoveNext to each recordset you would apply the instr() function for that field name.

By itself and outside of your DAO code, an instr() example might look like.

Dim intAnswer As Integer
Dim strFind As String
Dim strLiteral As String

'initialize variables
intAnswer = 0
strFind = ""
strLiteral = ""

'retrieve data and set variables
strFind = "COLLEGE Anatomy & Physiology"
strLiteral = "LOUISIANA STATE U-BATON ROUGE Combustion Engineering 2014 Fall Service Prospect " & _
             "BATON ROUGE COMM COLLEGE Anatomy & Physiology 1-Semester 2014 Fall Service Qualified"
    
'validate data
intAnswer = InStr(strLiteral, strFind)
'Insert future code here to update a field within recordset employing intAnswer

'Code to see and test results
    If intAnswer = 0 Then
        MsgBox "Did not find a match      " & vbCrLf & "intAnswer = " & intAnswer
    Else     'intAnswer is the first position strFind resides in strLiteral
        MsgBox "That course name exists here     " & vbCrLf & "intAnswer = " & intAnswer
    End If