Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

sql vba excel 2010 ado query syntax

Posted on 2014-04-10
2
Medium Priority
?
714 Views
Last Modified: 2014-04-10
Hello EE,

I am having an issue getting my records out of an table in excel 2010 using ado.

This statement returns 146 records fine:

rst.Open "SELECT [IndexKey], [Details], [Name], [Amount], [Name] FROM [tbl_import] ORDER by [Name]", cnn, adOpenStatic

Open in new window


As soon as I add some criteria, it does not:

rst.Open "SELECT [IndexKey], [Details], [Name], [Amount], [Code] FROM [tbl_import] WHERE ((([Code]) Is Null)) ORDER BY [Name]", cnn, adOpenStatic

Open in new window


Anyone got the solution? Its got me stumped!

TA
0
Comment
Question by:discogs
2 Comments
 
LVL 49

Accepted Solution

by:
PortletPaul earned 2000 total points
ID: 39993266
it does not ...

a. return anything
b. return what I expect

?

I'm assuming a.

Your criteria is to search for Code IS NULL
Perhaps there aren't any records where that is true" Maybe Code looks empty but isn't?

e.g.
SELECT
      [IndexKey]
    , [Details]
    , [Name]
    , [Amount]
    , {Code}
FROM [tbl_import]
WHERE ({Code} IS NULL OR {Code} = '')
ORDER BY
      [Name];

nb: I had to substitute { } for [ ] around the word "code"
0
 

Author Comment

by:discogs
ID: 39993355
Paul
Thanks for your response.

Interesting points you make. I ran a test against the cell and realised that there is a formula inside there which is why its not returning any records.

Thanks for the tip, I am going to have to approach this a different way.

TA
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

This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

578 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