SQL vs VBA query comparison

Hi,

This is 2 part question. 400 for 1st part and 100 points for 2nd part.

1. Sorry I am confused as in why this VBA called query isn't displaying results while a query, designed via "query design" feature is working.

This is the query I am calling from VBA

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 [TEMP2] 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 & "*' & Chr(34) ORDER BY ZONE.CITY, INTER.Class", dbFailOnError

The query designed via 'query-design' has the following sql

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)=[Forms]![Prov2Country]![servicecombo]) AND ((ZONE.ZONE)="Local") AND ((INTER.TCOMBO) Like "HALIFAXNOVA SCOTIA" & "*"))
ORDER BY ZONE.CITY, INTER.Class;


Part 2. I want to display some variables I pass into a function as 'column headers'

variable     column name
ocity         origin city
oprovince   origin province

given the following code

CurrentDb.Execute "SELECT ocity as [ORIGIN CITY], oprovince as [ORIGIN PROVINCE], ZONE.CITY AS [DESTIN CITY], ZONE.PROVINCE AS

but the above is not displaying. Tried online but its very hard to find this type of help :/

Thanks for the help!
LVL 13
Shanan212Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sdstuberCommented:
CurrentDb.Execute "SELECT '" & ocity & "' as [ORIGIN CITY], '" & oprovince & "' as [ORIGIN PROVINCE], 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 [TEMP2] 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 & "*' & Chr(34) ORDER BY ZONE.CITY, INTER.Class", dbFailOnError

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Shanan212Author Commented:
Thanks that worked!

As for part 1: I've simplified the queries so its easy to see why

VBA Query

CurrentDb.Execute "SELECT ZONE.CITY AS [DESTIN CITY], ZONE.PROVINCE AS [DESTIN PROVINCE], INTER.SERVICE, INTER.Class INTO [TEMP2] 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 & "*' & Chr(34) ORDER BY ZONE.CITY, INTER.Class", dbFailOnError

Above is outputting a table but empty. userinput value = "HALIFAXNOVA SCOTIA"

SQL query

SELECT ZONE.CITY AS [DESTIN CITY], ZONE.PROVINCE AS [DESTIN PROVINCE], INTER.SERVICE, INTER.Class INTO [TEMP OUTPUT]
FROM [ZONE] INNER JOIN INTER ON ZONE.TCOMBI = INTER.DTCOMBO
WHERE (((INTER.SERVICE)=[Forms]![Prov2Country]![servicecombo]) AND ((ZONE.ZONE)="Local") AND ((INTER.TCOMBO) Like "HALIFAXNOVA SCOTIA" & "*"))
ORDER BY ZONE.CITY, INTER.Class;

The above is outputting a big table
sdstuberCommented:
>>> This is 2 part question. 400 for 1st part and 100 points for 2nd part.

Sorry I didn't see this before,  this is inappropriate use of the point system.

You should be asking a single question per question.
Become a Microsoft Certified Solutions Expert

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

Shanan212Author Commented:
Ow sorry! I will close this out then! Thanks!
sdstuberCommented:
http:#36574576

I have no idea what you're trying to tell me.

sdstuberCommented:
note,  you can ask a 100 point question  and a 400 point question if you want. that is fine.

you'll likely get more response to the 400 point on though,  some people won't even see a 100 point question because their filters block them
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.