Solved

SQL VBA query giving syntax error

Posted on 2011-09-21
15
290 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 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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
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 33

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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

830 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