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

[Prof Brian Ripley]

This is a question about SQL, or more precisely, Microsoft's peculiar 
dialect of SQL.  You haven't even mentioned (let alone credited) package 
RODBC which you appear to be using.

In SQL queries you need to quote numeric values if you want them to be 
treated as character.  Why did you quote 'alt-bos' and not '2007-11-20' ? 
As I recall, some DBMSs need date values quoted and some do not, so try 
quoting.

On Mon, 12 Nov 2007, Tudor Bodea 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.
>
>> 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

-- 
Brian D. Ripley,                  ripley at stats.ox.ac.uk
Professor of Applied Statistics,  http://www.stats.ox.ac.uk/~ripley/
University of Oxford,             Tel:  +44 1865 272861 (self)
1 South Parks Road,                     +44 1865 272866 (PA)
Oxford OX1 3TG, UK                Fax:  +44 1865 272595