Solved

SQL vs VBA query comparison

Posted on 2011-09-21
7
305 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
7 Comments
 
LVL 120

Expert Comment

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

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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
LVL 47

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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

813 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now