DCOUNT

[chrisl]

Hi,

I am trying to generate a report that uses the Dcount function. The report works great until I use a dialog box for the user to enter a value into the criteria.

Any suggestions would be greatly appreciated.

Happy Holidays.

 

[glynch]

You can use use values from a form as criteria for a query that feeds a report or directly on a report to filter output. If you gave more of a description of what you are trying to do it would help.

BTW: The aggregate functions are handy when in a pinch, but should be used very sparingly. They can really slow down queries and reports, there is usually a better approach that is less resource intensive.

 

[chrisl]

Hello.

The query joins about 5 tables together with two conditions (one being provided by the user).

One of the fields contains about 4 different values, let's say A, B, C, or D.

What needs to happen is:

A total of the records with an A or B or C (within the other two criteria).

The next piece is a percentage of how many records are A or B in the report:

A + B / A + B + C

That percentage becomes part of the heading of the report.

Thank you for reply.

I'm sure you can tell I am new at this. Any help is greatly appreciated.

 

[glynch]

I would have the user enter their criterion on a form and then within one query do this:

SELECT Type, COUNT OF (DataFieldABC)
FROM tableXYZ
WHERE (insert the two criteria)
GROUP BY Type (the A,B,C,D field)

Use that query within the main query that feeds the report, link on Type

I don't know everthing you need on your reports but this will get the totals you need onto the report. I would then put a calculated field onto the report to get the percentages.

There are many ways to approach these types of problems, I think the most efficient approach is to get as much of the work as you can into queries. Once they are created and compiled they produce data much faster than DCount and DLookUp functions.