If...Then Evaluates 7 as Greater Than 10!

[JAB]

I have a database where a user selects on a form, a year and a range of periods (months in the year) on which to run a report. The periods are between 1 and 12. Validation code checks to make sure the From value is less than the To value. Example: to see data from July through October the user would enter 7 in the From field and 10 in the To field. If the To field value was less than 7 a message would pop up telling them of the error.

Here is the validation code:
If Me.cboFPFrom > Me.cboFPTo Then
MsgBox "Period From value must be less than the Period To value."
blnValidation = True
Me.cboFPFrom.SetFocus
GoTo EndValidation
End If

When I step through the code Me.cboFPFrom shows the value "7" and Me.cboFPTo shows "10". The intriquing part of this is the code has worked just fine for the last 18 months. So what has changed? Here where I work this last month the IT group installed more security updates (which happens often actually) and a patch to allow everyone to read and write to Office 2007 files (we use Office 2003). So now suddenly the logical evaluation is backward. Any ideas?!!

Thanks in advance,
John

 

[KenHigg]

Maybe you need to cint() the text box values:

If cint(Me.cboFPFrom) > cint(Me.cboFPTo)

As it maybe evaluating them as text - ?

 

[rapsr59]

You might try using val() with the cbo controls.

 

[rapsr59]

Try using:

    If [COLOR=red]Val([/COLOR]Me.cboFPFrom[COLOR=red])[/COLOR] > [COLOR=red]Val([/COLOR]Me.cboFPTo[COLOR=red])[/COLOR] Then
        MsgBox "Period From value must be less than the Period To value."
        blnValidation = True
        Me.cboFPFrom.SetFocus
        GoTo EndValidation
    End If

 

[MSAccessRookie]

I have a database where a user selects on a form, a year and a range of periods (months in the year) on which to run a report. The periods are between 1 and 12. Validation code checks to make sure the From value is less than the To value. Example: to see data from July through October the user would enter 7 in the From field and 10 in the To field. If the To field value was less than 7 a message would pop up telling them of the error.

Here is the validation code:
If Me.cboFPFrom > Me.cboFPTo Then
MsgBox "Period From value must be less than the Period To value."
blnValidation = True
Me.cboFPFrom.SetFocus
GoTo EndValidation
End If

When I step through the code Me.cboFPFrom shows the value "7" and Me.cboFPTo shows "10". The intriquing part of this is the code has worked just fine for the last 18 months. So what has changed? Here where I work this last month the IT group installed more security updates (which happens often actually) and a patch to allow everyone to read and write to Office 2007 files (we use Office 2003). So now suddenly the logical evaluation is backward. Any ideas?!!

Thanks in advance,
John

Sounds to me like the Control Field is defined as Text, and not Number. Check the Field Data type.

Update: I really need to read entire posts before I reply. I just noticed that Ken said the same thing, only he also proposed a possible solution if the field was Text.

 

[KenHigg]

Sounds to me like the Control Field is defined as Text, and not Number. Check the Field Data type.

Update: I really need to read entire posts before I reply. I just noticed that Ken said the same thing, only he also proposed a possible solution if the field was Text.

And I'm not sure the the controls in question are bound so there wouldn't be a data type involved anywhere would there?

 

[JAB]

These are unbound fields but the good news is that CInt() worked. Thanks again for the help.

John

 

[KenHigg]

Cool. Glad you have it working. I'm sure val() would work, cint() is just a habit of mine I guess...

 

[MSAccessRookie]

And I'm not sure the the controls in question are bound so there wouldn't be a data type involved anywhere would there?

I thought that you can define the input data type for any Control Field. Anyway, it looks like your advice about CInt() was what he needed.

 

[Banana]

I thought that you can define the input data type for any Control Field. Anyway, it looks like your advice about CInt() was what he needed.

As FYI, there is a disparity between values retrieved in database (e.g. JET), and Access controls/VBA. Controls' Value property returns a Variant, even if it is bound to a field that has a specific data type declared by Jet. The conversion is automatic and silent, but it could be the factor for why it sometimes doesn't behave correctly.

 

[gemma-the-husky]

if it WAS testing strings (and ther op said "7" and "10") then "7" IS greater than "10"