[scalt]

Hi

I have an Excel sheet accessed over an ADO connection which has daily data values for a few years. I need to extract a certain month range out (5-11/May to November for instance) for all the years, and I only need one column. Luckily, there is a seperate column which has the month value (numerical) for every entry.

I have tried writing an SQL query to do this, but so far am gettin syntax errors (I am using VB.NET in VS 2005)

strQuery = "(SELECT F2, F10 FROM [Paddock$] WHERE (F2>=5) AND (F2<=11))"

As you may or may not be able to tell I have had very little experience with SQL. I have used a couple of other statements almost identical to this (except lacking the WHERE part) in the same program and they are working fine (ie the connection is fine, sheet exists, columns exist and all can be read).

Any help would be appreciated

This post has been edited by scalt: 15 January 2008 - 07:03 PM

[PsychoCoder]

If you change your query to use the [url=http://technet.microsoft.com/en-us/library/ms187922.aspx]BETWEEN Keyword[/b] like so

strQuery ="SELECT F2, F10 FROM [Paddock$] WHERE (F2 BETWEEN 5 AND 11)"

Tty something like that to see if it solves your problem. Hope it helps

[baavgai]

The SQL looks syntactically correct, it shouldn't need all those parens, though:

SELECT F2, F10 FROM [Paddock$] WHERE F2>=5 AND F2<=11

Still, some folks dig parens.

I suspect the issue may be the nature of the data. An excel cell can be anything, numeric and alpha. If it's a letter, it may be annoyed with you. Try something like this, just to test.

SELECT F2, F10 FROM [Paddock$] WHERE F2 in ('5','6','7','8','9','10','11')

Also, find out what functions you can use, you may end up doing something like this:

SELECT F2, F10 FROM [Paddock$] WHERE CInt(F2) BETWEEN 5 AND 11

Hope this helps.

[scalt]

Thanks guys. The BETWEEN statement worked perfectly once I told the connection not to treat the Excel file as a database of strings

[PsychoCoder]

Glad I could help, and glad you got it working