Access query syntax for pulling 5 characters from the left of special charater in text field multiple times

Question

Looking for access query syntax for selecting a 5 character string to the left of a ";" in data field but the field may have multiple cases. example of data field below;

52441;cysto with insertion 52442; cysto repair

Would like to append existing table with field showing cpt code and new line for each patient that has multiple cpt codes

Answer 1

Hi. From the example data you posted, what part do you want to get back? 52441?

Have you tried?

Left([FieldName],5)

Answer 2

You will need a small helper function:

Public Function ExtractCode(ByVal Text As String, ByVal Item As Integer) As String

    Dim Values  As Variant
    Dim Value   As String

    Values = Split(Text, ";")

    If Item > LBound(Values) And Item <= UBound(Values) Then
        Value = Right(Trim(Values(Item - 1)), 5)
    End If

    ExtractCode = Value

End Function

Output:

cptcodes = "52441;cysto with insertion 52442; cysto repair"

? ExtractCode(cptcodes, 1)
52441

? ExtractCode(cptcodes, 2)
52442

In a query, you can pull the n'th value of the codes:

Select *, ExtractCode([cptcodes], n) As cptcode From YourTable