random number input - clever form???

[sha7jpm]

ok, this is a good one..!

I have a dbase which one of my users wants to input the number of batches which are archived.

Q is which is the best way to do it?

there are 100 batches for each project.

the user wil key in to the database the batches which have been archived for example,

1,5,10-15,27-39,41,44,49,60,69,80-97.

I want to be clever though and get the dbase to analyse this string to give some summary date,
ie. total batches inputted is - etc..

is there some coding that can be used to:
a. pick up the individual batches and add them together, ie. 1,5.6,7, = 4 batches.

b. pick up the batch ranges and add them together,
i.e. ,10-15, = 6 batches.

could the comma be used as seperator? or the VB code?

I am not sure if this can be done, but my other option is 100 checkboxes which is lunacy!

any help would be great. and most appreciated!

John

 

[Rich]

Your other option is actually a multi select list box

 

[sha7jpm]

thanks Rich,

I am a bit concerned about the risk of miskeying on a multiselect list.... due to the amount of batche having to be entered..

but it may be the best option.

ta

John

 

[ListO]

TRY THIS!

This does what you were talking about. It uses two textboxes on a form: txtInput and Answer. No doubt the code could be cleaned up to be more elegant, but it works. It should be placed in the On Exit module for the txtInput textbox.

Private Sub txtInput_Exit(Cancel As Integer)
Dim Lgth As Integer, X As Integer, Count As Integer, Char As String
Dim Num As String, Range1 As Integer, Range2 As Integer, Response As Integer
Dim Countadd As Integer
If IsNull(txtInput) Then GoTo txtInput_Exit_Exit

Lgth = Len([txtInput]) 'Get the length of the input string

For X = 1 To Lgth 'Evaluate each character of the input text.
Countadd = 0
Char = Mid$([txtInput], X, 1)
If Char = " " Then
If X = Lgth Then Countadd = 1
GoTo SkipDown 'Ignore Spaces
End If
If Char = "," Then 'Found the end of a single-number input
Countadd = 1 'So increment the count
Num = "" 'Clear the accumulator
GoTo SkipDown
End If

If Char = "-" Then 'Found a range.
Range1 = Val(Num) 'Save the first part of the range
Num = ""

While X < Lgth 'Don't continue if we're at end of txtInput
X = X + 1
Char = Mid$([txtInput], X, 1) ' Grab the next character in the input string
If Char = " " And Len(Num) > 0 Then GoTo FoundEndOfRange
If Asc(Char) < 48 Or Asc(Char) > 57 Then GoTo Input_Error
Num = Num + Char 'Concatenate it together
Wend

FoundEndOfRange: 'Calculate how many in the range, and add to the count
Range2 = Val(Num)
Countadd = Range2 - Range1
End If '(Char = "-")

If Asc(Char) < 48 Or Asc(Char) > 57 Then GoTo Input_Error
Num = Num + Char
If X = Lgth Then Countadd = Countadd + 1
SkipDown:
Count = Count + Countadd

Next X
Me![Answer] = Str(Count) & " bunches."
txtInput_Exit_Exit:
Exit Sub
Input_Error:
Response = MsgBox("Your range entry is not correct", vbOKCancel + vbCritical, "Screwed Up")

End Sub

 

[sha7jpm]

Brilliant!

ListO

that is fantastic, many many thanks.

am going to be difficult though!!

we have many ranges to input and this code only allows one range,

i.e. 1,2,5,7,8-10

but the typical amount of ranges entered is below...
i.e. 1,2,5,7,8-10,20-65,80-90

is there a way of when the code searches for the '-' to identify a range it can then once it has processed that range look to see if there is a another '-' after that and process the 2nd range and so on for however many ranges the user inputs....

any pointers would be very useful...

ta

john

 

[ListO]

This is what writing code is ALL about!

John, I thought it WOULD look at multiple ranges, but then I didn't test it properly for that, and as is the rule, untested is undone.

I'll take a look and see if I can alter the code just a bit to make it work correctly.

-Curt

 

[ListO]

Here it is!

I did some revisions to fix the bugs.

The entry rules are: numbers, spaces, hyphens and commas only. Numbers are dilineated by spaces OR commas, ranges are dilineated only by a hyphen between two numbers.

"1 2 3" will add up to 3
"12 3" will add up to 2.
"1,2 3" will add up to 3, etc.

Good luck.
Private Sub txtInput_Exit(Cancel As Integer)
Dim Lgth As Integer, X As Integer, Count As Integer, Char As String
Dim Num As String, Range1 As Integer, Range2 As Integer, Response As Integer
Dim Countadd As Integer
If IsNull(txtInput) Then GoTo txtInput_Exit_Exit

txtInput = Trim(txtInput)
Lgth = Len([txtInput]) 'Get the length of the input string

For X = 1 To Lgth 'Evaluate each character of the input text.
Countadd = 0 'This holds how many numbers to add to the total
Char = Mid$([txtInput], X, 1)
If Char = " " Then
If X = Lgth Then Countadd = 1
GoTo SkipDown 'Ignore Spaces
End If

If (Asc(Char) < 48 Or Asc(Char) > 57) And Char <> "," And Char <> "-" Then GoTo Input_Error

If Char = "," Then 'Found the end of a single-number input
Countadd = 1 'So increment the count
Num = "" 'Clear the accumulator
GoTo SkipDown
End If

If Char = "-" Then 'Found a range.
Range1 = Val(Num) 'Save the first part of the range
Num = "" 'Clear the accumulator

While X < Lgth 'Don't continue if we're at end of txtInput
X = X + 1
Char = Mid$([txtInput], X, 1) ' Grab the next character in the input string
If (Char = " " Or Char = ",") And Len(Num) > 0 Then GoTo FoundEndOfRange
If Asc(Char) < 48 Or Asc(Char) > 57 Then GoTo Input_Error 'It's not a number
Num = Num + Char 'Concatenate it together
Wend

FoundEndOfRange: 'Calculate how many in the range, and add to the count
Range2 = Val(Num)
Countadd = Range2 - Range1 + 1
Num = "" 'Clean up for later

'If X = Lgth Then Countadd = Countadd + 1 'Won't find the end otherwise
GoTo SkipDown
End If '(Char = "-")

Num = Num + Char
If X = Lgth Then Countadd = Countadd + 1
SkipDown:
Count = Count + Countadd

Next X

Me![Answer] = Str(Count) & " bunches."

txtInput_Exit_Exit:
Exit Sub
Input_Error:
Response = MsgBox("Your range entry is not correct", vbOKCancel + vbCritical, "Screwed Up")

End Sub

 

[sha7jpm]

Very Very Grateful indeed!

many many thanks,

I really appreciate the help you have given me, the coding works perfectly now.

kind regards

John