Problem with the TRIM command

[FMO902]

I have four fields (First_Name, MI, Last_Name, Suffix) that I am trying to
trim so that they will appear without spaces on a report. When I do a
combined string (=RTrim([First_Name])+" "+[MI]+" "+Trim([Last_Name])+"
"+[Suffix]) I run into problems when any one of the fields is blank. Nothing
will print. As long as all four fields contain data it will print. Problem is
that only a few records have data in all four fields.

When I separate out the fields into four separate controls on the report, I
cant get the TRIM function to work and there are spaces between the conrols.

Any idea? I think I am missing somehting simple here.

Thanks
Rob

 

[Duane Hookom]

Typically with Access you don't need to use Trim(). Also, the & and + have
different results. + will propogate nulls.

Try:
=[First_Name] & " " + [MI] & " " + [Last_Name] & " " + [Suffix]

 

[FMO902]

Duane:
That did the trick!! Thanks. Now for my education, can you explain the
difference between the '&' and the '+' operators and also can you explain why
the TRIM command is not needed here?

Thanks
Rob

Typically with Access you don't need to use Trim(). Also, the & and + have
different results. + will propogate nulls.

Try:
=[First_Name] & " " + [MI] & " " + [Last_Name] & " " + [Suffix]

--
Duane Hookom
Microsoft Access MVP

I have four fields (First_Name, MI, Last_Name, Suffix) that I am trying to
trim so that they will appear without spaces on a report. When I do a
combined string (=RTrim([First_Name])+" "+[MI]+" "+Trim([Last_Name])+"
"+[Suffix]) I run into problems when any one of the fields is blank. Nothing
will print. As long as all four fields contain data it will print. Problem is
that only a few records have data in all four fields.

When I separate out the fields into four separate controls on the report, I
cant get the TRIM function to work and there are spaces between the conrols.

Any idea? I think I am missing somehting simple here.

Thanks
Rob

 

[Duane Hookom]

Access doesn't store trailing spaces so Trim() generally is a waste of time.
The difference between + and & is that they can both be used to concatenate
text but the + will propogate a Null
Null + "Rob" = Null
Null & "Rob" = "Rob"
Null + " " & "Rob" = "Rob"
Null & " " & "Rob" = " Rob"

--
Duane Hookom
Microsoft Access MVP

Duane:
That did the trick!! Thanks. Now for my education, can you explain the
difference between the '&' and the '+' operators and also can you explain why
the TRIM command is not needed here?

Thanks
Rob

Typically with Access you don't need to use Trim(). Also, the & and + have
different results. + will propogate nulls.

Try:
=[First_Name] & " " + [MI] & " " + [Last_Name] & " " + [Suffix]

--
Duane Hookom
Microsoft Access MVP

I have four fields (First_Name, MI, Last_Name, Suffix) that I am trying to
trim so that they will appear without spaces on a report. When I do a
combined string (=RTrim([First_Name])+" "+[MI]+" "+Trim([Last_Name])+"
"+[Suffix]) I run into problems when any one of the fields is blank. Nothing
will print. As long as all four fields contain data it will print. Problem is
that only a few records have data in all four fields.

When I separate out the fields into four separate controls on the report, I
cant get the TRIM function to work and there are spaces between the conrols.

Any idea? I think I am missing somehting simple here.

Thanks
Rob