SQL helper function not called if a field passed is empty

Question

I have a helper function that should concatenate the Notes First Name, Notes Middle Name and Notes Surname fields from an Access table named IBM Notes.

The helper itself works fine, however if I attempt to call it on a row that has an empty entry for one of the aforementioned fields, the helper is not called.

How can I amend the SQL that is used to call the helper to ensure that it returns a result regardless of whether or not a field used as a parameter is empty?

Here is the SQL used to call the helper (DB is set to CurrentDb) -

DB.Execute "UPDATE [IBM Notes] SET [Notes Full Name] = NotesFullName([Notes First Name], [Notes Middle Name], [Notes Surname])"

And here is the helper itself -

Function NotesFullName(Optional ByVal firstName As String = "", _
                       Optional ByVal middleName As String = "", _
                       Optional ByVal surname As String = "") As String

    NotesFullName = Trim( _
        IIf(Not firstName = "", firstName & " ", "") & _
        IIf(Not middleName = "", middleName & " ", "") & _
        IIf(Not surname = "", surname, "") _
    )

End Function

score 1 · Answer 1 · answered Mar 10 '16 at 11:49

That's because empty fields are Null. Try this:

Function NotesFullName(Optional ByVal firstName As Variant = Null, _
                       Optional ByVal middleName As Variant = Null, _
                       Optional ByVal surName As Variant = Null) As Variant

    If IsNull(firstName & middleName & surName) Then
        NotesFullName = Null
    Else
        NotesFullName = Trim( _
            IIf(Not IsNull(firstName), firstName & " ", "") & _
            IIf(Not IsNull(middleName), middleName & " ", "") & _
            IIf(Not IsNull(surName), surName, "") _
        )
    End If

End Function

score 1 · Accepted Answer · answered Mar 10 '16 at 11:59

Would this query do the same without the need for a helper function?

UPDATE [IBM Notes] 
SET [IBM Notes].[Notes Full Name] = 
    TRIM(Replace(Nz([Notes First Name],"") & " " & 
    Nz([Notes Middle Name],"") & " " & 
    Nz([Notes Surname],""),"  "," "))

NB: The Replace function is replacing a double space with a single.

Edit: In this instance NZ is replacing Null fields with an empty string.

SQL helper function not called if a field passed is empty

2 Answers2