A Value minus nothing, should equal Value

[Trevor Howard]

Hello, I need a bit of help please with the following query.

I have a query based on a products table “UnitsReceived” and Orders Detail table “QtySold”

My auto calculation field in the query is as follows:-
In Stock: [UnitsReceived]-[QtySold]

If there is no value in field “QtySold” I get a blank return for that product line, I would like to show the value held in UnitsReceived field, since there is nothing to deduct from itself.


Starting Qty minus nothing, should equal Starting Qty

Thanks for your advice

 

[SOS]

Use the NZ function:

Nz([UnitsReceived],0)-Nz([QtySold],0)

 

[Trevor Howard]

WOW you were quick

It worked. I have spent hours trying to figure this out.

Thank you SOS

Best wishes,
trevor

 

[SOS]

Sure thing - best wishes for a great weekend!

 

[gemma-the-husky]

nothing and nulls are a problem

a value minus zero equals the value

a value minus nothing is undetermined, therefore still nothing.

once understood, never forgotten - nz is generally your friend

 

[Trevor Howard]

Thanks to you both, This one had me stumped!
I like to work my way through problems and in most cases I get it or with the help of searching the forum since most of the time someone else has gone before me.


Have a good weekend.