Solved

sql vba excel 2010 ado query syntax

Posted on 2014-04-10
2
676 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 48

Accepted Solution

by:
PortletPaul earned 500 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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

920 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

16 Experts available now in Live!

Get 1:1 Help Now