?
Solved

SQL vs VBA query comparison

Posted on 2011-09-21
7
Medium Priority
?
327 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 36

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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 49

Accepted Solution

by:
Dale Fye earned 2000 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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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

Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
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 …
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

616 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