[R] Query an Access database based on a date attribute

[Tudor Bodea]

Dear useRs,

I would like to query an Access database through R based on a date attribute
but, unfortunately, I fail to do so. For example, the table test_table of the
test.mdb looks like:

   ID         cd  market competitor         dd price
1   1 2007-11-20 atl-bos      delta 2007-11-20   210
2   2 2007-11-20 atl-bos      delta 2007-11-21   190
3   3 2007-11-20 atl-bos      delta 2007-11-22   180
4   4 2007-11-20 atl-bos     united 2007-11-20   205
5   5 2007-11-20 atl-bos     united 2007-11-21   195
6   6 2007-11-20 atl-bos     united 2007-11-22   175
7   7 2007-11-20 atl-sfa      delta 2007-11-20   350
8   8 2007-11-20 atl-sfa      delta 2007-11-21   320
9   9 2007-11-20 atl-sfa      delta 2007-11-22   300
10 10 2007-11-20 atl-sfa         aa 2007-11-20   340
11 11 2007-11-20 atl-sfa         aa 2007-11-21   320
12 12 2007-11-20 atl-sfa         aa 2007-11-22   300

In this context, I try to get all the records for which market is atl-bos,
competitor is delta and dd is 2007-11-20 (first record above). To do this I used

># channel <- odbcConnectAccess("test.mdb")
>res <- sqlQuery(channel, "select * from test_table where market = 'atl-bos'
and competitor = 'delta' and dd = 2007-11-20")

but the result seems to be incorrect.

> res
[1] ID         cd         market     competitor dd         price
<0 rows> (or 0-length row.names)

Does any of you know what it is going on? I suspect that the last expression in
the select statement is not executed/interpreted correctly and, as a
consequence, the query leads to the above empty set.

Thank you.

Tudor

--
Tudor Dan Bodea
Georgia Institute of Technology
School of Civil and Environmental Engineering
Web: http://www.prism.gatech.edu/~gtg757i