Report Code

[hinser13]

Dear All I attach a screen shot of a report we use for invoices.

The problem! If the client does not have a first name recorded a prenouced gap appears between the client title and surname.

How do I get the text to push up if the client first name is missing. Her is the code I use.

="Dear" & " " & ([ClientTitle]) & " " & ([ClientFirstName]) & " " & ([ClientSurname])

 

[PaulO]

I suggest using the TRIM function to eliminate leading, trailing, plus consecutive blanks. I'd also take out the second part of the expression and modify the first.

=trim("Dear " & ([ClientTitle]) & " " & ([ClientFirstName]) & " " & ([ClientSurname]))

 

[hinser13]

Thank you paul, that works great !

Lastly how would you trim the following that does not include "Dear" followed by the field names, but only the field names:

=([ClientHousenameornumber]) & " " & ([Clientaddress1])

 

[PaulO]

=trim(([ClientHousenameornumber]) & " " & ([Clientaddress1]))

Here, therefore, if the first field is blank you won't get any leading blanks in the resulting output

The process is always the same, basically ... wrap the TRIM function around the text you want to be trimmed up.

 

[DCrake]

I would add a further peice of code to finally round it off

NameInFull :Replace(NameInFull," ", " ")

IF their are only 2 elements such as title and surname you will have 2 spaces between the Mr Brown instead of Mr Brown. The replace function replaces all double spaces with single spaces.

 

[gemma-the-husky]

I would add a further peice of code to finally round it off

NameInFull :Replace(NameInFull,"^^", "^")

IF their are only 2 elements such as title and surname you will have 2 spaces between the Mr Brown instead of Mr Brown. The replace function replaces all double spaces with single spaces.

except that the awf syntax checker has removed the double spacing in your example!

^ = Space

 

[vbaInet]

If the Allow Zero Length property of each of those fields is set to No, then something like this would be appropriate too:

=("Dear" + " ") & ([ClientTitle] + " ") & ([ClientFirstName] + " ") & [ClientSurname]

=([ClientHousenameornumber] + " ") & ([Clientaddress1])