Compiler error problem with SQL in VBA

[ruchie]

I am constantly getting a "Compiler error : expected: end of
statement" error on this sql statement i am running in vba. could
anyone please guide me where i am going wrong?

Dim AudID As Integer
Dim audit As String

AudID = (Me.audits.ItemData(i))
audit = DoCmd.RunSQL "SELECT [Audit] FROM tblauditableUniverse WHERE
[AuditID] = AudID;"

 

[J_Goddard via AccessMonster.com]

First, you have written the docmd.runSQL as it it were a function, which it
isn't. Remove the audit = from the from of the line:

audit = DoCmd.RunSQL "SELECT [Audit] FROM tblauditableUniverse WHERE [AuditID]
= AudID;"

Second, you cannot use a Select query in the docmd.runsql statement - only
Action queries.

*IF* the SQL is expected to return only one record, you can use DLookup:

audit = DLookup("[audit]", "tblauditableUniverse", "[AuditID] = " & AudID)

HTH

John

I am constantly getting a "Compiler error : expected: end of
statement" error on this sql statement i am running in vba. could
anyone please guide me where i am going wrong?

Dim AudID As Integer
Dim audit As String

AudID = (Me.audits.ItemData(i))
audit = DoCmd.RunSQL "SELECT [Audit] FROM tblauditableUniverse WHERE
[AuditID] = AudID;"

 

[Douglas J. Steele]

Several things wrong.

First of all, RunSQL isn't a function, so it doesn't return any values.

Secondly, you can't use RunSQL with Select queries: it's only for Action
queries (Delete, Update, Insert Into)

And even if the above weren't true, your SQL is incorrect. You need to have
the reference to the variable outside of the quotes, as Jet doesn't know
anything about VBA variables.

Try:

audit = Nz(DLookup("[Audit]", "tblauditableUniverse", _
"[AuditID] = " & AudID), "Not found")

That'll work if AuditID is a numeric field. If it's text, use

audit = Nz(DLookup("[Audit]", "tblauditableUniverse", _
"[AuditID] = """ & AudID & """"), "Not found")

(that's 3 double quotes in front, and 4 double quotes after)

 

[Pendragon]

You need to separate your variable from the actual string. I've gone a step
further for clarity and separated the DoCmd from assigning the string
variable.

AudID = (Me.audits.ItemData(i))
audit = "SELECT [Audit] FROM tblauditableUniverse WHERE [AuditID] = " &
AudID & ";"
DoCmd.RunSQL audit

HTH