Function won't run in a Query

[Paul H]

I've written a short function to concatenate a full name from its parts. I did it mostly to practice writing functions, but thought it would come in handy. I stuck it in a new module. Here it is.

 
Public Function ApplicantName(FName As String, MI As String, LName As String)

    ApplicantName = FName & IIf(MI = "", "", " " & MI & ".") & " " & LName
 
End Function

When I call this from a form to populate a text box, it works just fine. When I put it in the query I get a Data Type Mismatch in criteria expression. Here's the SQL.

SELECT SSN, Applicant.Appl_Fname, Appl_MI, Applicant.Appl_Lname, ApplicantName([appl_FName],[appl_MI],[appl_LName]) AS Full_Name

FROM Applicant

GROUP BY SSN, Appl_Fname, Appl_MI, Appl_Lname, ApplicantName([appl_FName],[appl_MI],[appl_LName]);

This is a RowSource for a combo box.


Appl_Name: ApplicantName([appl_fname],[appl_mi],[appl_LName])

[ranman256]

You dont use IIF in code. its for queries.
use
IF...THEN
ELSE
ENDIF

[Paul H]

So here is my revised code which works exactly the same as my old code. Works fine from the form. Does not work in the query.

Public Function ApplicantName(FName As String, MI As String, LName As String)    
            
    If MI = "" Then
        MI = " "
    Else
        MI = " " & MI & "." & " "
    End If
    
    ApplicantName = FName & MI & LName 
 
End Function

[ItsMe]

Maybe
GROUP BY SSN, Appl_Fname, Appl_MI, Appl_Lname, Full_Name

or
GROUP BY SSN, Appl_Fname, Appl_MI, Appl_Lname, ApplicantName([appl_FName],[appl_MI],[appl_LName]) AS Full_Name

[Paul H]

This as well, I took the GROUP BY out of my query and it runs, but returns an error on every line that has no middle initial (MI). Again, this does not happen in the form where it seems to work perfectly. I'm not sure why it would work in one place, but not the other.

[Paul H]

FYI: This produces the same results. Fine in the form, errors in the query.

Public Function ApplicantName(FName As String, MI As String, LName As String)
    If MI = "" Or IsNull(MI) Then
        MI = " "
    Else
        MI = " " & MI & "." & " "
    End If
    
    ApplicantName = FName & MI & LName
  
End Function

[ItsMe]

I see. The error is happening because you are passing null to a variable that cannot handle nulls.

Try
Public Function ApplicantName(FName As String, MI As Variant, LName As String)

Or make middle name optional. With the optional parameter you should be able to employ the Nz() Function on the MI field within your query.
Public Function ApplicantName(FName As String, LName As String, Optional MI As String = "")

[Paul H]

That did it. To account for all possibilities I made all three variables Variants and the query seems to work in all cases. I'm still confused why the function did not produce an error when called by the form. This is a very old database I am upgrading and I'm afraid there really are ghosts in this machine.

Thanks for hanging in,

Paul

p.s. I'm going to leave this open briefly in case there are any other comments, but I'll come back later and mark it solved.

[June7]

Good question why the textbox expression works. Are you are pulling values from columns of combobox? Columns of combobox are always text, not actually numbers or dates or Null. So if there is nothing there, it is an empty string.

[Paul H]

From the form I am calling the function from AfterUpdate event of a combo box, reading those columns and populating a text box. I'm more than a little confused about the difference between nulls and empty strings and how to tell the difference. They both seem like a whole lot of nothing.

[June7]

Maybe Allen can clarify http://allenbrowne.com/vba-NothingEmpty.html

[Paul H]

I will read that. I just finished reading this which makes short work of explaining Nulls, Empty Strings and why Variants work with both.

http://www.everythingaccess.com/tuto...C-Zero-and-ZLS

Thanks for your help.

Paul

[June7]

Both links essentially the same info by Allen Browne.