Count Function in ReportForm - Microsoft Access Forum


	Back to Access Forum Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

Count Function in ReportForm

Joshua Jonas

4 years ago

I need some guidance with the Count function in reports and forms. I have several queries that are looking at tables and finding records where certain conditions have been met. I have a report that I would like to have 3 text boxes on that simply reflect the total number of records found in each query. The name of the first query is qryNumPacksServiced.
I'm probably getting the format wrong, but when I make the control source of the text box =Count(qryNumPacksServiced) I get the #Error in the box. I know I can make the Record Source of the Report equal to the query name and then put =Count(*) in the text box and that works, but then I cannot get the values of the other two queries in the other boxes. I'm sure this is a simple fix (probably my syntax), but I've tried several different iterations and can't get any of them to function correctly. Any ideas? Thanks in advance.

	Adam Schwanz @Reply 4 years ago
DCount is probably easier just to get a count of records in a query

Joshua Jonas OP @Reply

4 years ago

I actually tried using DCount when I couldn't get Count to work, although I was trying to pull the data directly from the tables without using a query. I need to pull information from more than one field using the AND operator and I couldn't get it to use it correctly. Again, it's probably an issue with my syntax with all of the double and single quotes. I was able to get the queries working correctly, so I was headed in that direction. I'm not committed to Count over DCount, just whichever method gets me the numbers in the boxes. How do I use DCount to simply count the records in a query?

	Alex Hedley @Reply 4 years ago
So your Aggregate query was working?

	Adam Schwanz @Reply 4 years ago
Same way you get it from a table, just put the query name where the table name goes `=DCOUNT("ID","QueryName")` or with Criteria `=DCOUNT("ID","QueryName","Criteria=" & Criteria)`

Joshua Jonas OP @Reply

4 years ago

Alex, yes the query was working fine, but it's not performing any Aggregate functions. I mean, I don't want it to, it's just looking for criteria to be met in three different fields and then I wanted a text box to show how many records it found with that criteria.

Adam, ok, I'll give that a shot. I think my hangup in using DCount with the table was with the AND operator and the syntax. I have two fields in a maintenance table labeled MaintenanceDate and MaintenanceType. I want to count records that have a MaintenanceType of 'PM' AND a MaintenanceDate of the current year, which I think is Year(Now()). I should be using AND and not &, correct?

	Adam Schwanz @Reply 4 years ago
You can either set the criteria in the query itself (easier), get it to show the records you want counted and then you won't need to use criteria in the dcount. OR you can do it yourself but it would be like `=DCOUNT("ID","QueryName","MaintenanceType=""PM"" and Year(MaintenanceDate)=Year(Date())")`

	Joshua Jonas OP @Reply 4 years ago
Ok, I like the first option better. :) So, just to verify...build my query (which is already working, and its name is qryNumPacksServiced) and then just use DCount to count the query. So the syntax would be =DCount("ID", "qryNumPacksServiced") Does that sound right?

	Adam Schwanz @Reply 4 years ago
yes, if that query is showing everything you want to count, then you shouldn't need to add any criteria in it. As long as your autonumber/ID field is called ID, that will work, otherwise replace ID with whatever your field is called. It can really be any field in the query in this case.

	Joshua Jonas OP @Reply 4 years ago
Awesome, I'll give it a whirl. Thanks so much!!

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Forum.

Next Unseen

New Feature: Comment Live View