Setting a negative number to 0

[plasma]

Hello all, i have a calculation in a report that sometimes results in a negative number. is there anyway to make that negative number = 0 or equal to a specific text?

 

[rainman89]

=iff((calculationfield<0), 0,[calculationfield])

or
=iff((calculationfield<0), "thats a negative number ",[calculationfield])

 

[plasma]

never mind, i figured it out. here's what I did:

=IIf([ReorderLevel]-[Units in Stock]>=0,[ReorderLevel]-[Units in Stock],0)

 

[Moniker]

As a Switch (because the Switch saves lines of code):

YourVariableName = Switch([ReorderLevel]-[Units in Stock]>=0,[ReorderLevel]-[Units in Stock],True,0)

The same thing as an If/Then in code:

If [ReorderLevel]-[Units in Stock]>=0 Then
    YourVariableName = [ReorderLevel]-[Units in Stock]
Else
    YourVariableName = 0
End If

The immediate If will work the same as the Switch in a query, but if you have more than one possible outcome (instead of it's either this or that), then Switch saves you from using multiple nested IIf statements.

For example, this situation:

If [ReorderLevel]-[Units in Stock] > 0 Then you have stock
ElseIf [ReorderLevel]-[Units in Stock] = 0 Then reorder
Else [ReorderLevel]-[Units in Stock] < 0 Then reorder now!

Using IIf, you'd need to nest two of them. Using Switch, you wouldn't. Just some good knowledge to know.