I am using MS Access, VBA (MS VBScript Regex 5.5) and have an expression that does a reasonable job for all cases except where all fields are blank

The challenge was to return all fields as populated (whether no quotes, Quote delimited or embedded single quote delimiters) or null, including the case where the first and/or last field is empty

the expression I am using is

(?=^,[^"]*)|([^"|,]+)|"(([^"]|"")*)"|(?=,,)|(?=,$)

Works fine for

a,b,c,d,e,f,g,h and "a","b 'c' d",c,d,e, and a,'b' B,c,d,

and

a,,,,,,,, or ,a,,,,,,,,b

but when the first field and/or the second field are empty, (,b,c,d,e,f or a,,c,d,e,f) the field count reduces by one

Anybody see what's missing?

Thanks

P

I ran the following in Access VBA with the 5.5 VBScript regex:

Option Compare Database
Option Explicit

Public Function CountSubString(Text As String) As Long
    Dim MyRegex As RegExp
    Dim Result As MatchCollection
   
    Set MyRegex = New RegExp
    MyRegex.Pattern = "(?=^,[^""]*)|([^""|,]+)|""(([^""]|"""")*)""|(?=,,)|(?=,$)"
    MyRegex.Global = True
    Set Result = MyRegex.Execute(Text)
    CountSubString = Result.Count
End Function

Public Sub Test()
    Dim ResultCount As Long
   
    ResultCount = CountSubString("a,b,c,d,e,f,g,h")             ' Count = 8
    Debug.Print "Test #1 - Should be 8, is"; ResultCount
    ResultCount = CountSubString("""a"",""b 'c' d"",c,d,e,")    ' Count = 6
    Debug.Print "Test #2 - Should be 6, is"; ResultCount
    ResultCount = CountSubString("a,'b' B,c,d,")                ' Count = 5
    Debug.Print "Test #3 - Should be 5, is"; ResultCount
    ResultCount = CountSubString("a,,,,,,,,")                   ' Count = 9
    Debug.Print "Test #4 - Should be 9, is"; ResultCount
    ResultCount = CountSubString(",a,,,,,,,,b")                 ' Count = 10
    Debug.Print "Test #5 - Should be 10, is"; ResultCount
    ResultCount = CountSubString(",b,c,d,e,f")                  ' Count = 6
    Debug.Print "Test #6 - Should be 6, is"; ResultCount
    ResultCount = CountSubString("a,,c,d,e,f")                  ' Count = 6
    Debug.Print "Test #7 - Should be 6, is"; ResultCount
End Sub

The output was

Test #1 - Should be 8, is 8
Test #2 - Should be 6, is 6
Test #3 - Should be 5, is 5
Test #4 - Should be 9, is 9
Test #5 - Should be 10, is 10
Test #6 - Should be 6, is 6
Test #7 - Should be 6, is 6

Not sure what problem you are experiencing.

Susan