first name , middle , last name

[Mike Hughes]

DB has a field for First Name, Middle Name and Last Name. I did this select statement to combine the three fields into one Full Name. The problem is that when there is no Middle Name I'm not getting anything....I want the first and last even when there is no middle....can someone show me how to change this ?

SELECT
NOLDBA_USER_ACCOUNTS.FIRST_NAME +' ' + NOLDBA_USER_ACCOUNTS.MIDDLE_INITIAL + ' ' + NOLDBA_USER_ACCOUNTS.LAST_NAME AS [FULL NAME]

 

[Mile-O]

Lookup the Nz() function.

 

[Mike Hughes]

That looks like it might work...if I had some idea what I was doing...and I don't...it is just luck that I got this far.....can you give me some examples from my select statement? Thanks

 

[maxmangion]

simply put Nz([FieldName], " ")

put anything you want between the quotes, or leave them as they are and you will get a space.

 

[Mile-O]

SELECT
[NOLDBA_USER_ACCOUNTS].[FIRST_NAME] & ' ' & Nz([NOLDBA_USER_ACCOUNTS].[MIDDLE_INITIAL]) & ' ' & [NOLDBA_USER_ACCOUNTS].[LAST_NAME] AS [FullName]

I had to add some stuff in due to your use of special characters in field and table names - bad practice.

Also, use & for concatenation and not + - the former is the correct concatenation operator while the latter is for mathematical addition.

The above statement will, however, create two spaces between fore- and surnames when there is no middle name. To get around this we can use an evaluation expression in the SQL too.


SELECT
[NOLDBA_USER_ACCOUNTS].[FIRST_NAME] & IIf(IsNull([NOLDBA_USER_ACCOUNTS].[MIDDLE_INITIAL]), " ", " " & ([NOLDBA_USER_ACCOUNTS].[MIDDLE_INITIAL]) & " ") & [NOLDBA_USER_ACCOUNTS].[LAST_NAME] AS [FullName]

 

[Mike Hughes]

My thanks to everyone for your help....I have it working