Array/Split String Help

[Pete]

I have a label creating program that creates records in a temporary table,
then prints them. The user can enter a range of product numbers into a text
box, e.g. “1-10†and the records are created accordingly. Code is as follows
(I have simplified the SQL string as there are actually more fields).

Dim n As Integer
Dim varRange As Variant
ReDim varRange(2)
Dim strSQL As String
varRange = Split(Me.txtRange, "-")
For n = varRange(0) To varRange(1)
strSQL = "INSERT INTO tmpLabel(nNo) VALUES( '" & n & "')"
DoCmd.SetWarnings (False)
DoCmd.RunSQL strSQL
DoCmd.SetWarnings (True)
Next n

I now wish to enhance this, so that the user can enter comma separated
values as well as ranges, similar to the way that you can enter a range of
pages to print in the Print Dialog e.g.

1-10,15,18,21 would create an array containing 13 elements

1-20,23,25,31-40 would create an array containing 22 elements

I need to split the array on both hyphens and commas, sort the array
elements into numerical order and count the number of elements so that I know
how many times to loop though my INSERT query. I am just not sure what the
best way is to go about doing this is.

All help appreciated.

 

[Douglas J. Steele]

Split first on commas, then on dashes:

Dim n As Integer
Dim n2 As Integer
Dim varRange As Variant
Dim varRange2 As Variant
Dim strSQL As String

varRange = Split(Me.txtRange, ",")
For n = LBound(varRange) To UBound(varRange)
varRange2 = Split(varRange(n), "-")
For n2 = LBound(varRange2) To UBound(varRange2)
strSQL = "INSERT INTO tmpLabel(nNo) VALUES( '" & n2 & "')"
DoCmd.SetWarnings (False)
DoCmd.RunSQL strSQL
DoCmd.SetWarnings (True)
Next n2
Next n

 

[Pete]

Hi Doug

Thanks for your reply. After my second cup of coffee I too realised that the
key to this problem was splitting on commas first (brain was a little slow to
get going on a Monday morning!). However, I wanted it to create records for
every value in the range, e.g. 1-10 would create 10 records numbered 1, 2, 3,
etc For the benefit of the group I am publishing my solution:

Dim n As Integer, intNo As Integer, intCount As Integer
Dim varNo As Variant, varNo2 As Variant
ReDim varNo(InStr(1, Me.txtRange, ","))
ReDim varNo2(2)

varNo = Split(Me. txtRange, ",")
For intCount = LBound(varNo) To UBound(varNo)
If InStr(1, varNo(intCount), "-") Then
varNo2 = Split(varNo(intCount), "-")
For n = varNo2(0) To varNo2(1)
intNo = n
strSQL = "INSERT INTO tmpLabel(nNo) VALUES('" & intNo & "')"
DoCmd.SetWarnings (False)
DoCmd.RunSQL strSQL
DoCmd.SetWarnings (True)
Next n
Else
intNo = varNo(intCount)
strSQL = "INSERT INTO tmpLabel(nNo) VALUES('" & intNo & "')"
DoCmd.SetWarnings (False)
DoCmd.RunSQL strSQL
DoCmd.SetWarnings (True)
End If
Next

--
Peter Schmidt
Ross-on-Wye, UK

Split first on commas, then on dashes:

Dim n As Integer
Dim n2 As Integer
Dim varRange As Variant
Dim varRange2 As Variant
Dim strSQL As String

varRange = Split(Me.txtRange, ",")
For n = LBound(varRange) To UBound(varRange)
varRange2 = Split(varRange(n), "-")
For n2 = LBound(varRange2) To UBound(varRange2)
strSQL = "INSERT INTO tmpLabel(nNo) VALUES( '" & n2 & "')"
DoCmd.SetWarnings (False)
DoCmd.RunSQL strSQL
DoCmd.SetWarnings (True)
Next n2
Next n