Criteria in a DCount function

[daniel_in_korea]

Hello,
I have some problems with a statement of a DCount function. My statement:

=DCount("[Date]","Query1","[Date] Between [Parameter Date: beginning] And [Parameter Date: End]")

Whereas 'Query1' is the basic for the report. Query1 depends on two parameters: [Parameter Date: beginning] and [Parameter Date: End]. They are to enter on opening the report. The statement is written in the 'Control Source' property. But it does not work. #Error is the result

Thank you for your help.

 

[Mile-O]

http://www.access-programmers.co.uk/forums/showthread.php?p=395763#post395763

 

[daniel_in_korea]

Hello,
I changed the syntax to:

=DCount("[Date]","Query1","[Date] Between [Query]![Query1]![Parameter Date: beginning] And [Query]![Query1]![Parameter Date: End]")

But it does not work...

 

[Mile-O]

=DCount("[Date]","Query1","[Date] Between [Query]![Query1]![Parameter Date: beginning] And [Query]![Query1]![Parameter Date: End]")

You can't refer to [Query]![Query!]..... because the QueryDefs collection does not work in this way. It only works with the forms collection.

As a form only displays one record at a time then you can refer to a specific value found in one of its controls. You can't do what you just tried as the Query's column can have thousands of different records associated with it; how would it be decided which value to use? The parameter is no good as it can still return thousands of records.

You need to refer to the form for your criteria (you are using forms, aren't you?). If not then I'd advise using forms since they are method of interacting with the data. You also need to consider date delimiters (the # sign) to signify dates, and the fact that you need to create a criteria string.

=DCount("[Date]","Query1","[Date] Between #" & [Forms]![FormName]![txtStartDate] & "# And #" & [Forms]![FormName]![txtEndDate] & "#")

 

[Mile-O]

Also, Date is a Reserved Word in Access and should not be used as a field name.

 

[Rich]

All you need is =Count(*) in an unbound textbox in the Report footer

 

[daniel_in_korea]

Thanks for your advice. It works now.