Solved

SQL vs VBA query comparison

Posted on 2011-09-21
7
304 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
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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Suggested Solutions

It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
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 …

867 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

22 Experts available now in Live!

Get 1:1 Help Now