Dcount

[ScottXe]

I have an unbound text box on the footer of a form in which the control source has =DCount("[Supplier]","recordset"). It did not work and just produced #Error. I just want to count the distinct suppliers in the form. Can someone advise where is possible error. Thanks!

 

[jdraw]

You're going to have to supply more info.
What is the recordset involved?

 

[plog]

Dcount counts the number of non-null values in a specific field of a specific data source (query or table). Your DCount is looking at the table/query named "recordset" and then at the field called "Supplier" to determine the number of non-null values it has. You have no table/query called "recordset" so your Dcount is producing an error.

So you need to make your DCount look for a valid field in a valid table/query that you can use to determine this value.

 

[ScottXe]

Hi Plog,

Thanks for your advice. The query, qrySKU-search has a parameter field to do the required search. I changed the code to =DCount("[Supplier]","qrySKU-search"). It produced a flashing #Error in the text box. Since I do not need any criteria, I leave it blank. Any idea to fix it?

 

[plog]

I think its that dash in your query name. You might need brackets around it:

=DCount("[Supplier]","[qrySKU-search]")

 

[ScottXe]

Hi Plog,

There was no luck with the modification, still flashing #Error.

 

[Minty]

The query, qrySKU-search has a parameter field to do the required search.

I don't think you can use a parameter based query in a dlookup, where is it going to get the parameter value from?
Create a copy of the query without the parameter and try using that as your recordset.

 

[ScottXe]

Can you clarify that DCount cannot be used in form based on parameter query? If I move keyword search to header of the search form, is it ok to use DCount in footer? I need to DCount the suppliers in a recordset based on certain filter criteria. Thanks!

 

[plog]

If the query you are looking into with DCount requires a user-supplied parameter, it is not going to work no matter where that DCount is. You need to use your Dcount into a regular query.

If you need to use criteria, you supply it as the last argument of the DCount->
http://www.techonthenet.com/access/functions/domain/dcount.php

 

[Minty]

I use count() on parameter based qry forms frequently, I'm not sure about DCount as I haven't got good example I can easily play with.

Where does the parameter for your query come from?

If your form is filtered via a parameter based query just count the supplier records on the form?

 

[smig]

Try to put the parameter as the filter part of the dcount() and not as part of the query.

 

[ScottXe]

I use count() on parameter based qry forms frequently, I'm not sure about DCount as I haven't got good example I can easily play with.

Where does the parameter for your query come from?

If your form is filtered via a parameter based query just count the supplier records on the form?

I use count() for other calculations on the footer of the form. For supplier field, there are duplicate suppliers in the supplier column and I need to count the distinct suppliers.

 

[ScottXe]

Try to put the parameter as the filter part of the dcount() and not as part of the query.

Let me try this alternative to see if it fits to the existing scenario. Thanks!

 

[Minty]

I don't think DCount will give you the result you want even if it did work.
Can you not create the supplier count in another query and look that up using the supplier in the current record as the criteria?

 

[ScottXe]

If the query you are looking into with DCount requires a user-supplied parameter, it is not going to work no matter where that DCount is. You need to use your Dcount into a regular query.

If you need to use criteria, you supply it as the last argument of the DCount->
http://www.techonthenet.com/access/functions/domain/dcount.php

Actually, the primary function of the form is for searching the records by entering some key words. On the footer, I have some statistics of the searched result. Count() and Sum() are working correctly. Now it is no luck with DCount. Is it an incorrect setting for the required results? If so, what is appropriate approach?

 

[Minty]

A Dcount is a Aggregate or Distinct Count - that is it requires something specific to count.

You are asking it to count up a number of Suppliers but without any grouping criteria it doesn't know which suppliers to count.

 

[ScottXe]

A Dcount is a Aggregate or Distinct Count - that is it requires something specific to count.

You are asking it to count up a number of Suppliers but without any grouping criteria it doesn't know which suppliers to count.

Are distinct values not considered a grouping?

 

[Minty]

Are distinct values not considered a grouping?

Actually it is a Domain Count (my bad) , however in your case which supplier is it distinctly counting? Without any criteria you are asking it to simply count all suppliers...