creating a vba array function in Access
Posted on 2014-09-10
I have a field that I need to parse, there are 6 components to the field. Each element is separated with a semi-colon. (I can, using a query, put quotes and a comma to separate the elements. (I actually did this, putting quote marks also at the beginning of the field and the end of the field, when the array called in the string, it added additional quotes at the beginning and the end))
here is the field data: "09/06/2014","21:00","24:00","67","Regular","3"
I want to create a function that will return one of the elements of the array. here is a copy of the function (and versions thereof) I have tried and I receive the error
Run-time error '13':
Function ParseOtherDept(ByVal s As String, ByVal i As Integer) As String
Dim strArry As Variant
strArry = s
ParseOtherDept = strArry(i)
I've also tried the following function which produced the following error
Compile error: Can't assign to array
Function ParseField1(ByVal s As String, ByVal i As Integer) As String
Dim WrdArray() As String
Dim x As String
WrdArray() = s
x = ","
ParseField1 = Split(WrdArray, x, i)
I can't seem to find the missing link - can someone please assist...I have a workaround but this solution would be so much cleaner and clear as to which element I'm extracting.
thanks in advance for any assist.