VBA code to append the current date to a table name

[BridgestoneGal]

I have a table T_Statistical_Forecast and would like to append the current month to the table name.

For example: T_Statistical_Forecast_April
or T_Statistical_Forecast_4

Please advise me.

Thanks.

 

[THoey]

You do realise that this is probably not a good idea, and I think that is why it is so difficult to figure out how to do? Just think of what you are going to have to do to get all of your forms, queries, and reports to work off that table. Is there a reason you need to do this? How many records do you plan to have in a table for a month? Terry
**************************
* General Disclaimor - Please read *
**************************
Please make sure your post is in the CORRECT forum, has a descriptive title, gives as much detail to the problem as possible, and has examples of expected results. This will enable me and others to help you faster...

 

[BridgestoneGal]

Thanks for your recommendation. I believe this is the answer I was looking for. I found it on a google search.



Action: Rename
New Name: "Table1_" & Format$(Date, "MMDD&quot
Object Type: "Table"
Old Name: "Table1"

Save the macro, then run it. Notice the funky table name?!?
Now to fix this, click on the new Macro, then choose from the Access menu:
Tools | Macro | Convert Macros to Visual Basic
This creates a new hunk of code in your Modules called "Converted Macro- Macro1".
Double-click on the new code. Don't worry, it won't automatically run the code!
Then edit the code and take out the quote marks until it looks like the line of code above...

Option Compare Database
Option Explicit

'------------------------------------------------------------
' Macro1
'------------------------------------------------------------
Function Macro1()

DoCmd.Rename """Table1_"" & Format$(Date, ""MMDD"&quot", acTable, "Table1"

End Function

Finally, change your original macro to the following:

Action: RunCode
Function Name: Macro1()

Then, when you double-click your original macro, it will call the piece of code.

I just double-clicked it and my table was renamed to "Table1_0205".

 

[THoey]

BG, I am glad you found a solution, but still think you are headed for trouble down the road. As a friend of mine's siganture said "There is never enough time to do it right, but plenty of time to do it over"... Hope you are not doing it this way just to get around a design flaw. Unless you are talking about an extraordinary amount of data each month, I can't see a good reason to do it this way. But, then again, I am not all wise or knowing...

Again, glad you found your answer... Terry
**************************
* General Disclaimor - Please read *
**************************
Please make sure your post is in the CORRECT forum, has a descriptive title, gives as much detail to the problem as possible, and has examples of expected results. This will enable me and others to help you faster...

 

[BridgestoneGal]

Terry,

Maybe you can recommend I do something else. I know it says programmer next to my name, but I program in MIMI (a proprietary language) not VBA.

Here's what I am trying to do.

I want to create a report to measure forecast accuracy using the following 3 text files:

Actual Orders - what the customer truly ordered
Statistical Forecast - what MIMI recommended as the forecast
Working Forecast - what the forecaster overrode as the forecast


Each of these files, when imported contain about 6000 lines.

I want to make the following 2 comparisons:
Actual vs Statistical Forecast
Actual vs Working Forecast

I will only save one actual file (which will be appended with new monthly totals) and I need to save a rolling 12 months of the other two files.

The user needs to be able to see both of the comparisons over 12 months.

This will be a monthly report, and I need to attach the current month name to the table name upon import.

Please let me know if you have any idea on how to handle this.