4 Replies - 1234 Views - Last Post: 16 January 2008 - 06:19 PM Rate Topic: -----

#1 scalt  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 63
  • View blog
  • Posts: 342
  • Joined: 22-November 07

Help With a Different SQL Query

Post icon  Posted 15 January 2008 - 06:56 PM

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


Is This A Good Question/Topic? 0
  • +

Replies To: Help With a Different SQL Query

#2 PsychoCoder  Icon User is offline

  • Google.Sucks.Init(true);
  • member icon

Reputation: 1642
  • View blog
  • Posts: 19,853
  • Joined: 26-July 07

Re: Help With a Different SQL Query

Posted 15 January 2008 - 07:42 PM

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 :)
Was This Post Helpful? 0
  • +
  • -

#3 baavgai  Icon User is offline

  • Dreaming Coder
  • member icon

Reputation: 5937
  • View blog
  • Posts: 12,862
  • Joined: 16-October 07

Re: Help With a Different SQL Query

Posted 16 January 2008 - 03:25 AM

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. :P

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.
Was This Post Helpful? 0
  • +
  • -

#4 scalt  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 63
  • View blog
  • Posts: 342
  • Joined: 22-November 07

Re: Help With a Different SQL Query

Posted 16 January 2008 - 03:39 PM

Thanks guys. The BETWEEN statement worked perfectly once I told the connection not to treat the Excel file as a database of strings
Was This Post Helpful? 0
  • +
  • -

#5 PsychoCoder  Icon User is offline

  • Google.Sucks.Init(true);
  • member icon

Reputation: 1642
  • View blog
  • Posts: 19,853
  • Joined: 26-July 07

Re: Help With a Different SQL Query

Posted 16 January 2008 - 06:19 PM

Glad I could help, and glad you got it working :D
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1