Using Functions in reports

[highbulp]

Okay, total newbie to Access here.

I've set up my database rather, now I'm trying to design rather specific reports. The report will only include data filtered by a form/query.

I'd like to be able to do something like
DCount("x",<THIS REPORT>,"x=1")
but I don't know how to reference only the data filted by the query! I've tried
DCount("x","QueryName","x=1")
but it gives me an #ERROR. This is all sitting in the Report Header

Is there a way to reference the report's source in a function? Is there some other way I should be filtering data in the report?

 

[boblarson]

Create a query as the recordsource for the report and in the query you can set the criteria to the form's controls that are controlling what you want to see. Just remember the form has to stay open for this to work.

 

[highbulp]

Create a query as the recordsource for the report and in the query you can set the criteria to the form's controls that are controlling what you want to see. Just remember the form has to stay open for this to work.

I'm sorry, maybe I wasn't clear. I already have the recordsource as a query that is tied to the form's controls. That's not a problem. The problem is, how do I reference the recordsource in a DCount call?

EDIT:

Okay, I think the problem is that my call
DCount("fieldname","QueryName","fieldname=1")
is having issues resolving the fact that QueryName is, in fact, a query which returns a table. This may have to do with that QueryName is built to work off a form, so it has parameters like Forms!FormName!parameter. Usually Access has no problem with this query if I don't call it from the form, as it just prompts me to fill in the parameters. This is the case when I set QueryName as the ReportSource. However, Access does not prompt me when I reference QueryName in the DCount(), and so I wonder if I've really confused it.

How do I get this to work?

 

[Rich]

Use the Where clause of the OpenReport method, but then why are you using DCount?

 

[highbulp]

Use the Where clause of the OpenReport method, but then why are you using DCount?

I want a report that displays lots of different pieces of information from a rather large database. One of these pieces of information is a Count of the number of records which have a certain value in a certain field. I'm trying to get that number to display in the report, but I only want it to count records that match the criteria established by this form/query set. Does that make sense?

 

[tjcinnamon]

Did you ever find a solution to this?

I am having a similar issue where I want to check the number of specific audits done in a month over a quarterly report.

Using D-Count I can conditionally count the total number of single unit audits (SFR - Single Family Rentals and OOC - Owner Occupied Rentals) for the quarter. However if broken down by month the totals will not calculate because D-Count is taking the total number of SFR and OOC in the query which is the quarter not the month.

Is there a way I can count audits per month in a report based on a query for the quarter.

 

[highbulp]

Did you ever find a solution to this?

I am having a similar issue where I want to check the number of specific audits done in a month over a quarterly report.

Using D-Count I can conditionally count the total number of single unit audits (SFR - Single Family Rentals and OOC - Owner Occupied Rentals) for the quarter. However if broken down by month the totals will not calculate because D-Count is taking the total number of SFR and OOC in the query which is the quarter not the month.

Is there a way I can count audits per month in a report based on a query for the quarter.

My solution was finally to make a temporary table and then count that. I had a form run a couple of queries to filter my main data table and populate a smaller, "report table." Since this report only had the records that I was interested in, I could easily take counts and other functions of those records. I'm not sure how helpful that will be to you.