dcount in report

[wezel99]

Hi, I've been reading about dcount for a while now and I cant seem to find a solution to my issues.

I have a report ,rptdeliveryreport, that uses a query ,qrydeliveryreport, to filter on user input dates.

I have duplicate invoiceno numbers because there are multiple line items on one invoice.

I want to count the unique Invoice# to get a total.

=DCount("VisionProNo","qtyDeliveryReport",)

I put that in a control source for a text box on the Report but it returns #error

If I try to use the table rather then a query it works fine. Why doesn't it work with the query?

 

[MarkK]

Is it spelling? You mention a qryDeliveryReport, but your DCount() references a qtyDeliveryReport. Do you see the difference in spelling?

 

[wezel99]

I just double checked. I do have the spelling correct.

 

[plog]

Two things that I don't know that matter, but might:

Put brackets around your field
Get rid of that last comma

 

[wezel99]

I tried most of the variants for bracket and quotes as I was not sure either.

=DCount("[VisionProNo]","qryDeliveryReport")

#error

 

[wezel99]

So just for kicks I removed the parameter input from the query and tried and everything works fine.

Am I attempting the impossible by generating a report based on a query that requires the user to enter a parameter and trying to count the unique values?

 

[plog]

Yes. The only thing that knows the value the user enters for that parameter is that instance of the query. The Dcount isn't part of that instance so it can't use the same data, it needs to run the whole query again. You need to use a Dcount that looks at the underlying data source of that query.

 

[wezel99]

Im not sure I know what you mean...

 

[plog]

That query isn't a fixed entity, it relies on user input-- every time it is run. When you have a query based on it that query runs once. When you have a Dcount on that report the query runs twice--once for the report and once for the Dcount, but its only getting user value once (for the report). So you need to reconfigure how that report runs.

My advice would be to build a form which accepts the input instead of a parameter query. That way the query can look at the form and find the value. So, when the query runs for the report it gets the value from the form. Then when it runs again for the Dcount it looks again at the value from the form.

 

[Galaxiom]

No need for a DCount when you already have the data you are counting in the RecordSource of the Report.

Create a textbox in the footer of the report with the following ControlSource:

=Count([VisionProNo])