How can I avoid runtime error 3075 when running this Access VBA query?

Question

Can someone tell what is wrong with this query?

sqltext = "SELECT utyp, count(*) AS anzahl
           INTO UTYP_Anzahl FROM 01_umwelt 
           WHERE [01_umwelt].status = Me.Controls(""STATUS"").Value 
           GROUP BY utyp;"

I am getting run time error 3075.

Answer 1

The SQL you are using is not valid. You must escape the query string when adding a reference to a control. Also, you can get the control directly by it's name. Try the following:

sqltext = "SELECT utyp, count(*) AS anzahl INTO UTYP_Anzahl " _
        & "FROM 01_umwelt WHERE [01_umwelt].status = " _
        & STATUS.Value _
        & " GROUP BY utyp;"