Adding Date to the table name in a Make Table Query

[Guest]

Before I delete the data in a particular table, I need to copy it into a
table with the prefix of today's date. Is there a way to create a make table
query that does this? Is there a better way? I've tried things like:

SELECT tblCO_Order_20.*
INTO Format(Now(),"yyyymmdd") & tblCO_Order_Level20
FROM tblCO_Order_20;

But no luck. I can't find a macro command to copy a table and save it with
a different name. What other options do I have?

 

[fredg]

Before I delete the data in a particular table, I need to copy it into a
table with the prefix of today's date. Is there a way to create a make table
query that does this? Is there a better way? I've tried things like:

SELECT tblCO_Order_20.*
INTO Format(Now(),"yyyymmdd") & tblCO_Order_Level20
FROM tblCO_Order_20;

But no luck. I can't find a macro command to copy a table and save it with
a different name. What other options do I have?

As your Make Table query, use:

SELECT tblCO_Order_20.*
INTO ATempTableName
FROM tblCO_Order_20;

Then use VBA to change the table name to the one you want:

Public Sub ChangeTableName()
DoCmd.Rename Format(Date, "yyyymmdd") & "tblCO_Order_Level20",
acTable, "ATempTableName"
End Sub

 

[Guest]

The Date need to be in the Select section

Try

SELECT tblCO_Order_20.* , Format(Now(),"yyyymmdd") AS TodayDate
INTO tblCO_Order_Level20
FROM tblCO_Order_20

 

[Guest]

Sorry, ignore my post.
I misundestood the question, Fred got your answer