Access vba Error 3075 syntax

Question

I've tried to find an answer in the posts that are similar but I can't find where I need to put a extra syntax or remove one.

The query on its own works if I put it in a listbox recordset in the property window as:

SELECT Overzicht_codes.code_compleet AS Code, Overzicht_codes.omschrijving1, Overzicht_codes.omschrijving2, Overzicht_codes.omschrijving3, Overzicht_codes.omschrijving4, Overzicht_codes.omschrijving5, Overzicht_codes.omschrijving6 
FROM Overzicht_codes
WHERE (((Nz([opleidingniveau]=[Forms]![OverzichtOpleidingen].[cbOpleiding],[opleidingniveau]))<>False) 
AND ((Nz([subniveau]=[Forms]![OverzichtOpleidingen].[cbopleidingniveau],[subniveau]<>False))<>False) 
AND ((Nz([studiegroep]=[Forms]![OverzichtOpleidingen].[cbstudiegroep],[studiegroep]<>False))<>False) 
AND ((Nz([studierichting]=[Forms]![OverzichtOpleidingen].[cbstudierichting],[studierichting]<>False))<>False))
ORDER BY Overzicht_codes.code_compleet;

Now I want to have the same code in VBA as a kind of 'reset'. For VBA it needed some altering:

SQL = "SELECT Overzicht_codes.code_compleet AS Code, Overzicht_codes.omschrijving1, Overzicht_codes.omschrijving2, Overzicht_codes.omschrijving3, Overzicht_codes.omschrijving4, Overzicht_codes.omschrijving5, Overzicht_codes.omschrijving6 " _
    & "FROM Overzicht_codes " _
    & "WHERE (((Nz([opleidingniveau]= " & Me.cbOpleiding & ",Overzicht_codes.[opleidingniveau]))<>False) " _
    & "AND ((Nz([subniveau]= " & Me.cbOpleidingNiveau & ",Overzicht_codes.[subniveau]<>False))<>False) " _
    & "AND ((Nz([studiegroep]= " & Me.cbStudiegroep & ",Overzicht_codes.[studiegroep]<>False))<>False) " _
    & "AND ((Nz([studierichting]= " & Me.cbStudierichting & ",Overzicht_codes.[studierichting]<>False))<>False)) " _
    & "ORDER BY Overzicht_codes.[code_compleet]"

I've read something about putting an extra ' in string parts of the code. But after several tries it still gives the error.

For an extra insight the error message is below:

Who can help me give insight in what I did wrong or what I forgot?

Nz is not SQL syntax so needs to be set outside of the string. — finjo
That's strange. The normal SQL query (first code) has NZ as well and it works. The translation to VBA doesn't (2nd code paragraph) So how complex would the translation be it it's true what you say? — TimB
I don't think Nz is the problem. Read this: How to debug dynamic SQL in VBA . Most probably you need ' around all controls that contain strings. — Andre
Looking at your error dialog, you're getting empty strings back from Me.cbOpleiding, Me.cbOpleidingNiveau, Me.cbStudiegroep, and Me.cbStudierichting. — Comintern
Is there a reason why you converted to a VBA string query? Saved queries tend to be slightly more efficient as the query optimizer saves the best plan. With VBA, query is executed immediately without caching. Plus you can open a recordset with saved query. By the way on your blast against Access, there is an old saying about the tool man blaming his tools. — Parfait

Parfait Parfait · Accepted Answer · 2017-02-28T15:04:01

Consider a parameterized query which avoids any need for quote enclosures. With DAO, you do so with the Parameters collection which specifies the placeholder name and data type and precedes the usual SQL commands (i.e., SELECT, UPDATE, INSERT, DELETE, ALTER):

' PREPARED STATEMENT WITH PLACEHOLDERS
strSQL = "PARAMETERS [cbOpleiding_param] TEXT, [cbopleidingniveau_param] TEXT," _
         & "         [cbstudiegroep_param] TEXT, [cbstudierichting_param] TEXT;" _
         & "SELECT Overzicht_codes.code_compleet AS Code, Overzicht_codes.omschrijving1," _
         & "       Overzicht_codes.omschrijving2, Overzicht_codes.omschrijving3," _
         & "       Overzicht_codes.omschrijving4, Overzicht_codes.omschrijving5," _
         & "       Overzicht_codes.omschrijving6 " _
         & "FROM Overzicht_codes " _
         & "WHERE (((Nz([opleidingniveau]= [cbOpleiding_param], Overzicht_codes.[opleidingniveau]))<>False) " _
         & "AND ((Nz([subniveau]= [cbopleidingniveau_param], Overzicht_codes.[subniveau]<>False))<>False) " _
         & "AND ((Nz([studiegroep]= [cbstudiegroep_param], Overzicht_codes.[studiegroep]<>False))<>False) " _
         & "AND ((Nz([studierichting]= [cbstudierichting_param], Overzicht_codes.[studierichting]<>False))<>False)) " _
         & "ORDER BY Overzicht_codes.[code_compleet];"

Set db = CurrentDb
Set qdf = db.CreateQueryDef("", strSQL)

' BIND VALUES TO PARAMETERS
qdf.Parameters("cbOpleiding_param") = Me.cbOpleiding
qdf.Parameters("cbopleidingniveau_param") = Me.cbOpleidingNiveau 
qdf.Parameters("cbstudiegroep_param") = Me.cbStudiegroep
qdf.Parameters("cbstudierichting_param") = Me.cbStudierichting

Set rst = qdf.OpenRecordset()
...

In fact, the above prepared statement can be saved as a stored query and then just called by name for binding parameter values as the PARAMETERS clause is fully compliant in Access SQL:

Set db = CurrentDb
Set qdf = db.QueryDefs("SavedQueryName")

' BIND VALUES TO PARAMETERS
qdf.Parameters("cbOpleiding_param") = Me.cbOpleiding
qdf.Parameters("cbopleidingniveau_param") = Me.cbOpleidingNiveau 
qdf.Parameters("cbstudiegroep_param") = Me.cbStudiegroep
qdf.Parameters("cbstudierichting_param") = Me.cbStudierichting

Set rst = qdf.OpenRecordset()

Access vba Error 3075 syntax

2 Answers