Solved Textbox Calculation, a question of conditional value (1 Viewer)

[Anthony.DG]

I have a form for calculating freight for an order with a textbox that works just fine. BUT my client says they want to implement a minimum freight charge of $80. (they deliver, dirt and gravel). So the client asks that if the calculation of the freight is less than 80 then they want the textbox to say $80. Now, I'm still fairly new to access but is this possible through a textbox? Or is this a deeper dive?

 

[MajP]

you can do that in a textbox. You make a calculated control and you can use the iif function

MS Access: iif Function

This MSAccess tutorial explains how to use the Access iif function with syntax and examples. The Microsoft Access iif function returns one value if a specified condition evaluates to TRUE, or another value if it evaluates to FALSE.

www.techonthenet.com

assume field is called "freightCharge". In the control source
=iif([freghtCharge] < 80,80,[freightCharge])

 

[arnelgp]

set the Default Value property of the [Freight Charge] to 80.
then add code to the Form's Before Update event:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Cancel = Nz(Me![Freight Charge], 0) < 80
If Cancel Then
    MsgBox "minimum Freight Charge is 80."
    With Me![Freight Charge]
        .SetFocus
        .Value = .DefaultValue
    End With
End If
End Sub

 

[Anthony.DG]

you can do that in a textbox. You make a calculated control and you can use the iif function

MS Access: iif Function

This MSAccess tutorial explains how to use the Access iif function with syntax and examples. The Microsoft Access iif function returns one value if a specified condition evaluates to TRUE, or another value if it evaluates to FALSE.

www.techonthenet.com

assume field is called "freightCharge". In the control source
=iif([freghtCharge] < 80,80,[freightCharge])

Thank you so much! Worked like a charm!

 

[Anthony.DG]

set the Default Value property of the [Freight Charge] to 80.
then add code to the Form's Before Update event:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Cancel = Nz(Me![Freight Charge], 0) < 80
If Cancel Then
    MsgBox "minimum Freight Charge is 80."
    With Me![Freight Charge]
        .SetFocus
        .Value = .DefaultValue
    End With
End If
End Sub

Thank you I will try this out!