Problem using DCount in Access 2003

Question

I am trying to use DCount to count the number of results in a particular query that have a specific value. I have looked carefully at the syntax of examples online and cannot find the problem. Running the report simply returns #error.

I have a query "Enquiries between dates" which returns all the enquiries between a particular set of enquiry dates.

I have a report linked to this query.

In the footer of the report I use

=Count(*)

to return the total number of results in the query. That works fine.

I would also like to display the number of results from the query where the field called "how_hear_about_us?" has value "Friend".

From what I can see, the following syntax should be correct:

=(DCount("[how_hear_about_us?]","Enquiries between dates","[how_hear_about_us?] = 'Friend'"))

I've also tried:

=(DCount("[how_hear_about_us?]","Enquiries between dates","[how_hear_about_us?] Like 'Friend'"))

Any suggestions for what I might be missing? Many thanks in advance.

Answer 1

Judging by its name my first guess would be that  that the Enquiries between dates query is expecting parameters.  This should be OK if they are references to controls on an open form, but not if the form is closed or they are simple system generated parameters.

Another possible cause:  Was the data type of he [how_hear_about_us?] column defined when designing the table by using the lookup field wizard by any chance?  If so, despite appearances to the contrary its value will be a hidden number, not the text you see.  If this is the case to use the text value in the criterion for the DCount function it would be necessary to join the table to the referenced (lookup) table containing the text column, and return this column in the reports query rather than the foreign key column.

The use of the lookup field wizard is generally deprecated by experienced developers.  For the reasons why see:

http://www.mvps.org/access/lookupfields.htm

If neither of these is the cause of the problem the only other thing which occurs to me is that the word 'between' is used in the query name.  As Access includes a BETWEEN....AND operator, it may be that this is causing confusion, so wrap the query name in square brackets:

=(DCount("[how_hear_about_us?]","[Enquiries between dates]","[how_hear_about_us?] = 'Friend'"))

However, once you have ensured that the value of the control in the report is the text value not a hidden numerical value, you can conditionally count the instances of the Friend value by an expression in an unbound text box's ControlSource in the report footer, avoiding the function call:

=Sum(IIF([how_hear_about_us?]="Friend",1,0))

The IIF function will return a 1 if the value is Friend, zero otherwise.  Summing the ones is the same as counting the  instances of the Friend value.

Answer 2

Thank you very much to Ken and Scott for your replies and advice - that's really helpful and much appreciated. I'm afraid I haven't had the opportunity to action the suggestions made so far, as I've only just returned from being away, but will certainly look into your ideas as soon as possible. Thank you again.

Answer 3

This is a guess, but an educated one. Did you set up the how_hear_about_us? field as a lookup field? You need to check the datatype of that field and see exactly what is being stored in it. If the problem is a lookup field, it is probably not storing the text value, but some code from the lookup table. 

This is one of the reasons using lookup fields on the table level is not recommended, because it masks what is actually stored.

On a separate note, I wouldn't use special characters like a ? in field names. These can cause problems. I would have named that field Referral and set the Caption property to How did you hear about us?. This will cause the Caption to be used on forms and reports, but an easier to use fieldname.