Solved

SQL vs VBA query comparison

Posted on 2011-09-21
7
301 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 119

Expert Comment

by:Rey Obrero
Comment Utility
upload a .mdb version of the db
0
 
LVL 33

Expert Comment

by:Norie
Comment Utility
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
Comment Utility
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
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)

 
LVL 47

Accepted Solution

by:
Dale Fye (Access MVP) earned 500 total points
Comment Utility
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
Comment Utility
HALIFAXNOVA SCOTIA
Halifax is the capital city of Nova Scotia
What happens with
HALIFAX NOVA SCOTIA
0
 
LVL 13

Author Closing Comment

by:Shanan212
Comment Utility
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
Comment Utility
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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
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…

762 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

5 Experts available now in Live!

Get 1:1 Help Now