Split text - Run-time error '9'

[Leo_Polla_Psemata]

I have a field (say which stores emails addresses.
Emails are separated with semicolon ";"
I use this field in a docmd.send object.

Now
I need to make a query and split this field into separate emails.

I found the below code in here
https://www.access-programmers.co.uk/forums/showthread.php?t=291823&highlight=InStr
[quote]
[code]
Function Splitstr(s as string, element as integer) as string
Splitstr=split(s,",")(element)
end function[/code]

then in your query

Dept:splitstr(myfield,0)
Unit:splitstr(myfield,1)
[/quote]

It works just fine, however, I get this error if the field [email] contain only one email and therefore the second field in the query gives #Error
If I fix field [email] to contain for example three emails (three and the query has three columns then it works just perfect.

How can I fix the error ?

Run-time error '9'
Subscript out of range

 

[sneuberg]

I suggest you use the UBound function to determine how many elements are in splitstr and then you could use if-then-else or Select case to determine what to do.

 

[MarkK]

Consider these amendments to the function, one which tests the length of the array, and the other which handles unexpected errors...

Function Splitstr(s as string, element as integer) as string
on error goto handler
   dim var
   var = split(s,",")
   if element <= ubound(var) then Splitstr=var(element)
   exit function
handler:
   Splitstr=err.description
end function