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("Select 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.OpenRecordset("SELECT 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]

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

Sub temp1()
Dim c&
c = CurrentDb.TableDefs("tblCustomer").RecordCount
Debug.Print c '91
End Sub

Sub temp2()
Dim c&
c = CurrentProject.Connection.Execute("SELECT COUNT(*) FROM
tblCustomer").Collect(0)
Debug.Print c '91
End Sub

Sub temp3()
Dim c&
c = CurrentDb.OpenRecordset("SELECT COUNT(*) FROM
tblCustomer").Fields(0).Value
Debug.Print c '91
End Sub

[Bob Quintal]

"Karl Irvin" <88kar...@comcast.net> wrote in
news:uu2dnXt9u9WsjKjY...@comcast.com:

Result = dCount("*","tblCustomer","optional 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...@sPAmpatico.ca> wrote:

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

[CDMAP...@fortunejames.com]

I think you can even use:

Result = DLookup("Count(*)", "tblCustomer", "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
CDMAP...@FortuneJames.com

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