MS Access DCount to show calculated field in a report

[fwild3]

My parameter query uses the DateDiff function in a calculated field to determine the number of days between open and close dates. Works fine. I want a related report to count records where DateDiff <46 and show the count.

The expression is:
=DCount("[NewNo]","selected dates","[Process] < '46'")
where "selected dates" is the query and "Process" is the calculated field. Access 2003 accepts the syntax but returns "#Error". I tried several variations such as "[Process] = '<46'") w/o effect. Any suggestions appreciated. Thank you.
Fred

 

[dhookom]

Assuming [selected dates] isn't a parameter prompt query:

If Process is numeric, remove the single quotes:
=DCount("[NewNo]","selected dates","[Process] < 46")




Duane
Hook'D on Access
MS Access MVP

 

[fwild3]

Calculated field "Process" IS numeric, a number of days. Query [selected dates] DOES prompt for a relevant period using criteria, "Between [Enter start date] And [Enter stop date]" in the CloseDate field.
Back in the report, I changed the expression to =DCount("[NewNo]","selected dates","[Process] < 46") but with same result. It still returns the #Error. Thank you for your help in this.
Fred

 

[dhookom]

You can't expect to use DCount() or other domain aggregate functions with a parameter prompt query. IMHO, you shouldn't even be using parameter prompts faq701-6763.

Do you want to count records from within your report's recordset? If so, don't use DCount(). You might be able to use a control source of:

   =Sum(Abs([Process]<46))

Duane
Hook'D on Access
MS Access MVP

 

[fwild3]

IMHO nothing. You did it with the =Sum(Abs([Process]<46)). Many, many thanks. Access never fails to frustrate me, but it's an indespensable tool.
Fred