Queries and showing Null Value as 0

[NewYorkFashionToGo]

Hello, I posted a few days ago about a problem that I was having, I have made some ground with it, but I have a new problem now, Here is the situation:

I am trying to create a table or query that shows a count of how much inventory is in a particular area by summarizing the query. I created several single queries to go along with my product ID In Inventory, One query is "Awaiting Payment" Another is Shipped, Another Is "Purchases", Every single one of them work perfectly when left alone, But I am trying to Get them all on One Table Query or Form, When I try to put the summarized Active query and Summarized Shipped query together. If a product is not on one or the others list, It completely wipes out the other products.

I would like to have all the Product IDs on One table query or form NO MATTER WHAT and If a value in awaiting shipment doesnt exist for that product show it as 0 or Null But SHOW IT, Right now its completely eliminating The Product ID, I tried a Awaiting Payment: IIf(Null,"0",[TotalsAwaitingPaymentQuery]![AwaitingPayment]) That did not work.

Is ther a work around for this, I would eventually like to have them all on a form datasheet view because I like the conditional formats of changing colors of the cells when a product is low for re-order or a different color for backorders.

 

[AlexCuse]

Awaiting Payment: IIf([TotalsAwaitingPaymentQuery].[AwaitingPayment] is null, 0, [TotalsAwaitingPaymentQuery].[AwaitingPayment]){/code]
Should do the trick.

Hope tis helps,

Alex

A wise man once said
"The only thing normal about database guys is their tables".

 

[NewYorkFashionToGo]

Thank you That was very Helpful, and worked perfectly.

 

[AlexCuse]

Glad it worked!

A wise man once said
"The only thing normal about database guys is their tables".

 

[PHV]

A simpler way:
Awaiting Payment: Nz([TotalsAwaitingPaymentQuery].[AwaitingPayment], 0)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886