Parsing Comma Values

[jo15765]

I have one field that has a comma seperated list. I want to split the list in my query. I found a function below online (Don't recall where, to give credit)

Function QuerySplit(FieldName As String, Delim As String, Position As Integer)
    QuerySplit = Split(FieldName, Delim)(Position)
End Function

Using this syntax if the field only has ONE value it throws an error.

Field2: Trim(querysplit([Comeon,",",1))

I tried to also check if the 1st location was null

Field2: IIF(Trim(querysplit([Comeon],",",1)) IS NOT NULL, Trim(querysplit([Comeon],",",1)), "")

However this throws an error as well. How can I use the split function to properly parse this field?

[andy49]

I'm wondering if this would work

Field2:querysplit([Comeon],",",1)

Function QuerySplit(FieldName As String, Delim As String, Position As Integer)   
if instr(fieldname,delim)>0
QuerySplit = Split(FieldName, Delim)(Position)
else querysplit = somethingorother
end if
End Function

[jo15765]

That did it, thank you @andy49

[jo15765]

If I try this same syntax with a 2 or 3 it will throw an error again.

[andy49]

Can you clarify what you mean by a 2 or a 3 Jo?

[andy49]

A slightly different version............

Function QuerySplit(FieldName As String, Delim As String, Position As Integer)   
dim mystr(100) as string
if instr(fieldname,delim)>0
mystr= Split(FieldName, Delim)
querysplit = trim(mystr(position))


else querysplit = somethingorother
end if
End Function

[jo15765]

This syntax throws an error of can't assign to array.

What I meant by 2 or 3 was using

Field2:querysplit([Comeon],",",2)

Field2:querysplit([Comeon],",",3)

[andy49]

Ok the alternative proposed should sort that

[jo15765]

The updated syntax gives me an error of can't assign to array on this line

mystr = Split(FieldName, Delim)

[andy49]

try this

Function QuerySplit(FieldName As String, Delim As String, Position As Integer)   
dim myarray() as string
if instr(fieldname,delim)>0
myarray= Split(FieldName, Delim)
querysplit = trim(myarray(position))


else querysplit = somethingorother
end if
End Function

[jo15765]

it throws a subscsript out of range error on this line

QuerySplit = Trim(myarray(Position))

[andy49]

How long is the text Jo?

Function QuerySplit(FieldName As String, Delim As String, Position As Integer)
dim myarray() as string
if instr(fieldname,delim)>0
myarray= Split(FieldName, Delim)
if position<=UBound(myArray) then
querysplit = trim(myarray(position))
else
querysplit = ""
endif

else querysplit = somethingorother
end if
End Function

[jo15765]

Less than 255 characters. Your most recent post fixed it. Thank you so much for the assistance.

[andy49]

Another challenge thoroughly enjoyed.

[jo15765]

Another challenge thoroughly enjoyed.

I Spoke to soon, lol. Further testing reveals that if there is only 1 value in the cell then the value is not displayed (works perfect for all other instances) it will show blank. For example, this returns a false blank if there is no comma in the row

Field:querysplit([Comeon],",",0)