Using Dcount with Query. Counts source table instead

[smercer]

Hi all

I am trying to count the number of records a query is showing using VB & Dcount but the problem is it will count the source table and not the query. (Yes I have put the right criteria in as the query shows the right records at the right time.)

here is the code:

Forms!frm_Book_Sales!sfrm_Sales_Book_Description_Current.Requery
Miscellaneous_Count = DCount("Customer_ID", "qry_Sales_Miscellaneous_Current", "")
Each_Book_Count = DCount("Customer_No", "qry_Sales_Each_Book_Current", "")

Also tried:

Forms!frm_Book_Sales!sfrm_Sales_Book_Description_Current.Requery
Miscellaneous_Count = DCount("*", "qry_Sales_Miscellaneous_Current", "")
Each_Book_Count = DCount("*", "qry_Sales_Each_Book_Current", "")

And

Miscellaneous_Count = DCount("Customer_ID", "qry_Sales_Miscellaneous_Current", "[Customer_ID] = Cust_ID")
Each_Book_Count = DCount("Customer_No", "qry_Sales_Each_Book_Current", "[Customer_No]= Cust_ID")

Cust_ID is a global variable, and when I put that in I get an error (See attachment)

This is for a set of buttons for when the customer is half way though a sale the customer wants to have another look around the user can click on number 2 button to serve other customers while waiting for previous customer. The program will then disable the "Sell books", "Return Books to stock" and "cancel sale" button because there are no books to sell for next customer yet.

When the user scans in books, the append query will use a number to identify which books go with whom and temporary store it in a field. This is the number I am trying to count.

Does anyone have any ideas? Thanks in advance

 

[DB7]

Hi Merecer,
with all due respect, I'm surprised it's working at all, (as you said, it returns ALL records from the source table?), because, your syntax is incorrect in all cases. (not to dwell, I'm just very curious).

This should work...

Miscellaneous_Count = DCount("Customer_ID", "qry_Sales_Miscellaneous_Current", "[Customer_ID] =" & Cust_ID)

The where statements in all aggregate functions, DMax, DSum, DAvg, are all SQL statements, without the "WHERE" .

So if Cust_ID was a string...

Miscellaneous_Count = DCount("Customer_ID", "qry_Sales_Miscellaneous_Current", "[Customer_ID] = '" & Cust_ID & "'")

...as you would in a filter string, or any other, WHERE string.

But, since the query is already filtered (if I understood correctly), you don't need a third argument, so...

Miscellaneous_Count = DCount("Customer_ID", "qry_Sales_Miscellaneous_Current" )

No need for "" or a comma.

but again, how were ALL records returning?

..I may have missed something?

Hope this helps, good luck!

 

[smercer]

Thanks DB7,

it was the Miscellaneous_Count = DCount("Customer_ID", "qry_Sales_Miscellaneous_Current", "[Customer_ID] =" & Cust_ID) that worked.

Tryed the one without comma etc, but that was same as before.

I am still learning which explains my mistakes.

Thanks again!!!