DAO and SQL COUNT (*)

  • Thread starter Thread starter dave h
  • Start date Start date
D

dave h

Hi,

Before I allow a row to be added to the db, I need to check to see if a row
already exists that meets certain WHERE clause criteria. The user will
have clicked a button to make the addition and I'll be adding the row via my
own code. But I need to check first before I actually execute that code.
So, how should this code look? I think the SQL should be:

Select COUNT(*) as empTotal from Employees

Using DAO how do I run that sql and pick up the resulting value that
represents the total number of rows in the Employee table. Actually, I need
a WHERE clause, but if this works there should be no problem adding the
WHERE clause.

Thanks
 
Simplest:
DCount("*", "Employees", "[Somefield] = 99")

If you only need to know if there are any, it might be faster to use
DLookup() to find the first match rather than count them all:
If Not IsNull(DLookup("EmployeID", "Employees", "[Somefield] = 99"))
Then
 
Hi Allen,

Thanks very much - this works great. I was not aware of these functions.
Access certainly has a few tricks up it's sleeve.

Much appreciated, Dave H


Allen Browne said:
Simplest:
DCount("*", "Employees", "[Somefield] = 99")

If you only need to know if there are any, it might be faster to use
DLookup() to find the first match rather than count them all:
If Not IsNull(DLookup("EmployeID", "Employees", "[Somefield] = 99"))
Then

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

dave h said:
Before I allow a row to be added to the db, I need to check to see if a
row
already exists that meets certain WHERE clause criteria. The user will
have clicked a button to make the addition and I'll be adding the row via
my
own code. But I need to check first before I actually execute that code.
So, how should this code look? I think the SQL should be:

Select COUNT(*) as empTotal from Employees

Using DAO how do I run that sql and pick up the resulting value that
represents the total number of rows in the Employee table. Actually, I
need
a WHERE clause, but if this works there should be no problem adding the
WHERE clause.

Thanks
Click to expand...
Click to expand...
 
Back
Top