By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,479 Members | 2,260 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,479 IT Pros & Developers. It's quick & easy.

Help with delimiters

P: 3
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.
Jul 17 '07 #1
Share this Question
Share on Google+
1 Reply


JKing
Expert 100+
P: 1,206
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.

Expand|Select|Wrap|Line Numbers
  1. Dim strFinal As String
  2. Dim strTemp As String
  3. Dim strDashString
  4. Dim intFirst As Integer
  5. Dim intLast As Integer
  6. Dim intDashPos As Integer
  7. Dim intCount As Integer
  8. Dim strSubString As String
  9.  
  10. intCount = 0
  11. strTemp = Me.yourTextBox
  12.  
  13. Do Until Len(strTemp) = 0
  14.     'Check for commas in the string
  15.     If InStr(strTemp, ",") > 0 Then
  16.         strSubString = Left(strTemp, InStr(strTemp, ","))
  17.     Else
  18.         'If there are no commas the string is at it's lowest substring
  19.         strSubString = strTemp
  20.     End If
  21.  
  22.     'Check for dashes in the sub string
  23.     intDashPos = InStr(strSubString, "-")
  24.  
  25.     'If there's a dash find the starting and ending number for the range
  26.     If intDashPos > 0 Then
  27.         strDashString = Left(strSubString, intDashPos - 1)
  28.         intFirst = Mid(strDashString, InStrRev(strDashString, ",") + 1)
  29.         strDashString = Right(strSubString, Len(strSubString) - intDashPos)
  30.         intLast = Mid(strDashString, 1, InStr(strDashString, ",") - 1)
  31.         'Loop through the range adding each number to the string
  32.         For intCount = intFirst To intLast
  33.             strFinal = strFinal & intCount & ","
  34.         Next
  35.     Else
  36.         'No dash simply add the substring
  37.         strFinal = strFinal & strSubString
  38.     End If
  39.  
  40.     'If there's commas left trim down the string to the next number
  41.     If InStr(strTemp, ",") > 0 Then
  42.         strTemp = Right(strTemp, Len(strTemp) - InStr(strTemp, ","))
  43.     Else
  44.         strTemp = ""
  45.     End If
  46. Loop
  47.  
  48. MsgBox strFinal
  49.  
  50.  
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.
Jul 18 '07 #2

Post your reply

Sign in to post your reply or Sign up for a free account.