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

[Guazzetti Stefano]

It seems that Access needs that you surround the dates with a # symbol.
You probably need something like.

res <- sqlQuery(channel, "select * from test_table where market = 'atl-bos'
			 and competitor = 'delta' and dd = #2007-11-20#")
Hope this helps,

Stefano
-----Messaggio originale-----
Da: r-help-bounces at r-project.org
[mailto:r-help-bounces at r-project.org]Per conto di Mark Wardle
Inviato: martedì 13 novembre 2007 11.59
A: Tudor Bodea
Cc: r-help at stat.math.ethz.ch
Oggetto: Re: [R] Query an Access database based on a date attribute


On 12/11/2007, Tudor Bodea <gtg757i at mail.gatech.edu> wrote:
> 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.


Dear Tudor,

Try running your query in Microsoft Access/Query first. Once it works,
then move to R/RODBC...

I don't use MS Access routinely in this way, but presumably there is a
problem with your SQL.

Best wishes,

Mark
-- 
Dr. Mark Wardle
Specialist registrar, Neurology
Cardiff, UK

______________________________________________
R-help at r-project.org mailing list
https://stat.ethz.ch/mailman/listinfo/r-help
PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
and provide commented, minimal, self-contained, reproducible code.