Solved

sql vba excel 2010 ado query syntax

Posted on 2014-04-10
2
689 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

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

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
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…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

738 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