DCount in report

[MikeJD]

I am trying to use the DCOUNT function in a report. I want to display the number of contracts selected by a query, Query 3, which have a value for the field 'Pre-tender' of 4.
I have entered the Control Source for a textbox as follows :
=DCOUNT("[Contract Name]","Query 3","[Pre-tender]='4'").
This just displays '#Error'. Can someone help with the correct syntax ?

 

[MikeJD]

Thanks Pat.
I have tried this and it still doesn't work. I have checked the field references again and they appear to be correct.

 

[simongallop]

The only thing that I can think of is that the query is retruning some dud data. Look at what query 3 is outputting and make sure that it is OK. Otherwise your formula is fine. Have just tested it on a table with settings being Text and Numeric.

One last stab in the dark, if query 3 is carrying out compex formulas or even just grouping then sometimes DCount etc won't work. If this is the case then create a new query which just takes the results from Query 3 and use that for your formula to look at.

HTH

 

[MikeJD]

Thanks Harry,

I've tried creating a new query as you suggest which just selects the results of Query 3 and the control still doesnt work.

Query 3 is not complex, it selects records between two dates which have results of a survey returned. I then want to be able to count how many records achieved a particular score for a field.The output from Query 3 looks correct.

 

[simongallop]

In which case the only other way is to group the query results. Using the query that shows ALL from query 3, select totals (View / Totals), group by survey and count on another field.

HTH

 

[MikeJD]

Problem solved !

I realised that the reason for the control not working was that the query it was referring to had stopped running. Opening the report runs the query but the control appears in the report footer.
I changed the query to a make-table query then based the report and control on the new table.

Thanks for your help.