Help with delimiters

[grahama2]

Greetings everyone,

I have set up a text box to accept a string of numbers seperated by commas. The numbers are seperated and each runs through a query. I would like to also be able to seperate the numbers with a dash, and have those numbers treated like a range of numbers. For instance, when I put in 34,37,48-54, I'd like to change that into 34,37,48,49,50,51,52,53,54.
Thanks in advance.

[JKing]

Hi, I think this will properly handle your question. Thought it seems a bit long and there may be a more efficient way to do this.

Dim strFinal As String
Dim strTemp As String
Dim strDashString
Dim intFirst As Integer
Dim intLast As Integer
Dim intDashPos As Integer
Dim intCount As Integer
Dim strSubString As String
 
intCount = 0
strTemp = Me.yourTextBox
 
Do Until Len(strTemp) = 0
    'Check for commas in the string
    If InStr(strTemp, ",") > 0 Then
        strSubString = Left(strTemp, InStr(strTemp, ","))
    Else
        'If there are no commas the string is at it's lowest substring
        strSubString = strTemp
    End If
 
    'Check for dashes in the sub string
    intDashPos = InStr(strSubString, "-")
 
    'If there's a dash find the starting and ending number for the range
    If intDashPos > 0 Then
        strDashString = Left(strSubString, intDashPos - 1)
        intFirst = Mid(strDashString, InStrRev(strDashString, ",") + 1)
        strDashString = Right(strSubString, Len(strSubString) - intDashPos)
        intLast = Mid(strDashString, 1, InStr(strDashString, ",") - 1)
        'Loop through the range adding each number to the string
        For intCount = intFirst To intLast
            strFinal = strFinal & intCount & ","
        Next
    Else
        'No dash simply add the substring
        strFinal = strFinal & strSubString
    End If
 
    'If there's commas left trim down the string to the next number
    If InStr(strTemp, ",") > 0 Then
        strTemp = Right(strTemp, Len(strTemp) - InStr(strTemp, ","))
    Else
        strTemp = ""
    End If
Loop
 
MsgBox strFinal
 
 

This code loops through the textbox string and seperates it into substrings adding them to a new string. Upon finding a substring with a dash it grabs the numbers on either sides of the dash and runs a for loop between those numbers adding each one to the new string. The code will work whether or not the user ends the string with a comma.

If 35,40,45-50,65 was entered in the textbox strFinal would display 35,40,45,46,47,48,49,50,65.

If you need help applying the numbers to your query using the code I wrote up, I can help with that too.