Zero and Nul values in calculations

[mzungu]

Help!

I have a database that records all procurements and requisitions of our medical supplies.

I have set up queries that sum all requisitions in a set time frame for each item that should be in the store (i.e. this month, this year etc) and similar query for the procurements.

A further query then subtracts the requisitions from the procurements and (should) give me an inventory of all medical supplies.

BUT.....

If no items have been procured or requisitioned, they are excluded from the sums. We have a standard list of items (which is a table in the DBase) and I need to know if we have no stock (there are certain things we can't get all the time in Tanzania, so procurements and requisitions are zero/nul for a while and I need the Dbase to highlight the lack of items).

Please help!

Note, if there is away of doing this with out VB I would be very happy as I have never used VB and it terrifies me.

Matt

 

[macca the hacke]

you need to use the nz function in your query. This changes a null value to whatever you specify - in this case I would guess that would be 0.

 

[AlphaZeroX]

Yea, i was going to suggest just changing every null value to the number "0" but using the nz function in the query would definitely help alot. Doing that will make it easier to search for what you dont have in inventory.
To do this try using an update query function to change all " " to "0".
Hope this helps.

 

[pbaldy]

Depending on your setup, the nz() function may not help. You may have to left join to your table containing the list of items to get them to show up in a query of a transactions table when no transactions for them exist.