Guazzetti Stefano
2007-Nov-13 11:41 UTC
[R] R: Query an Access database based on a date attribute
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 Bodea
2007-Nov-13 14:48 UTC
[R] R: Query an Access database based on a date attribute
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