Best Way to add a count query? 2

[niteraven]

Hello All

I have a form that allows the user to receive parts by part number. The sales order number is the primary key. So when the user open the form, continous form, all of the sales orders that are waiting on that part number. I made a query to count the total number of parts that are missing for the sales order. It works, but when i try to incorporate the query in the form it gives a #name error. I used the query as the controlsource for a text box.

The idea is to allow the user to know the total number of parts that are needed to complete the staging of an order, so if there are a total of 3 other parts besides the part you are receiving, you can deside what sales order gets the part.

Here is the query:

SELECT Count(tblstageparts.product) AS CountOfproduct
FROM tblstageparts
GROUP BY tblstageparts.sonum
HAVING (((tblstageparts.sonum)=[forms]![frmpurchasing]![sonum]));

SHould I be using a subform for this? I am not sure. Any suggestions will be greatly appreciated!

thanks in advance
raven

 

[Remou]

Use Dcount instead.

 

[niteraven]

Hi Remou,

Thank you. I have looked up the syntax, and got a dcount statement to work. But i need to only count the records that have not been received.

=DCount("[sonum] +  [receivedpart]","tblstageparts","[sonum] = forms!frmpurchasing!sonum")

I have tried this also:

=DCount("[sonum]","tblstageparts","[sonum] = forms!frmpurchasing!sonum" and "[receivedpart] = false")

ANy suggestions?
THanks
Raven

 

[Remou]

Received part is a YesNo field, is that right? Try:

=DCount("(*)","tblstageparts","[sonum] = " & forms!frmpurchasing!sonum & " and [receivedpart] = false")

 

[niteraven]

Thanks Remou,

I get an #Error message

Raven

 

[Remou]

Oops:

DCount("*","tblstageparts","[sonum] = " & forms!frmpurchasing!sonum & " and [receivedpart] = false")

 

[PHV]

=DCount("[sonum]","tblstageparts","[sonum] = forms!frmpurchasing!sonum and [receivedpart] = false")

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Remou]

PHV
Both versions work, and I can see the advantage in putting the form inside the quotes, but why change the asterisk (*) ? As I understand it, it is safer, in that it will ensure that everything is counted, rather than trying to find which field is key and therefore guaranteed to be filled. In this case the Where statement is going to limit the count.

 

[niteraven]

THank you Remou and PHV!

It works.

Thanks
Raven

 

[PHV]

Remou, I've just copied/pasted/edited the 2nd try of the OP timestamped 17 Jul 08 17:06.

 

[Remou]

PHV: Thanks for the reply.