dellehurley
Board Regular
- Joined
- Sep 26, 2009
- Messages
- 171
- Office Version
- 365
- Platform
- Windows
Hi
This function was working correctly and returns the dates when a payment is next payable. This works well except if I enter 2 dates dates which does not include a date in which a payment is due.
I need to value to set the DtAdd too if the dates are past, a variable I can use in an if statement to throw an error.
Originally I tried Null but got a Null not valid. DtAdd=Null
If I put the message box in the function upon exit function the procedure continues to run.
I tried the date as per below but neither if DtAdd="01/01/1900" then or if DtAdd is >" 01/01/2010" then will catch it.
This is the section I need help with.. (full function at bottom)
This is the entire function
Thanks Dannielle
This function was working correctly and returns the dates when a payment is next payable. This works well except if I enter 2 dates dates which does not include a date in which a payment is due.
I need to value to set the DtAdd too if the dates are past, a variable I can use in an if statement to throw an error.
Originally I tried Null but got a Null not valid. DtAdd=Null
If I put the message box in the function upon exit function the procedure continues to run.
I tried the date as per below but neither if DtAdd="01/01/1900" then or if DtAdd is >" 01/01/2010" then will catch it.
This is the section I need help with.. (full function at bottom)
VBA Code:
If StartDtPlus > StartDt Then
DtAdd = 1 / 1 / 1900
Else
DtAdd = StartDtPlus
End If
This is the entire function
VBA Code:
Function DtAdd(StartDt As Date, Freq As String, EndDt As Date) As Date
Dim StartDtPlus As Date
Dim LastPayment As Date
NowDt = Format(Now(), "dd/mm/yyyy")
Do Until CLng(StartDtPlus) >= CLng(NowDt)
If CLng(LastPayment) >= CLng(NowDt) Then Exit Do
StartDtPlus = StartDt
Select Case Freq
Case "Once Only"
MsgBox "This date is in the past. Please edit and resubmit.", vbOKOnly, "Date Past"
Exit Function
Case "Daily"
StartDtPlus = DateAdd("d", 1, StartDt)
LastPayment = DateAdd("d", -1, EndDt)
Case "Weekly"
StartDtPlus = DateAdd("ww", 1, StartDt)
LastPayment = DateAdd("d", -1, EndDt)
Case "Fortnightly"
StartDtPlus = DateAdd("ww", (2), StartDt)
LastPayment = DateAdd("d", -1, EndDt)
Case "Monthly"
StartDtPlus = DateAdd("m", 1, StartDt)
LastPayment = DateAdd("d", -1, EndDt)
Case "Quarterly"
StartDtPlus = DateAdd("q", 1, StartDt)
LastPayment = DateAdd("d", -1, EndDt)
Case "Tri Annually"
StartDtPlus = DateAdd("m", (4), StartDt)
LastPayment = DateAdd("d", -1, EndDt)
Case "Bi Annually"
StartDtPlus = DateAdd("m", (6), StartDt)
LastPayment = DateAdd("d", -1, EndDt)
Case "Annually"
StartDtPlus = DateAdd("yyyy", 1, StartDt)
LastPayment = DateAdd("d", -1, EndDt)
End Select
Loop
If StartDtPlus > StartDt Then
DtAdd = 1 / 1 / 1900
Else
DtAdd = StartDtPlus
End If
End Function