Simple contatination question for a textbox in a report

[Richard Hollenbeck]

I'm getting "#Error" in a report's textbox where the control
source is the following:

=[lname] & (", "+[fname]) .

Both lname and fname are valid field names for the table assigned
as the report's recordsource. If the fname field is null I want
to ignore the comma.

"John" in the fname field and "Smith" in the lname field should
read "Smith, John" but "The Home Depot" in the lname field with
nothing (Null) in the fname field should read "The Home Depot"
rather than "The Home Depot," with the un-needed trailing comma.

I wonder what I'm doing wrong. Please advise.

Thanks!

Rich Hollenbeck

[Phil Stanton]

Try

IIf(Not IsNull(FName), LName & ", " & FName, LName)

HTH

Phil
"Richard Hollenbeck" <ri****************@verizon.net> wrote in message
news:OiW%d.22006$oa6.3194@trnddc07...

I'm getting "#Error" in a report's textbox where the control
source is the following:

=[lname] & (", "+[fname]) .

Both lname and fname are valid field names for the table assigned
as the report's recordsource. If the fname field is null I want
to ignore the comma.

"John" in the fname field and "Smith" in the lname field should
read "Smith, John" but "The Home Depot" in the lname field with
nothing (Null) in the fname field should read "The Home Depot"
rather than "The Home Depot," with the un-needed trailing comma.

I wonder what I'm doing wrong. Please advise.

Thanks!

Rich Hollenbeck

[Alan Webb]

Richard,

Try =Nz([lname],'') & IIf(Len([fname])>0,", " & [fname],'')

Nz() substitutes a value for a null. Very useful when working with columns
that should not be null like explicit strings or numeric columns that need
to be zero if null.

"Richard Hollenbeck" <ri****************@verizon.net> wrote in message
news:OiW%d.22006$oa6.3194@trnddc07...

I'm getting "#Error" in a report's textbox where the control
source is the following:

=[lname] & (", "+[fname]) .

Both lname and fname are valid field names for the table assigned
as the report's recordsource. If the fname field is null I want
to ignore the comma.

"John" in the fname field and "Smith" in the lname field should
read "Smith, John" but "The Home Depot" in the lname field with
nothing (Null) in the fname field should read "The Home Depot"
rather than "The Home Depot," with the un-needed trailing comma.

I wonder what I'm doing wrong. Please advise.

Thanks!

Rich Hollenbeck