syntax error in string in query in VBA access

Question

I get 3075 error running this line in vba access:

Dim sqlMZG As String
sqlMZG = "SELECT MAZeitenGesamt.* Where MAZeitenGesamt.MA = 'JPA' FROM MAZeitenGesamt;"

Where JPA is a constant value. I tried the following forms and none of them worked.

SELECT MAZeitenGesamt.* Where MAZeitenGesamt.[MA] = '" & "JPA" & "' FROM MAZeitenGesamt;

SELECT MAZeitenGesamt.* Where MAZeitenGesamt.MA = '" & "JPA" & "' FROM MAZeitenGesamt;

SELECT MAZeitenGesamt.* Where MAZeitenGesamt.MA = ""JPA"" FROM MAZeitenGesamt;

SELECT MAZeitenGesamt.* Where MAZeitenGesamt.MA = \"JPA\"  FROM MAZeitenGesamt;

SELECT MAZeitenGesamt.* Where MAZeitenGesamt.MA = \'JPA\' FROM MAZeitenGesamt;

Any ideas?

the proper format for a query is like this: "SELECT * FROM tbl WHERE something = x" so in your case: "SELECT MAZeitenGesamt.* FROM MAZeitenGesamt WHERE MAZeitenGesamt.MA = 'JPA'" — gizlmo
– gizlmo, Commented Aug 12, 2016 at 10:16
@gizlmeier -- I'd recommend you add that as an answer, as I think you have correctly addressed Mary's issue — Hambone
– Hambone, Commented Aug 12, 2016 at 11:44
@gizlmeier the problem was what u said. do u wanna send it as an answer? — dieKoderin
– dieKoderin, Commented Aug 15, 2016 at 13:46

Hambone · Accepted Answer · 2016-08-12 11:16:38Z

2

I'd recommend you use a parameter rather than quoting a literal. That said, @gizlmeier is right, in that your syntax is wrong to begin with.

Dim sqlMZG As String
sqlMZG = "parameters [MAParam] text; " & _
  "SELECT MAZeitenGesamt.* FROM MAZeitenGesamt Where MAZeitenGesamt.MA = [MAParam];"

From there, when you create your query you can set the value for the parameter:

Set qry = CurrentDb.CreateQueryDef("GetMaz", sqlMZG)
qry.Parameters("MAParam") = JPA

No messy quoting to worry about and such.

answered Aug 12, 2016 at 11:16

Hambone

16.5k8 gold badges54 silver badges79 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

marlan Over a year ago

That is: qry.Parameters("MAParam") = "JPA"

Hambone Over a year ago

Oh, it's a literal? I thought JPA was a variable containing the actual value... my bad. If it's a literal, then the parameter isn't quite as helpful

Collectives™ on Stack Overflow

syntax error in string in query in VBA access

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related