sql Select Count result to variable ?

[Karl Irvin]

In VBA, how do you see the results of an sql count statement like the
following ?

Select Count (*) as Total from tblCustomer
Result = DoCmd.RunSql("S elect Count (*) as Total from tblCustomer")
gives an error

I need to get the count into a variable.

[ml_sauls]

<air code>

Dim rst As Recordset, x as Long

Set rst = CurrentDb.OpenR ecordset("SELEC T Count(*) AS Total FROM
tblCustomer;")
x = rst!Total
rst.Close
Set rst = nothing

If no records exist, x = 0; otherwise x = number of records in
tblCustomer.

[Lyle Fairfield]

Karl Irvin wrote:

In VBA, how do you see the results of an sql count statement like the
following ?

Select Count (*) as Total from tblCustomer
Result = DoCmd.RunSql("S elect Count (*) as Total from tblCustomer")
gives an error

I need to get the count into a variable.

Three ways:
1. is fast.
2. is portable to SQL Server / ASP (somewhat) etc
3. is ... whatever

Sub temp1()
Dim c&
c = CurrentDb.Table Defs("tblCustom er").RecordCoun t
Debug.Print c '91
End Sub

Sub temp2()
Dim c&
c = CurrentProject. Connection.Exec ute("SELECT COUNT(*) FROM
tblCustomer").C ollect(0)
Debug.Print c '91
End Sub

Sub temp3()
Dim c&
c = CurrentDb.OpenR ecordset("SELEC T COUNT(*) FROM
tblCustomer").F ields(0).Value
Debug.Print c '91
End Sub

[Bob Quintal]

"Karl Irvin" <88********@com cast.netwrote in
news:uu******** *************** *******@comcast .com:

In VBA, how do you see the results of an sql count statement
like the following ?

Select Count (*) as Total from tblCustomer
Result = DoCmd.RunSql("S elect Count (*) as Total from
tblCustomer") gives an error

I need to get the count into a variable.

Result = dCount("*","tbl Customer","opti onal Where Clause")

Some people badmouth as slow the domain aggregate features, dSum(),
dCount(), dAvg(), etc but they work well when used in moderation

--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com

[Lyle Fairfield]

On Oct 17, 4:12 pm, Bob Quintal <rquin...@sPAmp atico.cawrote:

Some people badmouth as slow the domain aggregate features, dSum(),
dCount(), dAvg(), etc but they work well when used in moderation

They could be very slow years and years ago, but now they seem fine. I
got into the habit of substituting SQL for them and I still do. But
that doesn't make them inadequate.

[CDMAPoster]

Bob Quintal wrote:

"Karl Irvin" <88********@com cast.netwrote in
news:uu******** *************** *******@comcast .com:

In VBA, how do you see the results of an sql count statement
like the following ?

Select Count (*) as Total from tblCustomer
Result = DoCmd.RunSql("S elect Count (*) as Total from
tblCustomer") gives an error

I need to get the count into a variable.

Result = dCount("*","tbl Customer","opti onal Where Clause")

Some people badmouth as slow the domain aggregate features, dSum(),
dCount(), dAvg(), etc but they work well when used in moderation

--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com

I think you can even use:

Result = DLookup("Count( *)", "tblCustome r", "optional Where Clause")

I use aggregate functions like those when I'm in a hurry, then use real
SQL later, like Lyle. Also, like Lyle, I don't scold anyone for using
them. I should make sure I have fixed the log :-) of inefficiencies in
my own code before picking at a speck of sawdust in someone else's code
-- and that hasn't happened yet.

James A. Fortune
CD********@Fort uneJames.com

Obtaining knowledge is relatively cheap. Not having the knowledge you
need when you need it is relatively expensive.