By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,334 Members | 2,776 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,334 IT Pros & Developers. It's quick & easy.

sql Select Count result to variable ?

P: n/a
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.
Oct 17 '06 #1
Share this Question
Share on Google+
5 Replies


P: n/a
<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.

Oct 17 '06 #2

P: n/a
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("Select 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.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

Oct 17 '06 #3

P: n/a
"Karl Irvin" <88********@comcast.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("Select Count (*) as Total from
tblCustomer") gives an error

I need to get the count into a variable.
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

Oct 17 '06 #4

P: n/a
On Oct 17, 4:12 pm, Bob Quintal <rquin...@sPAmpatico.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.

Oct 17 '06 #5

P: n/a
Bob Quintal wrote:
"Karl Irvin" <88********@comcast.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("Select Count (*) as Total from
tblCustomer") gives an error

I need to get the count into a variable.
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
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
CD********@FortuneJames.com

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

Oct 19 '06 #6

This discussion thread is closed

Replies have been disabled for this discussion.