DCOUNT not working on report

[dzirkelb]

I have a simple report which pulls data from a query. The query prompts the user to enter in a variable (route number) and it then displays the data; hence, when the report is run, it prompts the user to enter in the variable. This all works correctly; however, my dcount function doesn't work...it produces #ERROR. But, if I go to the query and replace the [Enter Route] so it doesn't prompt the user to enter in the variable, and replace it with a known route, then the dcount works fine...here is my dcount code:

=DCount("[ptype]","qryExpenseLog","[PTYPE]='T'")

It is erroring out, I am assuming, because the query isn't saved with a value in the rt1 field (the field which prompts the user to enter in the variable). I looked a little at the macro SetValue to see if I can do something there, but am pretty lost...any ideas on how to get this to work?

Main objective:

User opens report, it prompts user to enter in route number, report opens with value for variable entered and count functions work for what user entered in.

Thanks!

 

[mdrzewie]

I'm having the same problem...did you find a solution

I am having the same problem. I think it is because the DCount function is running against the query before the column is populated using the input parameter. This is why it works when the route is static and not when prompting for the route. Did you find a solution?
Mike

 

[usermj]

Could you create a form-based interface for users to enter the value?

Then you can open the report by using filter condition as:

DoCmd.OpenReport "WkShopReport", acViewPreview, , DateFilterStrirng.

Maybe in this way, DCount () can work properly

 

[RV]

Put the query field you refer to as a hidden field on your report.
Refer in your DCount to the hidden field.

RV