Weird Query Problem

[Rixx]

Elo all,

Firstly, sorry for the long post! Its very hard getting the right advice about this so I wanted to be thorough. Any help will be appreciated!!

I have a problem with a query and just when I thought I was done! Im creating an 'on hand' inventory control system. I have a value for when I receive stock from an order. I have a value for the initial amounts of stock I first enter in a table (Products table) and I have a value for stock that I have sold.

I have put this all into a query so it gives me amount of stock in Real time. However, I have just noticed the following problem:

When I add a product eg 10 PENS, at that moment I havent sold any pens and I havent had a delivery for any pens. So, the values I have are: 10,0,0

When I run the query, the number that comes up is ' ' ie nala, nothing, zip!

Reason for this is because I am performing a calculation in the query:

Items in Stock: Sum(-Newstockpart1.Quantity+Items.ItemStock+NewAcqPart1.Quantity)


My question is, is there a way of making the query return a value in a combination of situations ie:

SITUATION 1
10Pens added originally, 0 from a delivery but ive sold 1.. so the calculation should be [-1+10+0=9]

SITUATION2
10Pens added originally, 10 from a delivery but ive sold 0.. so the calculation is: [-0+10+10=20]

SITUATION3
10Pens added originally, 0 from a delivery and sold 0.. so the calculation is: [-0+10+0=10]


If any access guru out there can help I will be very appreciative!!


Thanks

 

[DALeffler]

I believe you're seeing a null value propagate through your expression. Try:

Items in Stock: Sum(-Nz(Newstockpart1.Quantity,0)+Nz(Items.ItemStock,0)+Nz(NewAcqPart1.Quantity,0))

 

[Rixx]

Hi mate,

I had sorted the problem out before I could get around to reading your post! If only I had read it earlier, it could have saved me a lot of bother, because that is exactly what I needed to do!

Thanks for the response in any case