Help with my 'if' statement - not resolving correctly

[fraser_lindsay]

Help!

I have some code which runs in the after update of an unbound text box. The user enters a number of questions required which is compared to the total number on the record set, also from a unbound combo on the form.

The code works (kind of) but for some reason the 'IF' part isn't working and I can't see why.

I want the latter part of the code to trigger only if the users requests more questions than there is in the database, but it triggers no matter what they type in, even if it is less than the total number. Why?

If Me.txtMCQnumber.Value < Me.txtTotalMCQ.Value Then
'do nothing
Else
    On Error GoTo txtMCQnumber_AfterUpdate_Err
    Dim intAnswer As Integer
    Dim strSQL As String
    intAnswer = MsgBox("There are only " & Me.txtTotalMCQ.Value & _
         " multiple choice questions in the database." & vbCrLf & _
        "Would you like to view all questions?" _
        , vbQuestion + vbYesNo, "Industrial Hygiene Exam Database")
                    
        If intAnswer = vbYes Then
        Me.txtMCQnumber.Value = ""
        Me.cmdMCQExam.SetFocus
        Me.txtMCQnumber.Enabled = False
        Me.chkAllQuestions.Value = -1
        DoCmd.SetWarnings False
        DoCmd.SetWarnings True
        MsgBox "All questions have been selected." _
            , vbInformation, "Industrial Hygiene Exam Database"
        Response = acDataErrAdded
    Else
        MsgBox "Please choose smaller number of questions." _
            , vbInformation, "Industrial Hygiene Exam Database"
        Response = acDataErrContinue
    End If
txtMCQnumber_AfterUpdate_Exit:
    Exit Sub
txtMCQnumber_AfterUpdate_Err:
    MsgBox Err.Description, vbCritical, "Error"
    Resume txtMCQnumber_AfterUpdate_Exit
     
End If

 

[namliam]

If Me.txtMCQnumber.Value < Me.txtTotalMCQ.Value Then

If your fields are actual text fields then 9 will be bigger than 800.

try converting these to doubles to make sure they are actual numbers... Cdbl( ) does that for you..

Hope this helps, good luck

 

[fraser_lindsay]

Amazing. I woudl never have got that in a million years.

Changed it to:

If CDbl(Me.txtMCQnumber.Value) < CDbl(Me.txtTotalMCQ.Value) Then

and it worked perfectly.

I'm not quite sure I understand why though? Is it because it forces a detection of actual numbers in the text box and not just text?

Thanks,

Fraser

 

[namliam]

A text is evaluated character by character, example order:
1
10
11
111
1111
112
1122
12222
2
22
23
3
34
35

While your expected order would be:
1
2
3
10
11
22
23
34
35
111
112
1111
1122
12222

Which in text would need to be:
00001
00002
00003
00010
00011
00022
00023
00034
00035
00111
00112
01111
01122
12222

Tekst sorting just behaves totaly differently from number sorting...

I hope you get the idea...

 

[fraser_lindsay]

I see what you mean, thanks.