How to set negative numbers to 0

[Ezzz]

I have an inventory database part of which does a small auto calculation, by subtracting the number received from the number ordered. See below!
=[Products Subform].Form!UnitsOnOr der

The problem is when i receive something without ordering it my calculation box ends up with a minus number.
Is there a way to set all minus figures to zero?

[TheSmileyCoder]

Well you could run a update query....

If you want it to automatically set it to zero, you could do:

=IIF([Products Subform].Form!UnitsOnOrder>0;[Products Subform].Form!UnitsOnOrder;0)

[Ezzz]

Calculations

Sorry gave the wrong info last time. The expression:-
=[Products Subform].Form!UnitsOnOr der
is the control source. (Not ultimately sure what this is/does)
The actual formula used for the calculation is:-
=Sum(nz([UnitsOrdered])-nz([UnitsReceived]))
Is there a way to adapt this formula so that any negative numbers always read zero?
Sorry to mess you about, but my knowledge is very limited, the database is a a microsoft template thats adapted for use, as best i can.
Regards
Ezzz

[TheSmileyCoder]

The code I provided could easily be adapted for another field.
Basicly its checking if the field is larger then 0, and if not, it sets the field to 0. If it is larger, it just performs the calculation.

=IIF(Sum(nz([UnitsOrdered])-nz([UnitsReceived]))>0; Sum(nz([UnitsOrdered])-nz([UnitsReceived])); 0)

[Ezzz]

Calculations

I have tried to input your formula see below:-
= IIf(Sum(nz([UnitsOrdered])-nz([UnitsReceived]))>0; Sum(nz([UnitsOrdered])-nz([UnitsReceived])); 0)
But it throws up an error saying it contains an invalid syntax?
I assume that trying to do this in the "Expression Builder" is the correct way to do this. But having never used it before I'm not sure if i am doing it correctly?
The only difference i can see is the IIf statement which i used from the "Built in functions" box of the expression builder, and then typed the rest in by hand.
Any ideas.
Ezzz

[TheSmileyCoder]

Usually when you get that error, Access will position the cursor at the part with invalid syntax. Where is that?

It could be that you need to replace ; with, that might depend on your regional settings (If I had a penny for each time regional settings gave me a headache......) .

[Ezzz]

You were spot on.
Have replaced the : with , and the formula now works a treat.
Many thanks.
Ezzz