DCount function with parameter query ?

thesiz
thesiz used Ask the Experts™
on
I have a select query that uses a parameter query in the criteria pane of  the date field. This query is linked to a report that uses a DCount function in a control on the report. When you run the report the DCount function produces an #ERROR result. If I change the criteria in the select query by removing the paramater query and manually entering a date in the criteria pane of the query, the DCount function in the report gives the proper result. Is there a conflict between domain aggregate functions and parameter queries?
I realize I can accomplish the same thing by creating a form with an unbound control for the user to enter the desired date and place a command button on the form to run the report. But I'm puzzled as to why the parameter query produces the #ERROR message. Anyone know?
Thanks !
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
My only thought would be something to do with data types.  What is the  exact Dcount function you're using?  When you place in the parameter what data type are you declaring?  This may give some clues.


Author

Commented:
I've set the parameter data type to Date/Time so that's not the issue. The function reads as follows =DCount("*","qrytesting","Midd='Middle'"). As I said it works fine when you enter a date directly into the criteria pane of the date field in "qrytesting". However if you try to use a parameter query such as [Enter Date] the DCount function will not compute.
Commented:

I tried to recreated your situation.  I ran into the same thing.  Not sure why the data type Date doesn't "stick" for the paramter but I was able to get it working when I used the following.

DCount("*","qrytesting "," Midd = #" & [middle] & "#")

I hope this is what you're trying?


Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
That will work. I'm just curious if the parameter query conflict is a "feature" or a glitch ? Thanks for the time Honeyman.

Commented:

No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:
 - Answered by: Honeyman
Please leave any comments here within the
next seven days.

PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER !

Nic;o)

Commented:
Finalized as proposed

modulo

Community Support Moderator
Experts Exchange

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial