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

[Tudor Bodea]

Stefano,

It did work - for some reason, the date components to be used in the sqlQuery
need to be placed in between hashes (i.e., #) for them to be interpreted
correctly.

Thank you so much.

Tudor

Quoting Guazzetti Stefano <Stefano.Guazzetti at ausl.re.it>:

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


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