Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
| Search | Today's Posts | Mark Forums Read
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #1 (permalink)  
Old April 5th, 2004, 05:23 PM
lxu lxu is offline
Authorized User
 
Join Date: Oct 2003
Location: , , .
Posts: 31
Thanks: 0
Thanked 0 Times in 0 Posts
Default How to execute SELECT count(*)

Please help and here is the code:

Dim db As DAO.Database
Dim strSQLSel As String

...

strSQLSel = "SELECT Count(*) AS Expr1 FROM TBL-1 WHERE (((TBL-1.[COLUMN-1])='" & var-1 & "') AND ((TBL-1.[COLUMN-2]) Is Not Null)) OR (((TBL-1.[COLUMN-2)='" & var-1 & "'))"

Set db = CurrentDb
db.Execute (strSQLSel)

The error message is "Cannot execute a select query"
If I should not use db.Execute, what should I use here to get the number of count?

Thanks in advance,
Lx
  #2 (permalink)  
Old April 5th, 2004, 08:27 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Melbourne, Vic, Australia.
Posts: 308
Thanks: 0
Thanked 0 Times in 0 Posts
Default

And just where exactly where you wanting this Count to be returned to? A MsgBox? The Debug Window?
the Execute statement can only execute actions, not selects.

Here's two ways you could get around your problem
1: Use a recordset
Code:
strSQLSel = "SELECT Count(*) AS Expr1 FROM TBL-1 WHERE (((TBL-1.[COLUMN-1])='" & var-1 & "') AND ((TBL-1.[COLUMN-2]) Is Not Null)) OR (((TBL-1.[COLUMN-2)='" & var-1 & "'))"

Dim rst as Recordset
rst.MoveFirst
Set rst = CurrentDB.OpenRecordset("strSQLSel")
Debug.Print rst("Expr1")
rst.Close
Set rst = Nothing
2: Create a new query
Code:
Dim qdf as QueryDef
Set qdf = CurrentDB.CreateQueryDef("NewQuery", strSQLSel)
docmd.OpenQuery "NewQuery"
I'm guessing it's the first one you'll end up with

I am a loud man with a very large hat. This means I am in charge
  #3 (permalink)  
Old April 8th, 2004, 08:16 AM
lxu lxu is offline
Authorized User
 
Join Date: Oct 2003
Location: , , .
Posts: 31
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thanks for the help. It works.

lx



Similar Threads
Thread Thread Starter Forum Replies Last Post
Select COUNT(*) grstad Classic ASP Basics 5 April 28th, 2006 11:01 AM
How to get "SELECT COUNT(*)" return information? datagram ASP.NET 1.0 and 1.1 Basics 3 December 23rd, 2004 01:21 AM
SELECT COUNT as a ROW?? t0ny SQL Language 3 December 8th, 2004 05:31 AM
Select Where Greater Than Count dave.cody@accenture.com SQL Language 3 April 29th, 2004 05:14 AM
COUNT ON SELECT DISTINCT stmt savoym SQL Language 7 August 28th, 2003 07:58 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.