?
Solved

SQL VBA query giving syntax error

Posted on 2011-09-21
15
Medium Priority
?
300 Views
Last Modified: 2012-05-12
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;
The above statement is giving error at this point

INTER.SERVICE =" Chr(34)

saying "expected end of statement". But I compared this statement with another statement it all looks fine!

Any help is appreciated!

I put it in quotes so its easy to read. 'Userinput' is a variable that is being passed to the function where this query is in.
0
Comment
Question by:Shanan212
  • 7
  • 5
  • 2
  • +1
15 Comments
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 2000 total points
ID: 36573948
you need to add  "&"


INTER.SERVICE =" & Chr(34)
0
 
LVL 13

Author Comment

by:Shanan212
ID: 36573989
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 & " * " 

Open in new window

0
 
LVL 120

Assisted Solution

by:Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1) earned 2000 total points
ID: 36573998

what data type is TCOMBO , Text or Number ?

if Number use this

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] =  'LOCAL'  And INTER.[TCOMBO] Like " & userinput & " ORDER BY ZONE.CITY, INTER.Class;


if Text use this

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] =  'LOCAL'  And INTER.[TCOMBO] Like '" & userinput & "' ORDER BY ZONE.CITY, INTER.Class;


0
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
LVL 13

Author Comment

by:Shanan212
ID: 36574012
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 :/
0
 
LVL 4

Expert Comment

by:jmdl1983
ID: 36574017
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
0
 
LVL 13

Author Comment

by:Shanan212
ID: 36574036
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)
0
 
LVL 13

Author Comment

by:Shanan212
ID: 36574041
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?
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 36574070
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"

0
 
LVL 13

Author Comment

by:Shanan212
ID: 36574103
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!
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 36574105
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
0
 
LVL 35

Expert Comment

by:Norie
ID: 36574110
dbFailOnError isn't part of the query, it's an argument of Execute, so it shouldn't be in the query string.
0
 
LVL 4

Expert Comment

by:jmdl1983
ID: 36574119
Change the end from:
    INTER.Class, dbFailOnError"
To:
    INTER.Class", dbFailOnError

You need to close the query before you can add the dbFailOnError
0
 
LVL 13

Author Comment

by:Shanan212
ID: 36574128
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!
0
 
LVL 13

Author Comment

by:Shanan212
ID: 36574284
quick question

'" & userinput & "'

would that be equivalent to

= "LOCAL" & "*"

^ that is the 2nd part is given wildcard?

Thanks!
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 36574336
if you want a wildcard attached to the variable


'" & userinput & "*'
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question