435,483 Members | 3,229 Online
Need help? Post your question and get tips & solutions from a community of 435,483 IT Pros & Developers. It's quick & easy.

# If Statement with a Do Until Statement

4 Replies

 P: 7 One more thing. Why does Access show a number like 128 as less than 68? It seems it will only check two numbers correctly if they are the same amount of characters (ie 46 and 87, 198 and 145, 3 and 8). Is there a way around this? Dec 19 '08 #2

 P: 36 Have you considered moving the values you are comparing into variables? That might make it easier to read when you step through it in debug mode. The only problem I've had with numbers not reading as they should was converting a string to an integer. If the number isn't explicitly an integer odd things can happen. You can force Access to see it as an integer by defining a variable, or doing math with it where you do the comparison. Expand|Select|Wrap|Line Numbers Dim intQtyLost as Integer Dim intLostItems as Integer   intQtyLost = Me.lstQtyLost intLostItems = Me.lstbxLostItems.Column(5, i) or --> Expand|Select|Wrap|Line Numbers If Me.lstQtyLost + 1 > Me.lstbxLostItems.Column(5, i) + 1 Then Dec 19 '08 #3

 100+ P: 675 Number Problem: When Access compares 2 strings, the strings are compared character-by-character. If the 2 1st characters are identical, then Access proceeds to the 2nd characters, and so on. Therefore, all strings beginning with "1" are less than those beginning with "2". This will give the results you are seeing. Assume txtA = "27" and txtB = "4". txtA is less than txtB, because "2" is less than "4". You can use the Val function to compare strings starting with simple numbers. Val(txtB) is less than Val(txtA). See Help - Type Conversion Functions for other numeric conversions. Dec 19 '08 #4

 P: 7 Thanks a ton you two. I kind searched with what you've both said and come up with a solution to solve the number problem. Expand|Select|Wrap|Line Numbers If CInt(Me.lstQtyLost) > CInt(Me.lstbxLostItems.Column(5, i)) Then                 Do Until CInt(Me.lstQtyLost) <= CInt(Me.lstbxLostItems.Column(5, i))                 MsgBox ("You entered " & Me.lstQtyLost & " as lost but there are only " & Me.lstbxLostItems.Column(5, i) & " of Material " & Me.lstbxLostItems.Column(6, i) & " " & Me.lstbxLostItems.Column(7, i) & " on the order. Please enter a new quantity.")                     strInput = InputBox(Prompt:=strMsg, Title:="Quantity Lost")                     Me.lstQtyLost = strInput                     Loop                 Else                 End If Cint() did the trick and even the loop seems to be working ok. I haven't broke it yet at least, ha. Thanks again, let me know if you have any other suggestions and I'll let you know if I break something else. Dan Dec 19 '08 #5