Naming new archive table

[jwillet1]

I have a Database in which I would like to archive data once it is now longer actively being used. I have VBA code that does everything i need it to, however I would like to name the new archive table related to the dates in the data being archived. Below is the code I am using to archive and delete the information from the main table.

Dim strSQLCreate As String
Dim strSQLDelete As String
Dim strArchiveTableName As String

strArchiveTableName = "tblTestData_" & Me.Combo0 & "Archive"

strSQLCreate = "SELECT tblTestData.*, tblPrograms.ProgramNumber " & _
    "INTO " & strArchiveTableName & " " & _
    "FROM tblPrograms INNER JOIN (tblVehicleData INNER JOIN tblTestData ON tblVehicleData.vehicleID = tblTestData.vehicleID) " & _
    "ON tblPrograms.ProgramNumberID = tblVehicleData.programNumberID " & _
    "WHERE tblPrograms.ProgramNumber = '" & Me.Combo0 & "';"

DoCmd.RunSQL strSQLCreate

DoCmd.RunSQL "ALTER TABLE " & strArchiveTableName & " ADD CONSTRAINT PrimaryKey PRIMARY KEY ( [TestID] );"

strSQLDelete = "DELETE tblTestData.*, tblPrograms.ProgramNumber " & _
    "FROM (tblPrograms INNER JOIN tblVehicleData ON tblPrograms.ProgramNumberID = tblVehicleData.programNumberID) INNER JOIN tblTestData ON tblVehicleData.vehicleID = tblTestData.vehicleID " & _
    "WHERE tblPrograms.ProgramNumber = '" & Me.Combo0 & "';"

DoCmd.RunSQL strSQLDelete

End Sub

I know it will be in the "strArchiveTableName" variable, I am just not sure how to pull the dates out of the data being moved. The TestData table holds a date and time for each test, and the earliest and latest dates is what i want to append to the table name.

For example:

Currently "tblTestData_A102Archive"​

What I want "tblTestData_A102_1/1/2012-1/1/2013"​

Hopefully I can get some help, thank you.

 

[Mihail]

I think that the answer is here:
http://office.microsoft.com/en-us/access-help/dmin-dmax-functions-HA001228826.aspx

 

[pbaldy]

You could use a DMin() and DMax() to get the two dates, using the same criteria.

 

[namliam]

it is usually better to backup into a seperate database and date the database instead of the table....

You can use a recordset or a DLookup to extract the date you require from the table.