setting date format.

[Bruce927]

I am useing the copyobject command in VB, and in the newname section of the code I am useing "tblStuff_"& date. To add todays date to the table name for archive purposes. How can I get the date to show up as mmddyyy format in stead of the defalt of mmdyyyy? The latter part will not allow the tables to appear in order of date, for example a table name ending in 8/1/2011 the next one ending in 8/15/2011 follow, the table with the date of 8/8/2011 follows after that, if two digits are used for the date it would keep them in order.

[TheSmileyCoder]

You would need to use the Format command on your date.

Try something like this:

"tblStuff_" & Format(date,"mmddyyyy")

and if that doesn't work, have a look at the help file on Format.

[Bruce927]

Thank you, this worked great. Here is the final [code] "tblstuff_"& format (Date-8,"mm/dd/yyyy")[code, gives me tblstuff_08/24/2011. this is the format I was looking for. The reason I subtracted 8 was to give me the date for close of businness from the week before.
Thank you again!
Bruce

[TheSmileyCoder]

Thats good to hear. Im happy that it worked for you.

Just one note, which you can consider. I would advice against using special characters such as / + - in table or even field names. If I need a special character I usually use underscore _ .
The reasons are that its usually easier to write and maintain code that does not involve the special charecters in table/field names.

TheSmileyCoder

[NeoPa]

Another very important point here is that it is a much (A really extreme much) better idea to archive your data into a single table than creating loads of different tables for what is fundamentally the same job. Simply add a [Date Archived] field into your table to allow recognising the data in the future.

If you must create tables with dates in that you want to be in order (and this is equally, in fact more, true for files) then I would suggest a format of yyyymmdd instead. That way they are all automatically kept in the same order as the dates.