IsNull Expression 2

[Jimmy2128]

I have the following query

Full Name: [FNAME] & " " & [MNAME] & " " & [LNAME]+IIf(IsNull([DESIGN]),""," ," & [DESIGN])

I am getting the follwing:

John D. Doe ,PHD

I want to eliminate the space between Doe and the Comma.
It should read like = John D. Doe,PHD

Can please help me out. I tried every single thing and I cannot make to work.

Thanks

Jimmy

 

[PHV]

Simply replace this:
" ,"
With this:
","

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Jimmy2128]

that looks the same to me. Can you please revise it.. Thanks

Jimmy

 

[PHV]

that looks the same to me
But it's not.

 

[MajP]

Another handy trick to use in these kind of cases is "null propagation". You do this by using "+" instead of "&". The difference is:
(string1 + string2) = string1string2
(string1 + null string) = null
(string1 & null string) = string1

So lets say I have first name, middle initial, and last name:

firstname middleinitial lastname
John R Smith
Susan Brown (no initial)

firstname & " " & (middleinitial + ".") & " "& lastname
will give
John R. Smith
Susan Brown

 

[r937]

firstname & " " & (middleinitial + ".") & " "& lastname

superb example, majp -- worth a star

r937.com | rudy.ca