Hide blank field and remove formatted period and space when blank

[dwyerj]

My delima is that I need to have [MI] and I need to have a period after the [MI] but not all names have middle initials. I know this is probably a simple IIF statement but I am not sure how to set it up so that if the field is null that it removes the period and the blank space created when there is no info in the [MI] field.

=[FIRST_NAME] & " " & [MI] & " " & [LAST_NAME] & "
" & [HSTREET] & "
" & [HCITY] & ", " & [HST] & " " & Left([HZIP],5) & "

I'm sure this is fairly simple, however it is beyond my capabilities. Thanks in advance for any help.

 

[straybullet]

=Iif(IsNull([MI]),[FIRST_NAME] & " " & [LAST_NAME] & " " & [HSTREET] & " " & [HCITY] & ", " & [HST] & " " & Left([HZIP],5) & ",=[FIRST_NAME] & " " & [MI] & ". " & [LAST_NAME] & " " & [HSTREET] & " " & [HCITY] & ", " & [HST] & " " & Left([HZIP],5) & ")

Let them hate - so long as they fear... Lucius Accius

 

[traingamer]

Or:

=[FIRST_NAME] & " " & [COLOR=blue]iif(isnull([MI]),"", [MI] & ". ")[/color] & [LAST_NAME] & " 
" & [HSTREET] & "
" & [HCITY] & ", " & [HST] & " " & Left([HZIP],5) & "

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[dwyerj]

Thanks Straybullet for helping me however access kept saying there is a syntax error. I copied and pasted it directly in as you had typed. Traingamer's solution seems to work fine. Thank you both for helping me.