Prev: Ms Access got an error and quit
Next: Which Form Event
From: Cadburys on 5 Nov 2009 15:41
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.

--
Cheers
From: Marshall Barton on 5 Nov 2009 16:46
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


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.

--
Marsh
MVP [MS Access]
From: Beetle on 5 Nov 2009 18:41
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" wrote:

> 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.
>
> --
> Cheers
 | 
Pages: 1
Prev: Ms Access got an error and quit
Next: Which Form Event