Search code examples
datems-accessvbaauto-increment

How to Auto Increment a Date Field in an Access Table

I have an Access table with 3 columns: an AutoNumber "WeeklyID", a fgn key "CampaignID", and a date field "WeekEnded". It looks like this. What I need to accomplish is a method to automatically add 7 days to the date field for each record sharing the same CampaignID.

So, if there are 3 records with the same Campaign ID and the date associated with the first record is 4/1/2016, I want the date on the second record to be 4/8/2016, and the date on the third record to be 4/15/2016.

I am not sure if this is possible to accomplish via the DateAdd function in Access, so I'm wondering if there is any VBA that can get the job done?

Solution

  • If this is a one-time thing then it's probably easiest just to take Gustav's suggestion and create a VBA routine that loops through a DAO.Recordset to update the dates. However, since you're using Access_2013 I thought I'd mention another approach: an event driven Data Macro.

    If you define the following Before Change data macro on the table

    BeforeChange.png

    then any time you add a new row for an existing [CampaignID] with a NULL [WeekEnded] column the data macro will populate [WeekEnded] with the value for the following week.

    If you create the macro on an existing table and want to update the existing rows then you could simply run the UPDATE query

    UPDATE TheTable SET CampaignID = CampaignID WHERE WeekEnded IS NULL;