Split Access field name string in table

Question

I am trying to get the string of field names in a table and then split the substring after a character. I have multiple fields with substrings that count, for example ChuckQ1 then the next field is ChuckQ2 then JawQ1 etc.

I am then trying to fill an array named strarray1 with the substring. I split it twice to not get duplicates. Please see below

Basic Example: Field name "TestQ1" and I want "1" as the split substring. If it is the first of the type, not 2 or 3..., then the second split is to grab the actual name type "Test"

I am getting type mismatch @ fldStr = Split(fldStr, "Q", 1). Please note that the first field is named "ID", tables prim key. Would that be an issue because it does not have "Q" in the string? Or is the field name not a string type?

Code

Dim f As Integer
Dim fldStr As String
Dim db As DAO.Database
Set db = CurrentDb()
Dim rs1 As DAO.Recordset
Set rs1 = db.OpenRecordset(Selection)
Dim fld As DAO.Field
f = 1
For Each fld In rs1.Fields
    fldStr = fld.Name
    fldStr = Split(fldStr, "Q", 1)
    If fldStr = "1" Then
        fldStr = fld.Name
        fldStr = Split(fldStr, "Q", 0)
        strArray1(f) = fldStr
        f = f + 1
    Else
    End If
Next
Set fld = Nothing

Erik A Erik A · Accepted Answer · 2017-10-18T06:43:49

I believe you don't fully understand what Split does. Split splits a string on a delimiter, and gives you an array. The third argument (where you entered 0 and 1) is the maximum number of elements in that array, where the last element contains the unsplit remainder of the string (0 is 0 elements so returns an empty array, 1 is 1 element so returns an array with your entire string).

If you want to return a specific element of the array, you can just use the following: Split(fldStr, "Q")(1)

Implemented in your code:

Dim f As Integer
Dim fldStr As String
Dim db As DAO.Database
Set db = CurrentDb()
Dim rs1 As DAO.Recordset
Set rs1 = db.OpenRecordset(Selection)
Dim fld As DAO.Field
f = 1
For Each fld In rs1.Fields
    fldStr = fld.Name
    fldStr = Split(fldStr, "Q")(1)
    If fldStr = "1" Then
        fldStr = fld.Name
        fldStr = Split(fldStr, "Q")(0)
        strArray1(f) = fldStr
        f = f + 1
    Else
    End If
Next
Set fld = Nothing

Note that this part:

fldStr = fld.Name
fldStr = Split(fldStr, "Q")(0)

makes no sense to me. Why change fldStr, and then instantly change it again.

Split Access field name string in table

1 Answers