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!)
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
Microsoft AccessVBA
Last Comment
Dale Fye
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
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.
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"
Ron, what is that:
.Collect(0)
syntax? I've never seen that before?
.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.
.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
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
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.
I so rarely use ADO, that I didn't pickup on that syntax:
CurrentProject.Connection.Execute
I was focused on the Execute.
ASKER
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.
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.