code wont recognizie that 10 is greater than 3

  • Thread starter Thread starter Cadburys
  • Start date Start date
C

Cadburys

Please help...

I have the following code in the after update event of a combo box. The
first 3 conditions are fine but with the last 3 if one zone is 2 and the
other is 10 then it is returning the 2 as the highest value. I have tried
declaring all variables as Integers but then the HighestZone is not updated
immediately and the message box pops up even with the first 3 conditions. Why
are the last 3 conditions NOT recognizing
values over 9 as being greater.


ZoneFrom = DLookup("[Zone]", "tblSuburbs", "[SuburbName] = '" _
& Forms!Bookings!SuburbFrom & "'")


If Me.ZoneTo = 1 And Me.ZoneFrom = 1 Then
Me.HighestZone = 1
Me.Additional = 0

ElseIf Me.ZoneFrom = 1 And Me.ZoneTo > 1 Then
Me.HighestZone = ZoneTo
Me.Additional = 0

ElseIf Me.ZoneTo = 1 And Me.ZoneFrom > 1 Then
Me.HighestZone = Me.ZoneFrom
Me.Additional = 0

ElseIf Me.ZoneTo <> 1 And Me.ZoneFrom <> 1 And Me.ZoneTo > ZoneFrom Then
Me.HighestZone = ZoneTo
MsgBox "Neither the pick up nor the drop off are within Zone 1,
an additional cost will added to the normal rate. Please ammend the
additional cost if necessary.", vbOKOnly, "No Zone 1"
Me.Additional = 50

ElseIf Me.ZoneFrom <> 1 And Me.ZoneTo <> 1 And Me.ZoneFrom > Me.ZoneTo
Then
MsgBox "Neither the pick up nor the drop off are within Zone 1,
an additional cost will added to the normal rate. Please ammend the
additional cost if necessary.", vbOKOnly, "No Zone 1"
Me.HighestZone = ZoneFrom
Me.Additional = 50

ElseIf Me.ZoneTo <> 1 And Me.ZoneFrom <> 1 And Me.ZoneFrom = Me.ZoneTo
Then
MsgBox "Neither the pick up nor the drop off are within Zone 1,
an additional cost will added to the normal rate. Please ammend the
additional cost if necessary.", vbOKOnly, "No Zone 1"
Me.HighestZone = ZoneTo
Me.Additional = 50
End If

End Sub

Any help would be appreciated.
 
Cadburys wrote
I have the following code in the after update event of a combo box. The
first 3 conditions are fine but with the last 3 if one zone is 2 and the
other is 10 then it is returning the 2 as the highest value. I have tried
declaring all variables as Integers but then the HighestZone is not updated
immediately and the message box pops up even with the first 3 conditions. Why
are the last 3 conditions NOT recognizing
values over 9 as being greater.


ZoneFrom = DLookup("[Zone]", "tblSuburbs", "[SuburbName] = '" _
& Forms!Bookings!SuburbFrom & "'")


If Me.ZoneTo = 1 And Me.ZoneFrom = 1 Then
Me.HighestZone = 1
Me.Additional = 0

ElseIf Me.ZoneFrom = 1 And Me.ZoneTo > 1 Then
Me.HighestZone = ZoneTo
Me.Additional = 0

ElseIf Me.ZoneTo = 1 And Me.ZoneFrom > 1 Then
Me.HighestZone = Me.ZoneFrom
Me.Additional = 0

ElseIf Me.ZoneTo <> 1 And Me.ZoneFrom <> 1 And Me.ZoneTo > ZoneFrom Then
Me.HighestZone = ZoneTo
MsgBox "Neither the pick up nor the drop off are within Zone 1,
an additional cost will added to the normal rate. Please ammend the
additional cost if necessary.", vbOKOnly, "No Zone 1"
Me.Additional = 50

ElseIf Me.ZoneFrom <> 1 And Me.ZoneTo <> 1 And Me.ZoneFrom > Me.ZoneTo
Then
MsgBox "Neither the pick up nor the drop off are within Zone 1,
an additional cost will added to the normal rate. Please ammend the
additional cost if necessary.", vbOKOnly, "No Zone 1"
Me.HighestZone = ZoneFrom
Me.Additional = 50

ElseIf Me.ZoneTo <> 1 And Me.ZoneFrom <> 1 And Me.ZoneFrom = Me.ZoneTo
Then
MsgBox "Neither the pick up nor the drop off are within Zone 1,
an additional cost will added to the normal rate. Please ammend the
additional cost if necessary.", vbOKOnly, "No Zone 1"
Me.HighestZone = ZoneTo
Me.Additional = 50
End If

End Sub
Click to expand...


When 10 sorts before 2, the values are text strings and they
are sorted in dictionary order. If you want to use
numerical sorting, you should store the values in a number
type field. If you can not do that, you will have to use
Val, Cint or CLng to convert the text values to a number.
 
Sounds like you need to define your Zone fields as Number
rather than Text. Also, HighestZone would probably be more
appropriate as a calculated field in a query rather than a
table field. However, if you are going to do it in code and
"push" a value into the HighestZone field, you could greatly
simplify it by just doing the following;


Dim strMsg As String

strMsg = "Neither the pick up nor the drop off are within" _
& " Zone 1, > an additional cost will added to the" _
& " normal rate. Please ammend the additional cost if necessary."

If Me.ZoneTo <> 1 And Me.ZoneFrom <> 1 Then
MsgBox strMsg, vbOKOnly, "No Zone 1"
Me.Additional = 50
End If

If Me.ZoneFrom > Me.ZoneTo Then
Me.HighestZone = ZoneFrom
Else
Me.HighestZone = ZoneTo
End If


Again, in most cases I would recommend against pushing
a calculated value like this into a table field. If the underlying
values that the calculation is based on subsequently get changed,
the calculated value may not necessarily get updated in the table.

--
_________

Sean Bailey


Cadburys said:
Please help...

I have the following code in the after update event of a combo box. The
first 3 conditions are fine but with the last 3 if one zone is 2 and the
other is 10 then it is returning the 2 as the highest value. I have tried
declaring all variables as Integers but then the HighestZone is not updated
immediately and the message box pops up even with the first 3 conditions. Why
are the last 3 conditions NOT recognizing
values over 9 as being greater.


ZoneFrom = DLookup("[Zone]", "tblSuburbs", "[SuburbName] = '" _
& Forms!Bookings!SuburbFrom & "'")


If Me.ZoneTo = 1 And Me.ZoneFrom = 1 Then
Me.HighestZone = 1
Me.Additional = 0

ElseIf Me.ZoneFrom = 1 And Me.ZoneTo > 1 Then
Me.HighestZone = ZoneTo
Me.Additional = 0

ElseIf Me.ZoneTo = 1 And Me.ZoneFrom > 1 Then
Me.HighestZone = Me.ZoneFrom
Me.Additional = 0

ElseIf Me.ZoneTo <> 1 And Me.ZoneFrom <> 1 And Me.ZoneTo > ZoneFrom Then
Me.HighestZone = ZoneTo
MsgBox "Neither the pick up nor the drop off are within Zone 1,
an additional cost will added to the normal rate. Please ammend the
additional cost if necessary.", vbOKOnly, "No Zone 1"
Me.Additional = 50

ElseIf Me.ZoneFrom <> 1 And Me.ZoneTo <> 1 And Me.ZoneFrom > Me.ZoneTo
Then
MsgBox "Neither the pick up nor the drop off are within Zone 1,
an additional cost will added to the normal rate. Please ammend the
additional cost if necessary.", vbOKOnly, "No Zone 1"
Me.HighestZone = ZoneFrom
Me.Additional = 50

ElseIf Me.ZoneTo <> 1 And Me.ZoneFrom <> 1 And Me.ZoneFrom = Me.ZoneTo
Then
MsgBox "Neither the pick up nor the drop off are within Zone 1,
an additional cost will added to the normal rate. Please ammend the
additional cost if necessary.", vbOKOnly, "No Zone 1"
Me.HighestZone = ZoneTo
Me.Additional = 50
End If

End Sub

Any help would be appreciated.
Click to expand...
 
Back
Top