Add column named current date

user_13757164 0
Viewed 57

I'm creating an Access database to try to track average cost of inventory. I want to make it to where when a button is clicked on the form a column is added to the table that is named with the current date and the word Quantity and another column is added with the current date and the word Price. I can't just add columns named quantity and price because I am hoping to be able to use this over time. So I want to know on mm/dd/yyyy we bought x widgets for y dollars a piece. Then a week later I want to be able to hit the same button and add more lines with the new current date. Is this even possible? I'm using Microsoft Access 2016. I've tried the following with some variations based on what I've researched but nothing seems to work. AvgCost is the table name. Also I'm very very new at this. 

Private Sub Record_Button_Click()
ALTER TABLE AvgCost ADD COLUMN Quantity_DATE()
End Sub

I keep getting an error message reading "Compile Error: Expected: ENd of Statement and it highlights AvgCost

ms-access vba
1 Answers

Can't run an SQL action in VBA like that. Certainly won't create a field with a date in field name. Need to call a command and concatenate date value.

CurrentDb.Execute "ALTER TABLE AvgCost ADD COLUMN Quantity_" & Format(DATE(), "yyyymmdd") & " Number"

However, this really is poor design. Instead of implementing procedure that routinely changes database structure, should be adding records.

user_76071900
Related