Getting lookup table descriptions from a string.

[mrfritz44]

I need an efficient way to extract values from a string and then find the corresponding value in a lookup table. After I get this value, I need to write the code and then the description from the lookup table.

My data looks like this:

Main table:
CLM D E
101324 001009020 011003050075

Lookup table:

CODE DESCRIPTION
001 RED
009 BLUE
020 GREEN
011 YELLOW
003 PURPLE
050 BLACK
075 PINK

My result would need to look like this:
CLM "RECORD D" "RECORD E"
101324 001-RED, 009-BLUE, 020-GREEN 011-YELLOW, 003-PURPLE, 050-BLACK, 075-PINK

I would probbaly do this in a module, but I'm stumped on how.

Any help would really be appreciated.

Fred

 

[Remou]

Is this the kind of thing you were thinking of?

Sub LookUpColour()
Dim strTemp, strCode, i
Dim strColours
strTemp = "011003050075"
Do While Len(strTemp) > 0
  If Len(strTemp) < 3 Then
    Debug.Print "Error"
  End If
  strCode = Left(strTemp, 3)
  strTemp = Mid(strTemp, 4)
  strColours = strColours & strCode & "-"
  strColours = strColours & DLookup("Description", "tblColours", "Code='" & strCode & "'") & ", "
Loop
Debug.Print strColours
End Sub

If it is, it would be quite simple to convert to a function.