Formula Help

[btcomput]

Hi,

I have set up this formula:
TrimRight ({Roster.Last})+ ", " + TrimRight ({Roster.First}) + " " + {Roster.MI}

to display first last and MI fields in a "Name" column.

As long as all three fields are NOT null, I get:

Name:
Jones, Fred P
Smith, Nancy A
Timmons, Ned W

If a MI is blank, such as Nancy Smith with no middle initial, I get:

Name:
Jones, Fred P

Timmons, Ned W

Other fields in Nancy Smith's record display fine, but her name (or anyone else's with no middle initial), does not get displayed.

Can anyone suggest a way to modify the formula so that ALL names print? I would rather not make the middle initial be required.

Thanks,

 

[MalcolmW]

If IsNull({Roster.MI}) then
TrimRight ({Roster.Last})+ ", " + TrimRight ({Roster.First})
Else
TrimRight ({Roster.Last})+ ", " + TrimRight ({Roster.First}) + " " + {Roster.MI} Malcolm
wynden@telus.net

 

[wrbodine]

I have a similar problem, and have included a similar if/then/else statement. The problem is, one of the joins in my SQL Query is on one of these fields that can at times be NULL. I've tried all kinds of joins w/ no luck yet....