Invalid use of Null

[Seth Schrock]

I'm trying to use the Nz() function in a replace function, but I'm getting an Invalid use of Null error and I don't know why. I thought that Nz() was supposed to take care of that. Here is what I have:

With Forms!frmIncidents
     strLegalWording = Replace(strLegalWording, "%Day%", Nz(Day(!IncDate), "__________") & "")
     strLegalWording = Replace(strLegalWording, "%Month%", Nz(MonthName(Month(!IncDate)), "__________") & "")
     strLegalWording = Replace(strLegalWording, "%Year%", Nz(Year(!IncDate), "__________") & "")
 
End With

I tried taking out the Day() function out of the Nz() function in line 2 so that the line looks like this:

strLegalWording = Replace(strLegalWording, "%Day%", Nz(!IncDate, "__________") & "")

and it works (however, I get the whole date instead of just the day). Is there something about having the Day() function in there that makes it so that I can't use this in the Nz() function?

[Stewart Ross]

Hi Seth.

Nz on the outside of your expression cannot prevent the date functions from failing when the !IncDate argument is null on the inside of your expression.

As you are repeating the same operation three times I suggest separating the replace component into a single function which can guard against null dates more easily:

Public Function fReplaceDateText(LegalText As String, ByVal WhichPart As String, WhatDate As Variant) As String
    Dim strSubstitute As String
    Const cNoValueText = "____________"
    If IsNull(WhatDate) Then
        strSubstitute = cNoValueText
    Else
        Select Case WhichPart
            Case "%Day%":
                strSubstitute = CStr(Day(WhatDate))
            Case "%Month%":
                strSubstitute = CStr(Month(WhatDate))
            Case "%Year%":
                strSubstitute = CStr(Year(WhatDate))
            Case Else:
                strSubstitute = cNoValueText
        End Select
    End If
    fReplaceDateText = Replace(LegalText, WhichPart, strSubstitute)
End Function

You can then call this function to do the replacements for you:

With Forms!frmIncidents
    strLegalWording = fReplaceDateText(strLegalWording, "%Day%", !IncDate)
    strLegalWording = fReplaceDateText(strLegalWording, "%Month%", !IncDate)
    strLegalWording = fReplaceDateText(strLegalWording, "%Year%", !IncDate)
End With

-Stewart

[Seth Schrock]

Works perfectly! I see now what the error was so I should be able to work around this in the future. Thanks Stewart.