How to remove spaces from a report if a field is left blank

[JamiLea]

I am creating a report that will print the letters for a function within my
company. I have created a report for each letter. I need to know how to
remove the spaces that are in the print preview when a certain field is not
filled out. What also makes this more difficult is the text box itself. See
the text box below. Does anyone know how to remove the spaces?

Text Box....=[AddlSent] & " " & [AddlSent2] & " " & [AddlSent3]

 

[fredg]

I am creating a report that will print the letters for a function within my
company. I have created a report for each letter. I need to know how to
remove the spaces that are in the print preview when a certain field is not
filled out. What also makes this more difficult is the text box itself. See
the text box below. Does anyone know how to remove the spaces?

Text Box....=[AddlSent] & " " & [AddlSent2] & " " & [AddlSent3]

= [AddSent] & (" "+[AddSent2]) & (" "+[AddSent3])

 

[John Spencer]

Fred's solution works if the Add1Sent2 and Add1Sent3 fields null. If the
field values are zero-length strings it fails. In my databases Fred's
solution would work since I don't allow zero-length strings. If his solution
fails then try the more complex expression below.

=[AddlSent] & IIF(Len(Add1Sent2 & "")=0,""," " & [AddlSent2]) &
IIF(Len(Add1Sent3 & "")=0,""," " & [AddlSent3])

Of course, in some databases you can store fixed length text fields and any
unused part of the length is automatically padded with spaces. This adds one
more level of complexity to the expression.

=[AddlSent] & IIF(Len(Trim(Add1Sent2) & "")=0,""," " & Trim([AddlSent2])) &
IIF(Len(Trim(Add1Sent3) & "")=0,""," " & Trim([AddlSent3]))

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

I am creating a report that will print the letters for a function within my
company. I have created a report for each letter. I need to know how to
remove the spaces that are in the print preview when a certain field is not
filled out. What also makes this more difficult is the text box itself. See
the text box below. Does anyone know how to remove the spaces?

Text Box....=[AddlSent] & " " & [AddlSent2] & " " & [AddlSent3]

= [AddSent] & (" "+[AddSent2]) & (" "+[AddSent3])

 

[JamiLea]

thank you very much! That worked perfectly

Fred's solution works if the Add1Sent2 and Add1Sent3 fields null. If the
field values are zero-length strings it fails. In my databases Fred's
solution would work since I don't allow zero-length strings. If his solution
fails then try the more complex expression below.

=[AddlSent] & IIF(Len(Add1Sent2 & "")=0,""," " & [AddlSent2]) &
IIF(Len(Add1Sent3 & "")=0,""," " & [AddlSent3])

Of course, in some databases you can store fixed length text fields and any
unused part of the length is automatically padded with spaces. This adds one
more level of complexity to the expression.

=[AddlSent] & IIF(Len(Trim(Add1Sent2) & "")=0,""," " & Trim([AddlSent2])) &
IIF(Len(Trim(Add1Sent3) & "")=0,""," " & Trim([AddlSent3]))

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
[quoted text clipped - 5 lines]

= [AddSent] & (" "+[AddSent2]) & (" "+[AddSent3])