SQL statement WHERE in vba code

**Cedicon** · 07-21-2011, 07:22 PM

Hi,

How can I manipulate with the data that I query in the following code? .. I want to make a WHERE statement in "select * from table_name" , but I don't know exactly how to write it..the code as below works finde, i just want to filter my data so as to fx. only look at the dates only in 2011...so i want to write something like "select * from table_name where date>1/1/2011", but that doesn't seem to work. I'm new to sql in vba so thank you a lot:

Sub getData()
     Dim conn As Variant
     Dim rs As Variant
     Dim cs As String
     Dim query As String
     Dim row As Integer
 
     Set conn = CreateObject("adodb.connection")
     Set rs = CreateObject("adodb.recordset")
 
            cs = "DRIVER=SQL Server;"
     cs = cs & "DATABASE=database_name;"
     cs = cs & "SERVER=server_name"
 
     conn.Open cs, "userid", "password"
      
     query = "select * from table_name"
       
     rs.Open query, conn

    row = 0
     Do Until rs.EOF
       row = row + 1
       Cells(row, 1).Value = rs.Fields("price").Value
       rs.movenext
     Loop
 
     rs.Close
     Set rs = Nothing
 
     conn.Close
     Set conn = Nothing
 
End Sub

**alansidman** · 07-21-2011, 07:56 PM

Try surrounding your date 1/1/2011 in single quotes.

Alan

**Cedicon** · 07-22-2011, 09:04 AM

Originally Posted by alansidman

Try surrounding your date 1/1/2011 in single quotes.

Alan

Thanks, that worked

However I have a problem in variable-making the date; I want to write like:

Dim startdate As Date
startdate = range("K2").value
     query = "select ID,Date,price  from table_name where Date='startdate' order by Date"

I get the error: conversion failed when converting datetime from character string. In cell K2 i have written for example 20-07-2011. Do you know any good site for manipulating with the tables in ado? i'd like to know then. thanks in advance

**alansidman** · 07-22-2011, 09:55 AM

While your date in your cell k2 looks like a date, how is it formatted? Is it a date format or text format or general.

Alan

**Cedicon** · 07-22-2011, 11:35 AM

Originally Posted by alansidman

While your date in your cell k2 looks like a date, how is it formatted? Is it a date format or text format or general.

Alan

The k2 cell is formatted as date, - or short dateformat it's called..it should be really simple i don't know what little thing i miss..or maybe i cannot include variables in my select-statement?