Case Select error Subscript out of range

[mattc66 via AccessMonster.com]

Having the a problem with the below function. I am parsing data below:

CC,4.5x5x7,C2
CC,4x5x7,C2
PB,5.5x5.5x8,C2,ModelName
'to
fld1 fld2 fld3 fld4 fld5 fld6
CC 4.5 5 7 C2
CC 4 5 7 C2
PB 5.5 5.5 8 C2 ModelName

The problem is it works great if all 6 flds are populated. However if the
last fld is null I get an error message "Subscript out of range".

Below is the code. What can I do to resolve this issue?
Public Function ParseData(pstrText As String, _
pintColumn As Integer) As String
On Error GoTo Err_ParseData

Dim arCSV
Dim arX
'create 3 comma separated values
arCSV = Split(pstrText, ",")
'split the "x" separated values
arX = Split(arCSV(1), "x")

Select Case pintColumn
Case 1
ParseData = arCSV(0)
Case 2
ParseData = arX(0)
Case 3
ParseData = arX(1)
Case 4
ParseData = arX(2)
Case 5
ParseData = arCSV(2)
Case 6
ParseData = arCSV(3)


End Select


Exit_ParseData:
Exit Function

Err_ParseData:
MsgBox Err.DESCRIPTION
Resume Exit_ParseData

End Function

 

[John Nurick]

Hi Matt,

It's not that the last field is null: in that case the sample data would
look like this:
CC,4.5x5x7,C2,
CC,4x5x7,C2,
PB,5.5x5.5x8,C2,ModelName
it's that last field is missing: the first two records in your data have
fewer commas than the third.

One approach would be to write code that counts the number of commas and
adds one at the end if necessary. A simple way to count the commas is to
use Replace() to replace "," with "" and comparing the length of the
resulting string with the length of the original.

Another would be to use a regular expression like this to parse the
string:

^([^,]*),(?[0-9.]+)x([0-9.]+)x([0-9.]+))?,([^,]*)(?:,(.+))?$

 

[John Spencer]

You could also check the size of the array(s) and compare that to pintColumn.

In this specific code
IF Ubound(arCSV) < 3 and pintColumn = 6 Then
ParseData = Null
Else


End if

Also

Hi Matt,

It's not that the last field is null: in that case the sample data would
look like this:
CC,4.5x5x7,C2,
CC,4x5x7,C2,
PB,5.5x5.5x8,C2,ModelName
it's that last field is missing: the first two records in your data have
fewer commas than the third.

One approach would be to write code that counts the number of commas and
adds one at the end if necessary. A simple way to count the commas is to
use Replace() to replace "," with "" and comparing the length of the
resulting string with the length of the original.

Another would be to use a regular expression like this to parse the
string:

^([^,]*),(?[0-9.]+)x([0-9.]+)x([0-9.]+))?,([^,]*)(?:,(.+))?$

Having the a problem with the below function. I am parsing data below:

CC,4.5x5x7,C2
CC,4x5x7,C2
PB,5.5x5.5x8,C2,ModelName
'to
fld1 fld2 fld3 fld4 fld5 fld6
CC 4.5 5 7 C2
CC 4 5 7 C2
PB 5.5 5.5 8 C2 ModelName

The problem is it works great if all 6 flds are populated. However if the
last fld is null I get an error message "Subscript out of range".

Below is the code. What can I do to resolve this issue?

Public Function ParseData(pstrText As String, _
pintColumn As Integer) As String
On Error GoTo Err_ParseData

Dim arCSV
Dim arX
'create 3 comma separated values
arCSV = Split(pstrText, ",")
'split the "x" separated values
arX = Split(arCSV(1), "x")

Select Case pintColumn
Case 1
ParseData = arCSV(0)
Case 2
ParseData = arX(0)
Case 3
ParseData = arX(1)
Case 4
ParseData = arX(2)
Case 5
ParseData = arCSV(2)
Case 6
ParseData = arCSV(3)


End Select


Exit_ParseData:
Exit Function

Err_ParseData:
MsgBox Err.DESCRIPTION
Resume Exit_ParseData

End Function

 

[mattc66 via AccessMonster.com]

Hi John,

I would I use this?: ^([^,]*),(?[0-9.]+)x([0-9.]+)x([0-9.]+))?,([^,]*)(?:,
(.+))?$
Do I put it in the query?

Matt

Hi Matt,

It's not that the last field is null: in that case the sample data would
look like this:
CC,4.5x5x7,C2,
CC,4x5x7,C2,
PB,5.5x5.5x8,C2,ModelName
it's that last field is missing: the first two records in your data have
fewer commas than the third.

One approach would be to write code that counts the number of commas and
adds one at the end if necessary. A simple way to count the commas is to
use Replace() to replace "," with "" and comparing the length of the
resulting string with the length of the original.

Another would be to use a regular expression like this to parse the
string:

^([^,]*),(?[0-9.]+)x([0-9.]+)x([0-9.]+))?,([^,]*)(?:,(.+))?$

Having the a problem with the below function. I am parsing data below:

[quoted text clipped - 48 lines]

End Function

 

[John Nurick]

One way is to use it with the rgxExtract() function at
http://www.j.nurick.dial.pipex.com/Code/vbRegex/rgxExtract.htm

This works in queries.

Hi John,

I would I use this?: ^([^,]*),(?[0-9.]+)x([0-9.]+)x([0-9.]+))?,([^,]*)(?:,
(.+))?$
Do I put it in the query?

Matt

Hi Matt,

It's not that the last field is null: in that case the sample data would
look like this:
CC,4.5x5x7,C2,
CC,4x5x7,C2,
PB,5.5x5.5x8,C2,ModelName
it's that last field is missing: the first two records in your data have
fewer commas than the third.

One approach would be to write code that counts the number of commas and
adds one at the end if necessary. A simple way to count the commas is to
use Replace() to replace "," with "" and comparing the length of the
resulting string with the length of the original.

Another would be to use a regular expression like this to parse the
string:

^([^,]*),(?[0-9.]+)x([0-9.]+)x([0-9.]+))?,([^,]*)(?:,(.+))?$

Having the a problem with the below function. I am parsing data below:

[quoted text clipped - 48 lines]

End Function