nz - null values in queries

[emshim]

nz - null values in queries - CRACKED!!!

i have checked the forums and i know they kind of answer this question, but im not really sure where i am ment to write it!

I have two queries, which a third query subtracts the 1st query and 2nd query values and gives the end value. If the 2nd value is null, i get a null value at the end, as i need to change this to zero instead. i know i need to use nz (as seen in microsoft access help, and other places on this forum) but im not sure exactly where to put this on my query, and in which query.

Basically:

(qryCountpartNo.CountPartCode)-(qryTotalquantityallocated.sumofquantity) = qryTotalFree

both countpartcode and sumofquantity may be null at any time

Any help would be greatly appreciated!

Thanks in advance,

Emily

 

[Brianwarnock]

nz(qryCountpartNo.CountPartCode,0)-nz(qryTotalquantityallocated.sumofquantity,0) = qryTotalFree

Should do the trick

Brian

 

[emshim]

i am still getting the same. No result for qryTotalquantity which means no result for the qrytotalfree. I have tried putting the nz bit into the 2 first queries which create the value for the 2nd but this does not work either.

Im going to try and explain how it works a little better, and it may help you help me!

My first query, qrycountpartno, has one field only, PartCode, which is taken from another query, qryStockPartNo. The total row is selected as Count.
qryStockPartNo generates a list of the items which match the part number typed in by the user from another form.

I have then got query 2, qryPartCodesAllocated, which is made up of two fields, PartCode, which comes from a table, tblAllocation, which is the table of all the systems already allocated. The 2nd field is quantity, and this shows each row with the partcode chosen, and how many systems have been ordered from each customer. I.e.
partCode Quantity
C 3
C 1
C 4
C 1

qryTotalquantityAllocated uses the qryPartCodesAllocated and just adds up all the quantities so i have one figure to work with.

The next query, qryTotalFree has the expression, qryCountPartNo.CountOfPartCode - qryTotalquantityAllocated.sumofquantity which gives me the amount of items in stock which have not been allocated!

This all works fine, and yes, im sure there is a much better way of going about this but i had a go at the ways i knew and ended up with a complete mess of queries, but it works! Apart from now, if there are none allocated, it cannot subtract. I did try and use the expression you gave me before, but it doesn't seem to have made any difference. Perhaps i am typing it in the wrong place? I tried it in the field section in the query.

I hope this helps,

Thanks for spending the time helping me, it is much appreciated.

Emily

 

[Brianwarnock]

It may help if you can either post your SQL or attach your database, because I'm having a little difficulty with your names, must be my age.

However lets say that you have a field such as fldTotal:fldA-fldB which works unless either fld is null , then fldTotal:nz(fldA,0)-nz(fldB,0) should work.

Brian

 

[emshim]

Problem with posting my db, apparently its too big to post, even when zipped.

I'll try and sort something and get it posted in a moment.

Thanks for you time, its really appreciated.

Emily

 

[Brianwarnock]

Compact and repair first, if still too big after zipping take a copy and remove any forms, reports not needed maybe some data, compact again and zip.

Brian

 

[emshim]

right, ive had to delete nearly everything! i think i have left what you need, but if i have deleted anything, please just let me know. Thanks very much!

Emily

 

[Brianwarnock]

Unfortunately my Winzip v9 could not open your zip file, perhaps its time I upgraded.

Brian

 

[emshim]

ok, let me try sort that, be right back.

em

 

[emshim]

i have tried to download winzip 9.0 but i cannot find it anywhere anymore! Only 11.1! i guess its not going to be possible!

Thanks anyway for all the help,
Emily

 

[Brianwarnock]

Somebody else might pick it up but in the mean time do you want to post the SQL code of the 3 queries to see if I can spot anything from that.

Brian

 

[emshim]

I have taken the NZ part out again so that it works if not null so the SQL here is without that.

query - qryTotalQuantityAllocated

SELECT qryPartCodesAlreadyAllocated.PartCode, Sum(qryPartCodesAlreadyAllocated.Quantity) AS SumOfQuantity
FROM qryPartCodesAlreadyAllocated

query - qryTotalFree

SELECT (qryCountPartNumbersFreeStock.CountOfPartCode)-(qrytotalquantityallocated.sumofquantity) AS Expr1
FROM qrytotalquantityallocated, qryCountPartNumbersFreeStock;

query - qryCountPartNumber

SELECT Count(qryStockPartNumberAvailable.PartCode) AS CountOfPartCode
FROM qryStockPartNumberAvailable;

query - PartCodesAllocated

SELECT tblAllocation.PartCode, tblAllocation.Quantity

If that makes sense to you, i believe you are a genius!!!

Em
FROM tblAllocation
WHERE (((tblAllocation.PartCode)=Forms!frmAllocate!cmbPartCode));

 

[Brianwarnock]

I'm worried now as you say it works if things aren't null.

query - qryTotalQuantityAllocated

The use of sum in this means that it is a Totals query and thus I expected to see a Groupby on qryPartCodesAlreadyAllocated.PartCode.

Are you summing and Counting by Partcode? If so you also need to include that in the query qryCountPartNumber and then join on this in qryTotalFree,
I suppose if you are not and each query only produces 1 record then a join may not be nescessary but then

query - qryTotalFree

SELECT nz(qryCountPartNumbersFreeStock.CountOfPartCode,0)-nz(qrytotalquantityallocated.sumofquantity,0) AS Expr1
FROM qrytotalquantityallocated, qryCountPartNumbersFreeStock;

should work. I bolded(is that a word) a bit of the query name as that is missing from the query, a copying error I presume.

Hope the above makes sense.

Brian

 

[emshim]

hang on, i really think i have done something whilst copying the SQL statements! let me have another go. The problem is, i have the database on a laptop, which is not connected to the net at the moment. So having to work between computers! At work, dont ask...

 

[emshim]

right here we go, let me try again...

qryTotalQuantityAllocated

SELECT qryPartCodesAlreadyAllocated.PartCode, Sum(qryPartCodesAlreadyAllocated.Quantity) AS SumOfQuantity
FROM qryPartCodesAlreadyAllocated
GROUP BY qryPartCodesAlreadyAllocated.PartCode;

qryTotalFree

SELECT (qryCountPartNumbersFreeStock.CountOfPartCode)-(qrytotalquantityallocated.sumofquantity) AS Expr1
FROM qrytotalquantityallocated, qryCountPartNumbersFreeStock;

qryPartCodesAlreadyAllocated

SELECT tblAllocation.PartCode, tblAllocation.Quantity
FROM tblAllocation
WHERE (((tblAllocation.PartCode)=[Forms]![frmAllocate]![cmbPartCode]));

qryCountPartNumbersFreeStock

SELECT Count(qryStockPartNumberAvailable.PartCode) AS CountOfPartCode
FROM qryStockPartNumberAvailable;


how about that then!? haha i hope thats better, and i think thats correct this time...

Thanks very much for all this brian!!!

Emily

 

[Brianwarnock]

ok

I think you may have me beat especially as I'm running out of time. I suspect that it is not an NZ problem but a problem that a partcode will not appear for both sides of the subtraction, I wish I could have unzipped your db and am sorry if I've wasted your time.

If nobody else answers I woul repost, you can delete this thread then if you wish or indicate a repost.

Brian

 

[emshim]

ok well thanks very much for the help anyway! i will try and sort out the db so i can zip it using winzip 9, and if so, i will contact you. Thanks anyway Brian, you have been a great help!

Emily

 

[Brianwarnock]

These 2 queries suggest that you are only working with 1 partcode

qryPartCodesAlreadyAllocated

SELECT tblAllocation.PartCode, tblAllocation.Quantity
FROM tblAllocation
WHERE (((tblAllocation.PartCode)=[Forms]![frmAllocate]![cmbPartCode]));

qryTotalQuantityAllocated

SELECT qryPartCodesAlreadyAllocated.PartCode, Sum(qryPartCodesAlreadyAllocated.Quantity) AS SumOfQuantity
FROM qryPartCodesAlreadyAllocated
GROUP BY qryPartCodesAlreadyAllocated.PartCode;
-----------------
This query uses a query not listed but assuming that it too is only selecting the same partcode it needs to select and group on that partcode, if this is an incorrect assumption I am stumped.

qryCountPartNumbersFreeStock

SELECT Count(qryStockPartNumberAvailable.PartCode) AS CountOfPartCode
FROM qryStockPartNumberAvailable;

Now the total query needs to ensure it can select an entry for both sides of the subtraction, this is where a join comes in

qryTotalFree

SELECT nz(qryCountPartNumbersFreeStock.CountOfPartCode,0)-nz(qrytotalquantityallocated.sumofquantity,0) AS Expr1
FROM qryPartCodesAlreadyAllocated left join qrytotalquantityallocated on qryPartCodesAlreadyAllocated.tblAllocation.PartCode= qrytotalquantityallocated. qryPartCodesAlreadyAllocated.PartCode left join qryCountPartNumbersFreeStock on qryPartCodesAlreadyAllocated.tblAllocation.PartCode = qryCountPartNumbersFreeStock.notsure of the fieldnameherefor thepartcode;

basically you are forcing an result from both queries based on the part number selected initially, if null you make It 0

I wonder if this is correct, off to watch the match now.

Brian

 

[emshim]

hey i think its working! i stole a few bits of that SQL and i think its working! Will have to try out a few values and see, but i think its good! Thanks very much for the help, and the quick, indepth replies! You have been great! Hope you enjoyed the footy! Off to finish this db for my dad now, got about 4 days until i go back to uni so gotta hurry! thanks again,

Emily

 

[emshim]

i was wrong, its not