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, Feb 28 '17 at 13:10
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, Feb 28 '17 at 13:24
I don't think `Nz` is the problem. Read this: [How to debug dynamic SQL in VBA](http://stackoverflow.com/questions/418960/managing-and-debugging-sql-queries-in-ms-access/1099570#1099570) . Most probably you need `'` around all controls that contain strings. — Andre, Feb 28 '17 at 13:28
Looking at your error dialog, you're getting empty strings back from `Me.cbOpleiding`, `Me.cbOpleidingNiveau`, `Me.cbStudiegroep`, and `Me.cbStudierichting`. — Comintern, Feb 28 '17 at 13:30
That's correct. They're empty. Reason is that if they're not filled that the query returns everything that's not according to those selections. Just as it does and works in the first paragraph. — TimB, Feb 28 '17 at 13:33
In the _Immediate Window_ (ctrl-G), do `?SQL` after you've built your string, but before you've executed it. That will let you examine exactly what you're passing to your query. Copy the results and attempt to execute that in... SSMS? or whatever query engine you're using. That should give you some better feedback to exactly where your error is. (Unless you're using Access as the database, not just a pass through - its error messages are useless.) If you don't find your own error, post the results of `?SQL` in your OP to help others determine where you've gone wrong. — FreeMan, Feb 28 '17 at 13:40
1/2 Well I don't use a different database so yes it's the shitty Access database it self. The reason to use Access as a local database to keep a overview of training's. `?SQL` gives the following in the _Immediate Window_ — TimB, Feb 28 '17 at 13:57
2/2 `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]= ,Overzicht_codes.[opleidingniveau]))<>False) AND ((Nz([subniveau]= ,Overzicht_codes.[subniveau]<>False))<>False) AND ((Nz([studiegroep]= ,Overzicht_codes.[studiegroep]<>False))<>False) AND ((Nz([studierichting]= ,Overzicht_codes.[studierichting]<>False))<>False)) ORDER BY Overzicht_codes.[code_compleet]` — TimB, Feb 28 '17 at 13:58
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, Feb 28 '17 at 14:02
If you're building SQL yourself, you can't just include the empty parts in your where clause. `WHERE (((Nz([opleidingniveau]= ,` isn't correct. If they're supposed to be strings, you need to text qualify them - `WHERE (((Nz([opleidingniveau]= '',`. — Comintern, Feb 28 '17 at 14:06
@Parfait yes you're right. I'll try to do a workaround for it. Of course it's my own doing that does this. Thanks everyone for the help. — TimB, Feb 28 '17 at 14:20

Parfait · Accepted Answer · 2017-02-28T15:09:45.020

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()

Thanks for the feedback! I've solved it in a other way that I'll post underneath your post. — TimB, Mar 02 '17 at 10:32

score 0 · Answer 2 · answered Mar 02 '17 at 10:35

I've solved it in another way:

Dim db As dao.Database
Dim rst As dao.Recordset
Dim qdf As dao.QueryDef
Dim SQL As String

SQL = "SELECT code_compleet as Code, omschrijving1, omschrijving2, omschrijving3, omschrijving4, omschrijving5, omschrijving6 " _
    & "FROM Overzicht_codes " _
    & "WHERE omschrijving1 LIKE '*" & Me.tbOmschrijving & "*' " _
    & " OR omschrijving2 LIKE '*" & Me.tbOmschrijving & "*' " _
    & " OR omschrijving3 LIKE '*" & Me.tbOmschrijving & "*' " _
    & " OR omschrijving4 LIKE '*" & Me.tbOmschrijving & "*' " _
    & " OR omschrijving5 LIKE '*" & Me.tbOmschrijving & "*' " _
    & " OR omschrijving6 LIKE '*" & Me.tbOmschrijving & "*' " _
    & " OR code_compleet LIKE '*" & Me.tbOmschrijving & "*' " _
    & "ORDER BY [code_compleet] "

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

Set rst = qdf.OpenRecordset()

Set Me.lbOpleidingOverzicht.Recordset = rst
Me.lbOpleidingOverzicht.Requery

Set qdf = Nothing
Call EmptyRecords

As you can see I got rid of the different filters and applied only one filter that looks in the entire query / table.

Thanks everyone for thinking with me and giving me insight on how o tackle the problem of different filters.

By placing wildcards in front of every statement Access won't use any indexes in searching your data. Multiply that out by 7 fields for every record and once you get to even a Medium sized number of records this query will run painfully slowly and then probably hang. You should construct your where clause dynamically only including the criteria you need. Have a look here for a excellent guide http://allenbrowne.com/ser-62.html — Minty, Mar 02 '17 at 11:16
Also as shown above, consider parameterization (industry best practice) to avoid need of quote handling and malicious users of your form who can run [sql injection](http://bobby-tables.com/). For `LIKE` add wildcard with binded values. Even use a saved query for optimizer's best plan. — Parfait, Mar 02 '17 at 14:00

Access vba Error 3075 syntax

2 Answers2