?
Solved

SQL vs VBA query comparison

Posted on 2011-09-21
6
Medium Priority
?
242 Views
Last Modified: 2012-05-12
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!
0
Comment
Question by:Shanan212
  • 4
  • 2
6 Comments
 
LVL 74

Accepted Solution

by:
sdstuber earned 2000 total points
ID: 36574494
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
0
 
LVL 13

Author Comment

by:Shanan212
ID: 36574576
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
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 36574598
>>> 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.
0
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.

 
LVL 13

Author Closing Comment

by:Shanan212
ID: 36574613
Ow sorry! I will close this out then! Thanks!
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 36574618
http:#36574576

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

0
 
LVL 74

Expert Comment

by:sdstuber
ID: 36574629
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
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone 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

This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Article by: Tammy
MySQLTuner is a script written in Perl that allows you to review a MySQL installation quickly and make adjustments to increase performance and stability. The current configuration variables and status data is retrieved and presented in a brief forma…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
If you are looking for an automated solution for backup single or multiple Office 365 user mailboxes to Outlook data file, then you can use Kernel Office 365 Backup & Restore tool. Go through the video to check out the steps to backup single or mult…
Suggested Courses

601 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