Dates in MS Access aren't escaped properly.

[erikvona]

Escaping dates when using a connection to MS Access goes wrong

Access uses octothorpes to delimit dates, and requires dates to be formatted either YYYY-MM-DD or MM/DD/YYYY. Optionally with hh:mm:ss as time, timezones are not supported.

escape(Sys.Date(), con = simulate_access())
# Expected: #2021-03-08#, or: #03/08/2021#
# Returned: '2021-03-08'
escape(Sys.time(), con = simulate_access())
# Expected: #2021-03-08 09:30:30#
# Returned: '2021-03-08T09:30:30Z'

The relevant docs are here concerning how to use dates in queries in MS Access.

To work around the issue, I currently use the following function. However, it doesn't handle collapse/parentheses, only single values.

escape_access <- function(x){
  if(inherits(x, "Date")){
    return(sql(ifelse(is.na(x), "NULL", format(x, "#%F#"))))
  }
  else if(inherits(x, "POSIXt")){
    return(sql(ifelse(is.na(x), "NULL", format(x, "#%F %T#"))))
  }else{
    escape(x, con = simulate_access())
  }
}