got concatentaion to work but need more help

[Mike D]

select cfname + ' ' + (isnull(cmi , ' cmi ')) + '. ' + clname as expr1


this is the expression I had to use. However this is what I need to perfect
it.

If there is a value in the CMI field then add the '. ' if not then do not
add the period.

 

[Alison]

I use the following to add the MiddleInitial and a space
when needed. You could do the same, but put the . in
front of the space.

Name: [FirstName] & " " & IIf(IsNull([MiddleInitial]),"",
[MiddleInitial] & " ") & [LastName]

Alison

 

[Van T. Dinh]

If you are using MS-SQL Server, you will to use the CASE
statement. Something like:

SELECT cfname +
CASE
WHEN (cmi Is Null) Then
' cmi '
ELSE
cmi + '. '
END
+ clname AS expr1

HTH
Van T. Dinh
MVP (Access)