Access 2007 vba

[catsoncars]

In an access database, data is imported daily from an external system. One of the fields in question is a text field and contains information that I would like to parse out to specific fields. I have picklists for each of these specific fields. Using VBA how would you match the list to a substring of the text? For example, BR is the list and has over 130 different combinations of letters and numbers. Let's say 78GH is one of the values. I want to match 78GH to the contents of the text field. If 78GH existed then I want to populate a separate field with 78GH. I have this working with recordset but it only works with short lists because of the string limitation in SQL.

Set rst = db.OpenRecordset("Select [CodeDrop] from CodeDrops WHERE [DataMineCD] = -1")
   If rst.EOF = False And rst.BOF = False Then
     rst.MoveFirst
      Do While rst.EOF = False
          strCriteria = strCriteria & " IIf(InStr(1,[Short Description], '" & rst!CodeDrop & "'" & ")>0,'" & rst!CodeDrop & "',"
          strCriteria2 = strCriteria2 & " IIf(InStr(1,[Short Description], '" & rst!CodeDrop & "'" & ")>0,'" & rst!CodeDrop & "',"
          parens = parens & ")"
        rst.MoveNext
      Loop
    End If
    strCriteria = Left(strCriteria, Len(strCriteria) - 1) & parens & " AS LoadVal"
    strCriteria2 = Left(strCriteria2, Len(strCriteria2) - 1) & parens
'
    Set qdf = Nothing
    Set qdf = db.QueryDefs("qryExtractBRCDTL")
    strSQL = "INSERT INTO MinedValuesCD ( [Ticket Number], [Short Description], LoadVal ) " & _
                   "SELECT TicketDetails.[Ticket Number], Remedy.[Short Description], " & _
                   strCriteria & _
                   " FROM TicketDetails INNER JOIN Remedy ON TicketDetails.[Ticket #] = Remedy.[Trouble Ticket #] " & _
                   " WHERE " & _
                   strCriteria2 & _
                   " AND TicketDetails.[Code Drop] Is Null AND OldTicket = 0; "
    qdf.SQL = strSQL
    DoCmd.OpenQuery "qryExtractBRCDTL"

But my list of 130 blows the code.

 

[Galaxiom]

It is hard to understand from the description and small example.

Can you upload a small sample of the source records and how they are to appear in the destination table?

It does sound like your output might be denormalized into a flat file which could lead to the problems you describe. Can you get away with having different output table structures?

 

[catsoncars]

sample record coming in....this is one text field called TxIncoming
"AXD - CI4 - VF99 - noticing several issues with the JHG spreadsheets"

we have 3 lookup tables containing a list of different codes.
AXD, AXE, AXF, ACG, etc. (keyno)
CI4, CA5, CA3, etc. (partno)
VF89, VB99, VF84, etc. (sourceno) (over 130 of these)

The program would take the first table (keyno) and match all entries to the AXD in TxIncoming. If a match is located the program would place the AXD into another datafield.

This would repeat using each of the 3 lookup tables on the same sample record.

 

[DCrake]

I think you need to use Split() to place the TxIncoming into an array using the hypen as the delimiter then enumerate through the array extracting the data as per your requirement.

 

[catsoncars]

I am not guaranteed there will be a dash, I just have to look for the value as a match. I can't use a delimiter.

 

[DCrake]

Are you aware of what possible variations there are apart from the hyphen?

Lets say there could be hyphens, commas, pipes, semicolons, colons

You could have a Txincoming looking something like

AXD - CI4 | VF99 ; FV99 , DE34 : WS00

You could run a series of replaces

txtString = Replace(TxIncoming,"|","-")
txtString = Replace(TxIncoming,":","-")
txtString = Replace(TxIncoming,";","-")

etc

Then use the split to create the array

 

[catsoncars]

There are usually no separaters other than a space. And the text is inconsistent. I am not even guaranteed a fixed order of the three fields. That is why I need to compare them to the lookup tables to see if it is a valid field.