I have a table on which I run a query that I export to Excel.
Here is an example:
Item | Price | Limit_Date | ------------------------------- Carrot | 0.80 | 08/07/2015 | Salmon | 4.30 | 01/07/2015 | Biscuits | 2.40 | 15/12/2015 | Milk | 1.00 | 25/06/2015 |
And I run this code in vba:
Dim cdb as DAO.Database Dim qdf as DAO.QueryDef Set cdb = CurrentDb Const xlsxPath = "C:\Users\Tom\Documents\Foo.xlsx" Set qdf=cdb.CreateQueryDef("Omnomnomnom", _ "SELECT Item, Price FROM Food WHERE Limit_Date >= [pDate];") Set qdf=Nothing DoCmd.TransferSpreadsheet acExport, acExport, acSpreadsheetTypeExcel112Xml, "Omnomnomnom", xlsxPath, True DoCmd.DeleteObject acQuery, "Omnomnomnom"
It works well as it is, but I want to replace the parameter
[pDate] with a hardcoded date so access doesn't ask for it anymore.
Here is what I have tried so far:
DateValue("1 July 2015")with and without
Format("01/07/2015", "dd/mm/yyyy")with and without
For each of these cases the query returns everything in the table, like it doesn't take it into account the date. I already checked that my Limit_Date field is Date/Time in my table. And no errors are popping.
The query only works if I keep
[pDate] and I manually enter
01/07/2015 when access prompts me to.