[R] RODBC : using and passing queries that use " in some arguments

[BOISSON, Pascal]

Dear R users,

I am trying to connect R to data that is in a Access Database but I have problem with the construction of queries using special characters. 

I am using RODBC package.

The following is working :

> MyQuery<-paste("SELECT first( (DateHeure) ) , avg(NNO3_AT322_OUT_moy) AS Cond FROM Colonne_3 ")                
> Col3<-sqlQuery(con, query=MyQuery)
> Col3
             Expr1000     Cond
1 2009-06-23 10:15:02 579.9562
> 
> MyQuery<-paste("SELECT first( format (DateHeure) ) , avg(NNO3_AT322_OUT_moy) AS Cond FROM Colonne_3 ")                
> Col3<-sqlQuery(con, query=MyQuery)
> Col3
             Expr1000     Cond
1 23/06/2009 10:15:02 579.9562

But I have problems as soon as I want to use SQL functions in my query that use a double quote (eg FORMAT() function). I have no idea on how to build my sql instruction containing "" since the escape code \" does not seem to work/be sufficient in this case. (Nota : the argument I would like to use in the following call to format is "00" )

#Obvious error :
> MyQuery<-paste("SELECT first( format(DateHeure, "00")) ) , avg(NNO3_AT322_OUT_moy) AS Cond FROM Colonne_3 ")                
Erreur : constante numérique inattendu(e) dans "MyQuery<-paste("SELECT first( format(DateHeure, "00"
             Expr1000     Cond
1 23/06/2009 10:15:02 492.0594
> 

#Trying to solve the error using the escape code \" :
> MyQuery<-paste("SELECT first( format(DateHeure, \"00\")) ) , avg(NNO3_AT322_OUT_moy) AS Cond FROM Colonne_3 ")                
> Col3<-sqlQuery(con, query=cat(MyQuery))
SELECT first( format(DateHeure, "00")) ) , avg(NNO3_AT322_OUT_moy) AS Cond FROM Colonne_3 Erreur dans odbcQuery(channel, query, rows_at_time) : 
  'getEncChar' doit être appelé sur un CHARSXP

Do you have any idea on how to proceed?

With Best regards
Pascal Boisson

___________________________________________________________________________________

Protegeons ensemble l'environnement : avez-vous besoin d'imprimer ce courrier electronique ?
___________________________________________________________________________________

Les informations figurant sur cet e-mail ont un caractere strictement confidentiel et sont exclusivement adressees au destinataire mentionne ci-dessus.Tout usage, reproduction ou divulgation de cet e-mail est strictement interdit si vous n'en etes pas le destinataire. Dans ce cas, veuillez nous en avertir immediatement par la meme voie et detruire l'original. Merci.

This e-mail is intended only for use of the individual or entity to which it is addressed and may contain information that is privileged, confidential and exempt from disclosure under applicable law. 
Any use, distribution or copying of this e-mail communication is strictly prohibited if you are not the addressee. If so, please notify us immediately by e-mail, and destroy the original. Thank you.