Dynamic dates in SQL queries in R

dynamic_query_date.r

# Pick some dynamic date - here's the start of the current month
start_date <- format(Sys.Date(), "%Y-%m-01")

# Then use paste0() to construct a query that includes it
# Here's an example to get all the positive QFTs this month
# (I have probably misremembered the field names).
# I'm pretty sure you have to put a # on each side of the
# date in order for Access to recognize it as a date.
test_query <- sqlQuery(tbdb, 
    paste0("SELECT person_id, test_date
            FROM QFT_View
            WHERE test_date >= #",
                start_date,
            "# AND result = 'Positive'"
    )
)