Count Function

[Brian P]

I have generated a number of queries successfully. However, I would in my subsequent reports like to get a count of each field which has data in it (that is, "is not null").

But I am confused by the Access Help instructions regarding the "Count Function". I tried to use the Expression Builder, but keep getting a syntax error message. Is there a simple way to do a count function for each of the fields in the query or report?

Thanks,
Brian P

[sanniep]

Hey Brian,

Do you mean you want 1 report to display fields with a number of records from different queries? If so use the DCount function in your report's textboxes. Function: DCount("[Employeenumber]";"[Qry_Employees]";"[Employee]='John Doe'"). (Mind the placement of single/double quotations). You can also use DSum, DMax, etc.

Reply if I don't get it.. Grtz, Sander

[Brian P]

Sander,

Not exactly. Suppose I have 5 Fields (Name, Green, Yellow, Blue, Red) and 20 Records. What I want my report to do is provide a count of how many records are in each field (not null). So that at the bottom of the report it would total how many records contain an entry in that field. Like so....

Name Green Yellow Blue Red
20 11 7 4 12

I would have thought this to be simple, even having a preprogrammed text box for it.

How would you do this?

Brian

[sanniep]

Well, it should be simple! There are a couple of ways:
1. Make a query (in Access interface), add the table and the fields to count. Use the totals button in your taskbar and set al your Totals to Count. The query result should give you the desired result. Take your report and add a subreport at the bottom based on the query we've just made. Customize the layout and it should be what you want.
2. Make 5 textboxes in your reportand fill them with =Count([Name]), = Count([Green]), etc.

What do you think? Sander

[Brian P]

Sander,

After I have entered the info, I keep getting a box asking to "Enter parameter value". What parameter value? I don't understand.

Brian