Solved

SQL vs VBA query comparison

Posted on 2011-09-21
7
309 Views
Last Modified: 2012-08-14
Hi,

Sorry I am confused as in why this VBA-initiated query isn't displaying results while a query, designed via "query design functionality" in access is displaying results.

This is the query I am calling from VBA

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 an empty table. 'userinput' value = "HALIFAXNOVA SCOTIA" (I suspect this being the culprit)

SQL query - This is the same query I converted into above VBA

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 SQL (taken from a query design) is outputting values

Any help is appreciated!

Thanks!
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 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 36574703
upload a .mdb version of the db
0
 
LVL 34

Expert Comment

by:Norie
ID: 36574707
It's an action query and if you were to run the query from design view instead of displaying it in datasheet view you wouldn't see any results either.

You would probably get a warning/notification about appending/altering records.
0
 
LVL 13

Author Comment

by:Shanan212
ID: 36574726
I could do that. Sorry about this but before that (lets see if this helps if not I will do that)

So meanwhile I did this, I removed the last part of the query and it output to a huge table (which is the expected output)

CurrentDb.Execute "SELECT '" & ocity & "' as [ORIGIN CITY], '" & oprov & "' as [ORIGIN PROVINCE], 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), dbFailOnError    

Note, this is the portion I cut out from my original query above in post #27320110

& " And INTER.[TCOMBO] Like '" & userinput & "*' & Chr(34) ORDER BY ZONE.CITY, INTER.Class", dbFailOnError

So its this portion thats giving me empty table (while the SQL works)
0
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.

 
LVL 48

Accepted Solution

by:
Dale Fye (Access MVP) earned 500 total points
ID: 36574732
Try:


strSQL = "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 " & Chr$(34) & userinput & "*" & Chr$(34) _
             & " ORDER BY ZONE.CITY, INTER.Class"
Currentdb.Execute strSQL, dbFailonError
0
 
LVL 26

Expert Comment

by:Nick67
ID: 36574760
HALIFAXNOVA SCOTIA
Halifax is the capital city of Nova Scotia
What happens with
HALIFAX NOVA SCOTIA
0
 
LVL 13

Author Closing Comment

by:Shanan212
ID: 36574774
Thanks!

My guess was right! It was this portion that was the culprit!

& userinput & "*" & Chr$(34) _
             & " ORDER BY ZONE.CITY, INTER.Class"

Open in new window


Cheers!
0
 
LVL 13

Author Comment

by:Shanan212
ID: 36574787
Nick67 - that was a "combination" that is being passed in accordance with "Origin City & Destination Province"

I am intentionally combining it to use it as an inter-reference between two tables (both has the same city and province but different values) and add the two tables under different conditions!

Thanks!
0

Featured Post

Industry Leaders: 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!

Question has a verified solution.

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

Suggested Solutions

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
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…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

759 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