issues with Split function (1 Viewer)

[Zedster]

I am trying to write a procedure that takes an string array of field names and a tab delimited string and then splits the appropriate value for each field from the string so it can be appended with a querydef.

The code suffers a runtime error on line 30 type mismatch but I cannot work out why.

Public Function AppendToTblKTM(arrFields() As String, varRecord As Variant) As Boolean

      'This is a specific append query which appends to tblKTM
      'NB most fields are type text but fields 7 & 8 are both date they are in the format mm/dd/yyyy which is type 101 for convert function
      'varRecord is a tab delimited string representing one record

10    On Error GoTo err_AppendToTblKTM

          Dim qdf As DAO.QueryDef
          Dim db As Database
          Dim strSQL As String
20        Dim strTable As String: strTable = "tblKTM"
          Dim varValues() As Variant
          Dim strRecord As String: strRecord = CStr(varRecord)
          Debug.Print strRecord
          
30        varValues = Split(strRecord, vbTab, -1, vbTextCompare)

The argument arrFields is passed to the procedure as follows:

    Dim var_record As Variant
    Dim arrFields(10) As String
    arrFields(0) = "KeyTask"
    arrFields(1) = "Category"
    arrFields(2) = "Task"
    arrFields(3) = "Requirements"
    arrFields(4) = "Deliverables"
    arrFields(5) = "Progress"
    arrFields(6) = "Owner"
    arrFields(7) = "Start"
    arrFields(8) = "Deadline"
    arrFields(9) = "Status"
    arrFields(10) = "Comments"

Has anyone any idea why I am getting type mismatch error?

 

[MajP]

split function returns an array of strings.

 

[Zedster]

split function returns an array of strings.

So varValues() as Variant needs to be a strValues() as string.

Thanks.

 

[MajP]

Yes. I just double tested this. But the crazy thing is the reference from MS is incorrect.

Split function (Visual Basic for Applications)

Office VBA reference topic

docs.microsoft.com

 

[MajP]

Here. Test it yourself.
Public Sub test()
Dim ar() As String
'Dim ar() as variant
ar = Split("some'two'thre'", "'")
End Sub

 

[arnelgp]

Public Function AppendToTblKTM(arrFields As Variant, varRecord As Variant) As Boolean

      'This is a specific append query which appends to tblKTM
      'NB most fields are type text but fields 7 & 8 are both date they are in the format mm/dd/yyyy which is type 101 for convert function
      'varRecord is a tab delimited string representing one record

10    On Error GoTo err_AppendToTblKTM

          Dim qdf As DAO.QueryDef
          Dim db As Database
          Dim strSQL As String
20        Dim strTable As String: strTable = "tblKTM"
          Dim varValues As Variant
          Dim strRecord As String: strRecord = CStr(varRecord)
          Debug.Print strRecord
          
30        varValues = Split(strRecord, vbTab, -1, vbTextCompare)

 

[DarrenDWild]

Zedster, Hi I am sure this has nothing to do with it, I am brand new with only 1 intro post and no experience or knowledge and nowhere near as advanced as you. The only thing I wondered is if the declaration Dim arrFields(10) As String should be ...arrFields(11) As String because there are 11 fields. 0-10 so wondering if that could throw it off? Sorry if this is a silly observation but its always the smallest oversights that cause the biggest headaches.