Joining First, middle, last name in a qry expression help

[d9pierce1]

HI all,
I have attached a moch db and i am trying to do a full name concatenation with some If statements in it possibly? Not really sure. I put what i seek in memo on form open. Could someone take a look at it and let me know if possible and any ideas maybe? db is just a sample, very cut and clear. One Tbl, One Qry, and One Frm...and small data amount....

Thanks
Dave

Contact.zip

[davegri]

Had to create a UDF which had to go into a module.
The query calls the UDF to get the proper format for the names.

Contact-davegri-v01.zip

[moke123]

In addition to Daves solution, When entering names, here's a helpful function to format them in proper case. http://access.mvps.org/access/strings/str0008.htm

When entering a middle name or initial, I usually test for the length. If it is 1 (just an initial) then I concatenate a "." on the end.

Private Sub MidName_AfterUpdate()


    Me.MidName = mxc(Me.MidName)

    If len(Me.MidName) = 1 then Me.MidName = Me.MidName & "."


End Sub

[d9pierce1]

Thank you all so much!! This is just so awsome... I am in awe
Dave