DateAdd Function error won't accept Null as 0 or ""

[dellehurley]

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)

If StartDtPlus > StartDt Then
        DtAdd = 1 / 1 / 1900
    Else
        DtAdd = StartDtPlus
    End If

This is the entire function

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

Thanks Dannielle

 

[Alex Blakenburg]

We probably need some sample data with expected result in an XL2BB to answer this but does this do what you are trying to do ?

DtAdd = #1/1/1900#

 

[dellehurley]

We probably need some sample data with expected result in an XL2BB to answer this but does this do what you are trying to do ?

DtAdd = #1/1/1900#

Brilliant. Magic what a couple of # can do.
? Thanks

 

[Alex Blakenburg]

Haha. Glad I could help.

 

[smozgur]

Brilliant. Magic what a couple of # can do.
? Thanks

The marked solution has been switched accordingly.

@dellehurley - In your future questions, please mark the post as the solution that answered your question to help future readers. No further action is needed for this thread.

 

[dellehurley]

The marked solution has been switched accordingly.

@dellehurley - In your future questions, please mark the post as the solution that answered your question to help future readers. No further action is needed for this thread.

Honestly I thought I did, sorry about that.