I am trying to implement the DateAdd function into my Microsoft Access 2010 form. I have a form that when the user inputs the date (property name called "txtStartDate", then I want the output in a different part to be 18 calendar days in the future (property name called "textEndDate").
I know the format will be
=DateAdd("d", 18, txtStartDate)
but how do I write it in VBA code to implement?
It sounds like you wish to set it after an update. Thus, use the AfterUpdate event of the start date:
Private Sub txtStartDate_AfterUpdate()
Dim EndDate As Variant
If IsDate(Me!txtStartDate.Value) Then
EndDate = DateAdd("d", 18, Me!txtStartDate.Value)
Else
EndDate = Null
End If
Me!txtEndDate.Value = EndDate
End Sub
Hope this helps somewhat, two declarations for the original date and the new date. Msgbox then shows them both here on different lines:
sub addingDate()
Dim txtStartDate As Date
Dim txtEndDate As Date
txtStartDate = Now 'Or wherever you get this info from
txtEndDate = DateAdd("d", 18, txtStartDate)
MsgBox (txtStartDate & vbNewLine & txtEndDate)
End Sub
It sounds like you're implicitly converting a textbox value from a String to a Date. You might want to add some checks like:
If IsDate(txtStartDate) Then
And you'll want to be particularly conscious of the behavior should your code be running on systems that have varying Regional Date settings for American/English/Japanese dates. That might require using a date-picker or some other date-validation on the control itself.
You don't need VBA to do that, only an expression as ControlSource for that other textbox:
=DateAdd("d", 18, [txtStartDate])
Try This:
= DateAdd("d", 18, cdate(txtStartDate)
=DateAdd("d",18, txtStartDate)