Show Null Records

[jland]

I made a very simple Inventory for our small stock room. The tables are Lens Products and Inventory Transactions. I made and enforced a relationship between the two tables using the ItemNo Field.

What I want to do is make a query that would calculate the stock movement [Out] on a given date interval. I am able to accomplish this properly using the SUM function.

However, there are Lens products/items wherein there was no movement [Out] on the specified date interval. And it is understandable that it will not be included in the query report. However, I still want to show these in the query result as being there was no [OUT] on the specified date interval. I've used NZ ---> Total OUT: Nz(Sum([Out]),0) and it works only if I wouldn't delimit the query to a date interval. Whenever I specify a date interval the items with no [OUT] transactions will not be shown.

I've added IS NULL in the criteria of the date and it doesn't work.

Please help. Thanks.

 

[Isskint]

can you post the SQL of the query you have already created?

For Nz(Sum([Out]),0) TRY Sum(Nz([Out],0))

 

[jland]

can you post the SQL of the query you have already created?

Hello Isskint,
Thanks for the reply. I've attached a picture of the query. Thanks.

 

[jland]

can you post the SQL of the query you have already created?

For Nz(Sum([Out]),0) TRY Sum(Nz([Out],0))

I've tried this already. It works only if I don't delimit the query to a given date interval. Once I add the date interval criteria then the null records won't show anymore.

 

[Isskint]

One issue you will encounter with the criteria you have set is that you are asking it to return records WHERE (([Description] = X*) AND ([InventoryDate] BETWEEN Z AND Y)) OR ([InventoryDate] IS Null)) So (Crit1 AND Crit2) OR (Crit3) but not BOTH sets of criteria. Try using an IIF() in the [InventoryDate] criteria, like: Iif(NZ([OUT],0)=0,IS NULL, Between Z AND Y).

 

[jland]

Hello Sir,

Yes it worked! Only that I have to place your suggested criteria in the "OR" row. It doesn't return anything if I place it at the same level with the Product Description Row.

Could you please explain to me why? Or whats happening?

Thank you sir. Thank you indeed!

 

[Isskint]

the reason is as i described, except i did not take my own advice:banghead:.

Your criteria wants to return one set of criteria OR the other, so the second criteria needs to be on the second criteria row.