MS Access Query - Selecting Certain Entries In A Field

[deboraha.bishop]

Using a MS Access query, I would like to write a formula that will give
me only the first 3 items in a field separated by semi-colons. For
example, of the data in the field including:
horses;cats;dogs;parrots;mice;elephants,
I would like to extract:
horses;cats;dogs; only.

Any help would be appreciated.

 

[John Vinson]

Using a MS Access query, I would like to write a formula that will give
me only the first 3 items in a field separated by semi-colons. For
example, of the data in the field including:
horses;cats;dogs;parrots;mice;elephants,
I would like to extract:
horses;cats;dogs; only.

Any help would be appreciated.

Well, you're having problems because you're violating normal design
principles: fields should be atomic, having only one value. If you had
this information split out into a related table with one row per item
it would be easy!

That said... you'll need some moderately snarky string parsing code.
You might be able to get by with some nested InStr() and Left()
function calls, but they'll have problems with records which only have
TWO items, or just one. So try:

Public Function FirstThree(strIn As String) As String
Dim strWords() As String ' array of words
Dim iLen As Integer
Dim i As Integer
strWords = Split(strIn, ";") ' split out each word
iLen = uBound(strWords)
If iLen > 2 Then iLen = 2
For i = 0 to iLen
FirstThree = strWords(i) & ";"
Next i
End Sub

John W. Vinson[MVP]