DCount Additional Criteria

[steve21nj]

How would I add additional criteria to the below.

My criteria would be [myfield] = "Complete"

Is this even possible?

ThisYear: DCount("*","Open Issues","year([RequestDate]) = " & Year(DateAdd("y",-1,Date())))

 

[Galaxiom]

Include AND or OR in the statement just as you would in an SQL Where clause.

BTW. Testing for the Year is not an efficient query because every record must have the Year function applied before deciding which records to return. The query will get slower and slower as the number of records increases.

The test should be of the date field directly against a date range generated from the current date. This will allow the engine to return records based on the date field's index without applying a function to every record.

 

[steve21nj]

To answer the Where Clause, would it look like this?

CompThisYear: DCount("*","Open Issues","year([RequestDate]) = " & Year(DateAdd("y",-1,Date())) And [MyField]='COMPLETE')

The second part, which makes sense but I'm not exactly sure how to build that.

 

[Galaxiom]

As it stands it wuld look like this.

DCount("*","Open Issues","Year([RequestDate]) = " & Year(DateAdd("y",-1,Date())) & " And [MyField]='COMPLETE'")

However you are apparently using the DCount in a query. This will be even slower because each DCount is like running a query.

Investigate how to do the entire job in an aggregate query.