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]

Re: sql Select Count result to variable ?

<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]

Re: sql Select Count result to variable ?

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]

Re: sql Select Count result to variable ?

"Karl Irvin" <88karl3200@com cast.netwrote in
news:uu2dnXt9u9 WsjKjYnZ2dnUVZ_ tSdnZ2d@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]

Re: sql Select Count result to variable ?

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@FortuneJames.com]

Re: sql Select Count result to variable ?

Bob Quintal wrote:

"Karl Irvin" <88karl3200@com cast.netwrote in
news:uu2dnXt9u9 WsjKjYnZ2dnUVZ_ tSdnZ2d@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
CDMAPoster@Fort uneJames.com

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