Returning zero when calculated field is negative

janeyd

Registered User.
Local time
Today, 08:05
Joined
Jun 24, 2011
Messages
27
Hi
I have a query which includes a calculated field as follows:

Free Stock: Nz([Total Stock],0)-Nz([SumOfKg],0)

In some cases this returns a negative number and I would like this changed to a zero.

I have tried the following in the criteria

IIf(Nz([Total Stock],0)-Nz([SumOfKg],0)>0,Nz([Total Stock],0)-Nz([SumOfKg],0),0)

however when I run the query the lines are just missed out rather than it being there with a zero.

Can anyone help please?

Thank you
 
You mention criteria; it should be in the field row, as you have it above.
 
Try this expression
IIf(Nz([Total Stock],0)<Nz([SumOfKG],0),0,Nz([Total Stock],0)-Nz([SumOfKG],0))
 
Or you can try this
Switch((Nz([Total Stock],0)<Nz([SumOfKG],0)),0,True,Nz([Total Stock],0)-Nz([SumOfKG],0))
 
Thank you. Funny how it's always obvious when someone tells you the answer :-)
 

Users who are viewing this thread

Back
Top Bottom