Create Table As Select Where - Compile Error using current Database

Question

Trying to crate a new table: [PickupDonTbl] in an existing non-relational Db by extracting all records from table: [Donation_Data] where the Pickup Date = current date.

CREATE TABLE CurrentDb.PickupDonTbl AS SELECT * from Donation_Data WHERE [Pickup Date] = Date()

When I type in code I get CurrentDb "highlighted" with Compile error: Expected:end of error.

I then want to export this table to EXCEL using:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "PickupDonTbl", "C:\Users\dads\BlindCtr\Donation Data.xlsx", True

(I have tested this part and it works)

All this code is initiated from a form in this Db with one command button "On Click"

Inserted "SELECT * PickupDonTbl from Donation_Data WHERE [Pickup Date] = Date"...got new error - Compile error: Expected:Case. — Michael
Is there a particular reason why you need to create a temporary table instead of just saving your SELECT query and then using TransferSpreadsheet to export the query instead of the table? — Gord Thompson
Yes...this is being developed for visually impaired persons to use with a "voice reader" in background. They cannot navigate with a mouse, so I was setting up a form with a one command button click that would ask them for a "pickup date" and on entry would generate an excel spread sheet for another EXCEL/VBA application by a non-impaired person. — Michael
Doesn't matter. Your VBA code behind the button can just do DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "YourSavedSelectQueryName", ... instead of creating the temporary table and using DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "PickupDonTbl", .... — Gord Thompson
Not sure how to set up a query where a visually impaired person could enter a date and "click" resulting in the query run with [Pickup Date] = "date entered" and then automatically export as an EXCEL file — Michael

Gord Thompson Gord Thompson · Accepted Answer · 2015-02-17T21:41:01

how to set up a query where a visually impaired person could enter a date and "click" resulting in the query run with [Pickup Date] = "date entered" and then automatically export as an EXCEL file

Create a saved query named "PickupDonQry" in Access as

PARAMETERS [Enter Pickup Date] DateTime;
SELECT Donation_Data.*
FROM Donation_Data
WHERE (((Donation_Data.[Pickup Date])=[Enter Pickup Date]));

then just export that query from your VBA code

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "PickupDonQry", _
        "C:\Users\dads\BlindCtr\Donation Data.xlsx", True

Access willl prompt for the value of the "Enter Pickup Date" parameter and then export the result to the specified Excel file.

Create Table As Select Where - Compile Error using current Database

1 Answers