How do I output part of a string that has a non fixed length?

[Guest]

I have a table in Access. One of the fields in the table contains look-up
codes that relate to a look-up table (in the same database). The field that
contains the look-up codes may contain one or many look-codes. If there are
multiple look-up codes, they are split by a comma. I want to output in
different fields the various look-up codes. The look-up codes have various
lengths ranging from 3 to 10 bytes. Is there a query that I can create that
will do this for me?

 

[Nikos Yannacopoulos]

Richard,

It looks like you are starting out in the wrong direction... if you need
multiple lookup codes in one field (i.e. in one record), then you must
be dealing with a one-to-many relationship, in which case what you ought
to do is use a separate table to capture it.
You may come up with a trick or two to do what you have in mind at this
point, but as your development progresses you will encounter more and
more problems, requiring ever more tricks to deal with them, all
resulting from your poor design choice. My best advice is fix your
design the soonest possible, as the earlier you do it the less pain
you're in for.
Post back with a more detailed description of what you are trying to do,
if you need more guidance.

HTH,
Nikos

 

[Brendan Reynolds]

I can't see a way to do that with just a query, but you could do something
with a query and some VBA ...

Public Function GetCode(ByVal Source As String, ByVal Number As Long) As
String

Dim astrWork() As String

If InStr(1, Source, ",") = 0 Then
'One code only.
If Number = 1 Then
GetCode = Source
Else
GetCode = vbNullString
End If
Else
astrWork = Split(Source, ",")
If UBound(astrWork) >= Number - 1 Then
GetCode = astrWork(Number - 1)
Else
GetCode = vbNullString
End If
End If

End Function

SELECT tblTest.TestMemo, GetCode([TestMemo],1) AS CodeOne,
GetCode([TestMemo],2) AS CodeTwo, GetCode([TestMemo],3) AS CodeThree,
GetCode([TestMemo],4) AS CodeFour, GetCode([TestMemo],5) AS CodeFive
FROM tblTest;