Solved

SQL VBA query giving syntax error

Posted on 2011-09-21
15
289 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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Exporting Access Tables as CSV 3 24
SQL multicriteria from ONE textbox 32 43
Trying to force an answer in a combobox 7 18
access query to sql server 3 20
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…
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Familiarize people with the process of utilizing SQL Server functions 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 Ac…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

810 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