Make Query

[Guest]

I am trying to code in the name of the table created from a make query to
include the date. Any suggestions on how to set the Destination table
property. I tried setting it to
= "TableName" & year(now())
but it put the text in exactly as i type it rather than evaluating. Thank
you for all the help.

 

[fredg]

I am trying to code in the name of the table created from a make query to
include the date. Any suggestions on how to set the Destination table
property. I tried setting it to
= "TableName" & year(now())
but it put the text in exactly as i type it rather than evaluating. Thank
you for all the help.

Can you make the entire table using code?

Public Sub MakeATable()
' Run a MakeTable query
Dim qName As String
qName = "TableName" & Year(Date)

DoCmd.SetWarnings False 'Optional to avoid update message

DoCmd.RunSQL "SELECT YourTable.ID, YourTable.LastName, YourTable.ADate
INTO " & qName & " FROM YourTable;"

DoCmd.SetWarnings True 'Optional if set to false above
End Sub

 

[Dale Fye]

Edward,

The concept of naming a table with a date as part of its name sets off red
star clusters in my mind. Unless you are doing this for some sort of
archiving purpose, I strongly recommend against it.

And even if you are archiving, why not just add a field to the destination
data table the indicates the year it was archived, and put all of the
different years into a single table. This will make it much easier to
analyze the data in the archive files, if that becomes necessary.

HTH
Dale