R help - Aug 2008 - sqlQuery with date attribute

Hello R users,

I would like to use an integer and a date as attributes in sqlQuery,  
and these arguments
are defined in my function.
I guess this is clearer comparing to my first post.

Here is my function:

GetReturn<-function(code,date)
{
db<-"C:/Test.mdb"
channel<-odbcConnectAccess(db)
ssql<-paste("select * from tblCalendarDate Where CalendarID  
=",code,"and DateRebal >=",date)
print(ssql)    # so as i can see what ssql contains
mydata<-sqlQuery(channel,ssql)
mydata
}
This is the content of my table tblCalendarDate:
CalendarID	DateRebal
1	29/09/2006
1	10/10/2006
1	20/10/2006
1	31/10/2006
1	10/11/2006
1	20/11/2006
1	30/11/2006
1	08/12/2006
1	20/12/2006
1	29/12/2006
1	10/01/2007
1	19/01/2007
1	31/01/2007
1	09/02/2007
1	20/02/2007
1	28/02/2007
1	09/03/2007
1	20/03/2007
1	30/03/2007
2	31/05/2006
2	30/06/2006
2	31/07/2006
2	31/08/2006
2	29/09/2006
2	31/10/2006
2	30/11/2006
2	29/12/2006
2	31/01/2007
2	28/02/2007
2	30/03/2007
2	30/04/2007
2	31/05/2007
2	29/06/2007
2	31/07/2007
2	31/08/2007
2	28/09/2007
2	31/10/2007
2	30/11/2007
2	28/12/2007

Actually, the channel is open but the query on the table did not  
perform the query correctly, here is the
result of the function when i run GetReturn(1,"2007-03-01") for
example:
> GetReturn(1,"2007-03-01")
[1] "select * from tblCalendarDate Where CalendarID = 1 and DateRebal  
 >= 2007-03-01"
    CalendarID  DateRebal
1           1 2006-09-29
2           1 2006-10-10
3           1 2006-10-20
4           1 2006-10-31
5           1 2006-11-10
6           1 2006-11-20
7           1 2006-11-30
8           1 2006-12-08
9           1 2006-12-20
10          1 2006-12-29
11          1 2007-01-10
12          1 2007-01-19
13          1 2007-01-31
14          1 2007-02-09
15          1 2007-02-20
16          1 2007-02-28
17          1 2007-03-09
18          1 2007-03-20
19          1 2007-03-30

The command paste worked for the CalendarID argument but not for the  
second argument DateRebal, I still
have the records where the date is < 2007-03-01.

Thank you for helping.

A.Many

Hi Many
> GetReturn<-function(code,date)
> {
> db<-"C:/Test.mdb"
> channel<-odbcConnectAccess(db)
> ssql<-paste("select * from tblCalendarDate Where CalendarID 
> =",code,"and DateRebal >=",date)
> print(ssql)    # so as i can see what ssql contains
> mydata<-sqlQuery(channel,ssql)
> mydata
> }
[snip]
> This is the content of my table tblCalendarDate:
> CalendarID    DateRebal
> 1    29/09/2006
> 1    10/10/2006
> 1    20/10/2006
> 1    31/10/2006
> 1    10/11/2006
> 1    20/11/2006
>
> Actually, the channel is open but the query on the table did not 
> perform the query correctly, here is the
> result of the function when i run GetReturn(1,"2007-03-01") for
example:
Something with the formatting of the date goes wrong as I think. In the 
table tblCalendarDate you have it
like *29/09/2006* but in your function you have it as *"2007-03-01"*. 
Dig deeper by experimenting with
the dates format. You can experiment in Access itself to see what kind 
of dates Access accepts.

s.