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 <[EMAIL PROTECTED]>: > 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: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] conto di Mark Wardle > Inviato: martedì 13 novembre 2007 11.59 > A: Tudor Bodea > Cc: [EMAIL PROTECTED] > Oggetto: Re: [R] Query an Access database based on a date attribute > > > On 12/11/2007, Tudor Bodea <[EMAIL PROTECTED]> 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@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 ______________________________________________ R-help@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.
