How To Copy Table Appending Date to Destination Table Name

[whimbrel]

I am trying to copy a table, appending the date to the destination table name in VB code.
However I get a Type Mismatch error.

Here's the troublesome code:

Dim MyDay, MyMonth, MyYear, MyDate, MyNewTable As String
MyDay = Format(Date, "dd")
MyMonth = Format(Date, "mm")
MyYear = Format(Date, "yyyy")
MyDate = "_" & MyDay & "_" & MyMonth & "_" & MyYear
MyNewTable = "OldConsignmentNumbers" & MyDate
DoCmd.CopyObject MyNewTable, acTable, "Consignment Numbers"

in Microsoft Office Access 2003 (11.6566.6568) SP2.

Can anyone help?
Thanks.

 

[boblarson]

I am trying to copy a table, appending the date to the destination table name in VB code.
However I get a Type Mismatch error.

Here's the troublesome code:

Dim MyDay, MyMonth, MyYear, MyDate, MyNewTable As String
MyDay = Format(Date, "dd")
MyMonth = Format(Date, "mm")
MyYear = Format(Date, "yyyy")
MyDate = "_" & MyDay & "_" & MyMonth & "_" & MyYear
MyNewTable = "OldConsignmentNumbers" & MyDate
DoCmd.CopyObject MyNewTable, acTable, "Consignment Numbers"

in Microsoft Office Access 2003 (11.6566.6568) SP2.

Can anyone help?
Thanks.

Try this, as trying to assign a date field to a text string may be causing your problem:

    MyDay = CStr(Format(Date, "dd"))
    MyMonth = CStr(Format(Date, "mm"))
    MyYear = CStr(Format(Date, "yyyy"))

 

[RuralGuy]

With this syntax:
Dim MyDay, MyMonth, MyYear, MyDate, MyNewTable As String
...everything but the MyNewTable variable is defined as a Variant.
Try using:
Dim MyDay As String, MyMonth As String, MyYear As String, MyDate As String
Dim MyNewTable As String

 

[whimbrel]

Thanks for the suggestions, guys. I have tried both but unfortunately still get the type mismatch error.

It seems such an innocuous piece of code, too.

Any other suggestions gratefully accepted.

Regards,
whimbrel.

 

[RuralGuy]

From VBA Help:

expression.CopyObject(DestinationDatabase, NewName, SourceObjectType, SourceObjectName)

It looks like you might have skipped one of the arguments.

 

[whimbrel]

Thanks RuralGuy, you're right. The problem wasn't date formatting it was the syntax of the copyobject expression. Although I'm copying the table to the same database I still needed to signify the presence of the argument by inserting a comma, like this:

DoCmd.CopyObject , MyNewTable, acTable, "Consignment Numbers"

Regards,
whimbrel.

 

[RuralGuy]

Glad I could help.

 

[vito1010]

One line:
DoCmd.CopyObject , "Backup Name" & Format(Date, " mm/dd/yy"), acTable, "File to Backup"

 

[The_Doc_Man]

@vito1010 - thanks for the comment, but please note that the thread's previous entry was in 2007. I think the problem has been either solved or abandoned.

 

[Pat Hartman]

Not sure why you wouldn't just use the Format() function:

MyDate = Format(Date(), "yyyy_mm_dd")

One line of code + the name will be sortable by date. If you don't ever want to sort by date then format as mdy or whatever floats your boat.

And finally, creating tables with data by date is spreadsheet thinking. In a relational database, you would only archive data if there was a space issue.