ms-access

Access change dates on other date fields based on date in first date field

I am relatively new at Access and need to find a way that when one date is selected, the other 3 date fields are updated. I am guessing a query would be best. My fields are:

Week Start Date This date will be selected from a combo box so that a wrong date is not entered. Week Finish Date (6 days after the start date) Invoice Date (9 days after the start date) Check Date (13 days after the start date)

Your assistance is appreciated.

I tried doing a query, however, the syntax I used is apparently incorrect:

SELECT DateAdd("d",6,[WeekStartDate]) AS NewDate FROM [tblStartDates]

Solution

  • Name your controls: cboStartDate txtWeekFinishDate txtcboInvoiceDate txtcboCheckDate

    Private Sub cboStartDate_AfterUpdate()
    'BM 2024-JUN-14 post 78624239 Update the other date controls after cboStartDate is updated using DateAdd "d" (days) 
   If IsDate(cboStartDate.Value) Then          
      txtWeekFinishDate.Value = DateAdd("d",6,cboStartDate.Value) 
      txtInvoiceDate.Value = DateAdd("d",9,cboStartDate.Value) 
      txtCheckDate.Value = DateAdd("d",13,cboStartDate.Value) 
   Else
      'handle bad start dates. options include:
       'msgbox cboStartDate.Value + " is an invalid date"

       'txtWeekFinishDate.Value = ""
       'txtInvoiceDate.Value = ""
       'txtCheckDate.Value = ""

       'txtWeekFinishDate.Value = "Unable to calculate. Invalid start date."
       'txtInvoiceDate.Value = "Unable to calculate. Invalid start date."
       'txtCheckDate.Value = "Unable to calculate. Invalid start date."

   End If
End Sub