Where with And Clause SQL, Access-VBA

Question

Hi Everyone, Another Installment of WTF am I missing? When I run this code I get a missing operator run-time Error, see attached photo.

I can't seem to figure it out. This string is used to open a recordset that will populate a form.

Private Sub BtnUseSelection_Click()

Dim CompSQL As String

CompSQL = "SELECT T1Company.LegalName, T2AddressType.AddressType, T1Addresses.City" & _
                    " FROM T2AddressType INNER JOIN (T1Addresses INNER JOIN (T1Company INNER JOIN T3Company_Addresses ON T1Company.CompanyID = T3Company_Addresses.CompanyID) ON T1Addresses.AddressID = T3Company_Addresses.AddressID)" & _
                    " ON T2AddressType.AddressTypeID = T1Addresses.AddressType" & _
                    " WHERE T1Company.LegalName = " & Me.LstboxCompanies.Column(0) & " And T2AddressType.AddressType = " & Me.LstboxCompanies.Column(1) & " And T1Addresses.City = " & Me.LstboxCompanies.Column(2)

Set db = CurrentDb

Set RSCompany = db.OpenRecordset(CompSQL, dbOpenSnapshot, dbSeeChanges)

Not quite sure what I am missing, any help would be greatly appreciated.

score 2 · Answer 1 · answered Jun 25 '17 at 22:19

Consider parameterizing query to avoid any need for quote enclosure or string concatenation.

SQL (save below as a saved Access query, more efficient than VBA string query as database engine saves best execution plan)

PARAMETERS [LegalNameParam] Text(255), [AddressTypeParam] Text(255),
           [CityParam] Text(255);
SELECT T1Company.LegalName, T2AddressType.AddressType, T1Addresses.City
FROM T2AddressType
INNER JOIN (T1Addresses INNER JOIN (T1Company
            INNER JOIN T3Company_Addresses
             ON T1Company.CompanyID = T3Company_Addresses.CompanyID)
  ON T1Addresses.AddressID = T3Company_Addresses.AddressID)
  ON T2AddressType.AddressTypeID = T1Addresses.AddressType
WHERE T1Company.LegalName = [LegalNameParam]
 AND T2AddressType.AddressType = [AddressTypeParam]
 AND T1Addresses.City = [CityParam]

VBA (call the above query and bind values to named parameters)

Dim db As Database, RSCompany As Recordset, qdef As QueryDef
Dim CompSQL As String

Set db = CurrentDb    
Set qdef = db.QueryDefs("myStoredQuery")

qdef!LegalNameParam = Me.LstboxCompanies.Column(0)
qdef!AddressTypeParam = Me.LstboxCompanies.Column(1)
qdef!CityParam = Me.LstboxCompanies.Column(2)

Set RSCompany = qdef.OpenRecordset(dbOpenSnapshot, dbSeeChanges)

I've never seen that before I will have to research that more.... would the parameters go in coding, in their own function/sub or in the same sub? — Matthew Cochrane, Jun 26 '17 at 13:35
Run exactly as I advised, saving SQL as a saved query and running VBA as posted. `PARAMETERS` clause is valid in the Access SQL dialect. Notice the named params in sql query are being assigned values in VBA during runtime. Did you run into any issues? — Parfait, Jun 26 '17 at 13:59
Just as a FYI , a snapshot recordset isn't update-able, so no need for the dbSeeChanges. — Minty, Jun 27 '17 at 11:45

score 0 · Accepted Answer · answered Jun 25 '17 at 18:55

It seems you've missed the ' ' around your string literals.

instead of

... WHERE T1Company.LegalName = " & Me.LstboxCompanies.Column(0) & " ...

use:

... WHERE T1Company.LegalName = '" & Me.LstboxCompanies.Column(0) & "' ...

You also need to escape your strings to avoid corruption of the command (or sql injections). Look here and here for some info.

Where with And Clause SQL, Access-VBA

2 Answers2