Solved

SQL VBA query giving syntax error

Posted on 2011-09-21
15
293 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 5
  • 2
  • +1
15 Comments
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 500 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 500 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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 34

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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
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…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

691 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