Trim Middle name

[Dean0_53]

Hello,



Is there an easy way to trim out a middle name and only have middle initial with a period? My next question is, what happens if there is not middle name? I tried doing a left function, but I couldn't get it to work.

([LAST_NAME ] & ' ' & [FIRST_NAME]) & ' ' & (' '+[MI]+' ')


Thanks,
Deano

[isladogs]

Hi Deano

Are you Russ by any chance?
Use Nz function

([LAST_NAME ] & ' ' & [FIRST_NAME]) & Nz(' ' & [MI],'')

[June7]

Have the right idea of using + for concantenation. It's a weird quirk of the + symbol. Using it for concatenation is holdover from ancient BASIC. Also, using + with Null returns Null.

([LAST_NAME ] & " " & [FIRST_NAME]) & " " + Left([MIDDLE_NAME],1) + "."

Now I see ridders52 suggestion. That expression with ' ' & will never return Null. Nz is not needed.

[ArviLaanemets]

Maybe

([LAST_NAME ] & " " & [FIRST_NAME]) & Trim(" " + Nz([MI],"")) & Iif(Nz([MI],"")="","",".") 

is better (better is not to mess up different syntaxes - maybe OP needs to port the app to some other database in future).

[Dean0_53]

Hi ridders52.

Nope, sorry my name isn't Russ.

[Dean0_53]

I'll take a look at this tomorrow am. Thanks for the suggestions!

[isladogs]

Have the right idea of using + for concantenation. It's a weird quirk of the + symbol. Using it for concatenation is holdover from ancient BASIC. Also, using + with Null returns Null.

([LAST_NAME ] & " " & [FIRST_NAME]) & " " + Left([MIDDLE_NAME],1) + "."

Now I see ridders52 suggestion. That expression with ' ' & will never return Null. Nz is not needed.

1.

using + with Null returns Null.

Thanks - I didn't know that ...but then I never use + for concatenation

2. You're of course right about my solution - my brain wasn't in gear
This should do what I meant in the first place :

([LAST_NAME ] & ' ' & [FIRST_NAME]) & IIf(Nz[MI],'')<>'',' ' & [MI],'')

[Dean0_53]

Hey Guys,

I found another table I can use but now I have a different problem. Some of the names from the table i'm using aren't coming into the final report. Also, is there a way to add a period to the Middle Initial? Please see my new expression below.

Record Name: ([LAST_NAME ] & ' ' & [FIRST_NAME]) & ' ' & (' '+[MIDDLE_INITIAL]+' ')


Thanks!

[ArviLaanemets]

Try

 ([LAST_NAME ] & ' ' & [FIRST_NAME]) & ' ' & (' '+[MIDDLE_INITIAL]+'.')

and even better will be

 ([LAST_NAME ] & ' ' & [FIRST_NAME]) & (' '+[MIDDLE_INITIAL]+'.')

as in your current expression when middle name is empty, a single space string is added after first name

[Dean0_53]

This is great!

Thanks again for the help!

Deano