Query to Count Records (1 Viewer)

[Mario_Party]

I have been searching everywhere but I can't find queries, or don't understand the ones I find as they are to complex, that will count the number of records in my table, which will also have some criteria to match.

My table has two fields in it, one is called "Inactivity" and the other is called "InactivityWarned".

"Inactivity" is a number.
"InactivityWarned" is Yes/No.

I need a query that will run in VBA (ie DoCmd.RunSQL.......) to allow me to find the number of records that have "Inactivity" as greater than or equal to 90 (>=90) and "InactivityWarned" as "No".

I then want to be able to put the number of rows returned into a variable in visual basic. However, if this isn't possible just knowing whether results are returned or not is enough for me, ie 0 for no results and 1 for more than 0.

Many thanks!

 

[ghudson]

You need to use the DCount() function. I am surprised that your search did not lead you to it.

 

[x0reset]

RunSQL only works for transaction queries, your trying to run a Select query. Instead try using an aggregate function like DCount.

 

[Mario_Party]

Excellent - thanks!

 

[Colt45]

wow! 4 years later and it still works....