Returning zero when calculated field is negative (1 Viewer)

J

janeyd

Registered User.

Local time

Today, 05:31

Joined

Jun 24, 2011

Messages

27

• Aug 14, 2017

• #1

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

 

pbaldy

Wino Moderator

Staff member

Local time

Yesterday, 21:31

Joined

Aug 30, 2003

Messages

36,125

• Aug 14, 2017

• #2

You mention criteria; it should be in the field row, as you have it above.

 

arnelgp

..forever waiting... waiting for jellybean!

Local time

Today, 12:31

Joined

May 7, 2009

Messages

19,243

• Aug 14, 2017

• #3

Try this expression

IIf(Nz([Total Stock],0)<Nz([SumOfKG],0),0,Nz([Total Stock],0)-Nz([SumOfKG],0))

Click to expand...

 

arnelgp

..forever waiting... waiting for jellybean!

Local time

Today, 12:31

Joined

May 7, 2009

Messages

19,243

• Aug 14, 2017

• #4

Or you can try this

Switch((Nz([Total Stock],0)<Nz([SumOfKG],0)),0,True,Nz([Total Stock],0)-Nz([SumOfKG],0))

Click to expand...

 

J

janeyd

Registered User.

Local time

Today, 05:31

Joined

Jun 24, 2011

Messages

27

• Aug 15, 2017

• #5

Thank you. Funny how it's always obvious when someone tells you the answer