I'm leaning towards the code method as well unless there's a calculation in a query field, but one isn't jumping out at me as it would probably require mixing of Left and IN functions. I say that because I interpreted the original post to mean a lookup table doesn't exist - the lookup values come from the same table. Maybe I'm wrong on that.
At any rate, I don't see that code working because the start position isn't changing for the Instr function, so it will always start at 1, plus the string to search for isn't actually being provided - just the string to search (arg) but the parameter for the string to find is being passed an integer (x) which is really the counter and not a string value. Also, the line that sets the value of rslt isn't being concatenated, so it will just be over-written on every loop. It is a very good start, though, and I apologize if you tested it and it worked. I could be misinterpreting something.
I also think that what was asked for is a comma separated concatenation ("RECERT, INCERT"). So what I think would work is
Code:
Public Function ExtractCode(arg As String) As String
Dim x As Long
Dim rslt As String
For x = 1 To Len(arg)
rslt = rslt & DLookup("Abbrev", "Categories", "Categ='" & Mid(arg, x, 1) & "'") & ","
Next x
rslt = Left(rslt, Len(rslt) - 1) 'Trim the trailing ","
'Debug.Print rslt
End Function
if it is called like
extractcode("IA")
I haven't allowed for the DLookup ever returning a null, such as if a character in the supplied string isn't found.