Extra comma and zero-length string

[BillA]

I have a 'simple' concatenation of 3 fields with a comma before the 3rd
field: "FirstName LastName, Academic"

I would like for the finished string to read; "Jane Smith, PhD"
howevever, I've discovered many records have zero-length strings in the
Academic field and are causing the comma to appear when the Academic field is
blank, e.g:
"Tom Jones,"
This is what I have, which works for most, but not all the records that the
Title field appears blank:

Name: [tbl_Investigator]![FirstName] & " " & [tbl_Investigator]![LastName] &
", "+[tbl_Investigator]![Academic] & IIf(Len([tbl_Investigator]![Academic] &
"")=0,"")

Any advice would be appreciated.
Thank you,
Bill

 

[John Spencer]

I would recommend that you not use Name as the column name.

I would write the expression as follows

FullName: [tbl_Investigator].[FirstName] & " " &
[tbl_Investigator].[LastName] &
IIF(Len(tbl_Investigator.Academic & "")=0,Null,", ") &
tbl_Investigator].[Academic]

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[tedmi]

Your code unconditionally concatenates a comma after Lastname. The comma
should be inside the condition, like this:

FirstName & " " & LastName &
IIF(Len(Academic)=0, "", ", " & Academic)

 

[BillA]

Twice in one week - thank you for your time and assistance.

Have a great weekend.
Bill

I would recommend that you not use Name as the column name.

I would write the expression as follows

FullName: [tbl_Investigator].[FirstName] & " " &
[tbl_Investigator].[LastName] &
IIF(Len(tbl_Investigator.Academic & "")=0,Null,", ") &
tbl_Investigator].[Academic]

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

I have a 'simple' concatenation of 3 fields with a comma before the 3rd
field: "FirstName LastName, Academic"

I would like for the finished string to read; "Jane Smith, PhD"
howevever, I've discovered many records have zero-length strings in the
Academic field and are causing the comma to appear when the Academic field is
blank, e.g:
"Tom Jones,"
This is what I have, which works for most, but not all the records that the
Title field appears blank:

Name: [tbl_Investigator]![FirstName] & " " & [tbl_Investigator]![LastName] &
", "+[tbl_Investigator]![Academic] & IIf(Len([tbl_Investigator]![Academic] &
"")=0,"")

Any advice would be appreciated.
Thank you,
Bill