Link to home
Start Free TrialLog in
Avatar of Patrick O'Dea
Patrick O'DeaFlag for Ireland

asked on

Access VBA .... Quotes!

See attached code
How do I add to the where clause a second condition ... being .... len(TransDesc)=0.
The quotes are confusing me (again!)

Var = CurrentProject.Connection.Execute( _
"SELECT COUNT(*) FROM tblBankAIB " _
& " WHERE AIBCreditValue<>0 ").Collect(0)

MsgBox "There are " & Var & " lodgements with no Giro reference"



End Sub

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of IrogSinta
IrogSinta
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Patrick O'Dea

ASKER

Thx!  Easy one!
The best way to do this is to build the SQL before the Execute method, so that you can debug it until it is correct.  

Plus, you cannot set the value of a variable to the results of an EXECUTE method.

Dim rs as DAO.Recordset
strSQL = "SELECT COUNT(*) as Lodgements FROM tblBankAIB " _
             & "WHERE AIBCreditValue<>0"
debug.print strSQL
set rs = currentdb.openrecordset(strSQL, , dbfailonerror)
Var = rs!Lodgements
rs.close
set rs = nothing

MsgBox "There are " & Var & " lodgements with no Giro reference"

Open in new window

Ron, what is that:

.Collect(0)

syntax?  I've never seen that before?
You're right, Dale.  I didn't even pay attention to that.  I would normally use:
           .Fields(0)
In order to get the result of the first field.
@Ron,  I've never seen that done in an Execute method, and cannot figure out how to do that.

I assume that you were thinking that the OP was opening a recordset, not trying to retrieve data through an Execute method.  Is that correct?

@Dewsbury,

Did you even try that syntax in the context you provided?  If so, and it worked, could you please post what worked for you, because I've never seen it done that way before.

Dale
@Dale, I looked into it and apparently the syntax is correct.  It is used for ADO connections.  I even did a quick test and it works.  I think we both learned something new :-)

Ron
Thanks, Ron.

I so rarely use ADO, that I didn't pickup on that syntax:

CurrentProject.Connection.Execute

I was focused on the Execute.
As the OP .... I must confess that the Collect.(0) syntax was cut and pasted from elsewhere..... (perhaps contrary to best practice)!
21Dewsbury,

No, don't assume that at all.  I just rarely use ADO, so didn't recognize the syntax.  It's good when even the experts get a chance to learn something new.