SQL VBA query giving syntax error

CurrentDb.Execute "SELECT ZONE.CITY AS [DESTIN CITY], ZONE.PROVINCE AS [DESTIN PROVINCE], INTER.SERVICE, INTER.Class, INTER.Min AS [MIN], INTER.LTL, INTER.[500], INTER.[1M], INTER.[2M], INTER.[5M], INTER.[10M], INTER.[20M] INTO [TEMP OUTPUT] FROM [ZONE] INNER JOIN INTER ON ZONE.TCOMBI = INTER.DTCOMBO WHERE INTER.SERVICE =" & Chr(34) & [Forms]![Prov2Country]![servicecombo] & Chr(34) & " And ZONE.[ZONE] = " & Chr(34) & "LOCAL" & Chr(34) & " And INTER.[TCOMBO] Like userinput & " * " ORDER BY ZONE.CITY, INTER.Class;"

I am getting a 'type mismatch error'

Again, a similar query is running (like this but not with inner join)

Could it be because of this part

"Like userinput & " * " 

Select allOpen in new window

CurrentDb.Execute "SELECT ZONE.CITY AS [DESTIN CITY], ZONE.PROVINCE AS [DESTIN PROVINCE], INTER.SERVICE, INTER.Class, INTER.Min AS [MIN], INTER.LTL, INTER.[500], INTER.[1M], INTER.[2M], INTER.[5M], INTER.[10M], INTER.[20M] INTO [TEMP OUTPUT] FROM [ZONE] INNER JOIN INTER ON ZONE.TCOMBI = INTER.DTCOMBO WHERE INTER.SERVICE =" & Chr(34) & [Forms]![Prov2Country]![servicecombo] & Chr(34) & " And ZONE.[ZONE] = " & Chr(34) & "LOCAL" & Chr(34) & " And INTER.[TCOMBO] Like userinput ORDER BY ZONE.CITY, INTER.Class, dbFailOnError

Added dbfailonerror

Same error exists. If I remove the quotations from

"Like userinput & " * " 

Then I get too few parameters. Expected 2 :/

CurrentDb.Execute "SELECT ZONE.CITY AS [DESTIN CITY], ZONE.PROVINCE AS [DESTIN PROVINCE], INTER.SERVICE, INTER.Class, INTER.Min AS [MIN], INTER.LTL, INTER.[500], INTER.[1M], INTER.[2M], INTER.[5M], INTER.[10M], INTER.[20M] INTO [TEMP OUTPUT] FROM [ZONE] INNER JOIN INTER ON ZONE.TCOMBI = INTER.DTCOMBO WHERE INTER.SERVICE =" Chr(34) & [Forms]![Prov2Country]![servicecombo] & chr(34) & " And ZONE.[ZONE] = " chr(34) & "LOCAL" & chr(34) & " And INTER.[TCOMBO] Like userinput ORDER BY ZONE.CITY, INTER.Class;


should become:
CurrentDb.Execute "SELECT ZONE.CITY AS [DESTIN CITY], ZONE.PROVINCE AS [DESTIN PROVINCE], INTER.SERVICE, INTER.Class, INTER.Min AS [MIN], INTER.LTL, INTER.[500], INTER.[1M], INTER.[2M], INTER.[5M], INTER.[10M], INTER.[20M] INTO [TEMP OUTPUT] FROM [ZONE] INNER JOIN INTER ON ZONE.TCOMBI = INTER.DTCOMBO WHERE INTER.SERVICE =" & Chr(34) & [Forms]![Prov2Country]![servicecombo] & chr(34) & " And ZONE.[ZONE] = " & chr(34) & "LOCAL" & chr(34) & " And INTER.[TCOMBO] Like userinput ORDER BY ZONE.CITY, INTER.Class;

You needed to add a & right after INTER.SERVICE =" and right after ZONE.[ZONE] = " or else it is not knowing to join the entire string and variables

Ow I was too fast :o

Thanks so far. Now that brings it down to "too few parameters; expected 1" error.

All forms are open and all tables involved are available (but closed)

CurrentDb.Execute "SELECT ZONE.CITY AS [DESTIN CITY], ZONE.PROVINCE AS [DESTIN PROVINCE], INTER.SERVICE, INTER.Class, INTER.Min AS [MIN], INTER.LTL, INTER.[500], INTER.[1M], INTER.[2M], INTER.[5M], INTER.[10M], INTER.[20M] INTO [TEMP OUTPUT] FROM [ZONE] INNER JOIN INTER ON ZONE.TCOMBI = INTER.DTCOMBO WHERE INTER.SERVICE =" & Chr(34) & [Forms]![Prov2Country]![servicecombo] & Chr(34) & " And ZONE.[ZONE] = " & Chr(34) & "LOCAL" & Chr(34) & " And INTER.[TCOMBO] Like '" & userinput & "' ORDER BY ZONE.CITY, INTER.Class, dbFailOnError"

above is the latest one. I suspect the error is due to the way I added dbFailOnError?

try this

change this part   INTER.Min AS [MIN]  with  INTER.[MIN]


CurrentDb.Execute "SELECT ZONE.CITY AS [DESTIN CITY], ZONE.PROVINCE AS [DESTIN PROVINCE], INTER.SERVICE, INTER.Class, INTER.[MIN], INTER.LTL, INTER.[500], INTER.[1M], INTER.[2M], INTER.[5M], INTER.[10M], INTER.[20M] INTO [TEMP OUTPUT] FROM [ZONE] INNER JOIN INTER ON ZONE.TCOMBI = INTER.DTCOMBO WHERE INTER.SERVICE =" & Chr(34) & [Forms]![Prov2Country]![servicecombo] & Chr(34) & " And ZONE.[ZONE] = " & Chr(34) & "LOCAL" & Chr(34) & " And INTER.[TCOMBO] Like '" & userinput & "' ORDER BY ZONE.CITY, INTER.Class, dbFailOnError"

the error is still there. (expected 1) my guess is that its on the bottom half and due to some syntax thing Should I ask this as another question?

Thanks!

try this

change this part   INTER.Min AS [MIN]  with  INTER.[MIN]
place the ending " before  { ,dbfailonerror }

CurrentDb.Execute "SELECT ZONE.CITY AS [DESTIN CITY], ZONE.PROVINCE AS [DESTIN PROVINCE], INTER.SERVICE, INTER.Class, INTER.[MIN], INTER.LTL, INTER.[500], INTER.[1M], INTER.[2M], INTER.[5M], INTER.[10M], INTER.[20M] INTO [TEMP OUTPUT] FROM [ZONE] INNER JOIN INTER ON ZONE.TCOMBI = INTER.DTCOMBO WHERE INTER.SERVICE =" & Chr(34) & [Forms]![Prov2Country]![servicecombo] & Chr(34) & " And ZONE.[ZONE] = " & Chr(34) & "LOCAL" & Chr(34) & " And INTER.[TCOMBO] Like '" & userinput & "' ORDER BY ZONE.CITY, INTER.Class", dbFailOnError

dbFailOnError isn't part of the query, it's an argument of Execute, so it shouldn't be in the query string.

Change the end from:
    INTER.Class, dbFailOnError"
To:
    INTER.Class", dbFailOnError

You need to close the query before you can add the dbFailOnError

that worked :) I knew that quotation at the end shouldn't be there!

The table output nothing (which is another logical problem) but thanks for the help!

quick question

'" & userinput & "'

would that be equivalent to

= "LOCAL" & "*"

^ that is the 2nd part is given wildcard?

Thanks!

if you want a wildcard attached to the variable


'" & userinput & "*'