Using Dcount in an expression

[RickK]

I am trying to use DCount in a query as an expression to return a value of a count in a table.

My table name is SerialTest and the field name is ModelID. My table has three fields, SerialID, Serial Number and ModelID. The SerialID is the primary key , the Serial Number is a field that the user enters with a new serial number and the ModelID field is a list of three different numbers that the user would select depending on what type of machine it is. I need to know how many total entrees there are for model ID's using 1 and 2. For example,

Serial ID Serial Number ModelID
1 1234 1
2 8765 1
3 7623 2
4 0973 3
5 2323 1

In this example it would return 4 as the result because there are 3 number 1's and 1 number 2

I am using

Expr1: DCount("ModelID","SerialTest","ModelID = '1, 2'")

This is throwing an error. What am I doing wrong or what is another way to count the entrees from a query?

Thanks
Rick

 

[pbaldy]

Rarely is using a domain aggregate function in a query a good idea, but the correct syntax would be:

DCount("ModelID","SerialTest","ModelID = 1 OR ModelID = 2")

Depending on your need, a totals query would probably be a better solution.

 

[RickK]

Rarely is using a domain aggregate function in a query a good idea, but the correct syntax would be:

DCount("ModelID","SerialTest","ModelID = 1 OR ModelID = 2")

Depending on your need, a totals query would probably be a better solution.

That worked exactly like I needed it to. Why is it not a good idea to use it that way?

Thanks so much

Rick

 

[pbaldy]

Happy to help. That's like a separate query, and it will run for every record returned by the main query. Using them like that can be very inefficient, and there's usually a way around it with joins or separate queries.