Mailing Address

[Roger Bell]

I have a text box on a Report as follows:

=Trim([titles] & " " & [Firstname]) & " " & [Lastname] & Chr(13) & Chr(10) &
Trim([unit/flat] & " " & [unit/flat no] & Chr(13) & Chr(10) & "" & ([street
number] & "" & [street prefix] & " " & [street name]) & Chr(13) & Chr(10) &
Trim([suburb] & " " & [state] & " " & [pcode]))

What I would like is when there is no Unit/Flat, that the line space will be
closed up.

Could someone please advise how this can be achieved?

Many thanks

 

[MikeJohnB]

Hi Roger

I just cannot get your code to work for testing, I have seen the other
threads you have posted but have a different answer

=Trim([titles] & " " & [Firstname]) & " " & [Lastname] & Chr(13) & Chr(10) &
IIf(Len([unit/flat] & " " & [unit/flat no])>1,[unit/flat] & " " & [unit/flat
no] & Chr(13) & Chr(10),"") & [street number] & " " & [street prefix] & " " &
[street name] & Chr(13) & Chr(10) & Trim([suburb] & " " & [state] & " " &
[pcode])

All the above is one line. The IIF(Len statement takes out the Empty
unit/flat and unit/flat no and doesn't add the endline code allowing the
following code to fall into the space where these two objects would have been.

I don't know if this is exactly what you want, I have tested it here and all
works but you will have to adapt it for your circumstances and field types I
guess.

I am not sure what you are doing with the trim function, haven't really
concentrated on that, I think John gave you pointers on that?

I hope this helps??????

Regards

Mike B

 

[MikeJohnB]

Hi Roger, I will try to post this one more time, been getting errors.

I have tested the following code, I cannot get your code to work here but
that may be a field type difference.

=Trim([titles] & " " & [Firstname]) & " " & [Lastname] & Chr(13) & Chr(10) &
IIf(Len([unit/flat] & " " & [unit/flat no])>1,[unit/flat] & " " & [unit/flat
no] & Chr(13) & Chr(10),"") & [street number] & " " & [street prefix] & " " &
[street name] & Chr(13) & Chr(10) & Trim([suburb] & " " & [state] & " " &
[pcode])

This code is all one line, I have not looked at what you are doing with the
trim function and have concentrated on your request. I think John gave you
pointers to that in a previous thread.

The above code removes the flat/unit and flat/unit no if they are "" length,
the IIF(Len(unit/flat] & " " & [unit/flat no])>1 part allows for the fact
that you have a space which is one character long. If it is greater than one
character, the unit/flat (a space) and the unit/flat no is printed to the
text box and a line feed end line. If it = 1, then nothing is printed to the
textbox and no line feed end line allowing the rest of the code to run on
from there.

I hope I have interpreted your requirements????

Regards
Mike B

 

[Ken Sheridan]

Try this (all as a single line). Because Null propagates in arithmetical
expressions the use of the + operator rather than the & concatenation
operator within the parenthesized expression will suppress the space and
carriage return/linefeed:

= Trim([titles] & " " & [Firstname]) & " " & [Lastname] & Chr(13) & Chr(10)
& Trim(([unit/flat] + " " + [unit/flat no]+ Chr(13) + Chr(10)) & ([street
number] & "" & [street prefix] & " " & [street name]) & Chr(13) & Chr(10) &
Trim([suburb] & " " & [state] & " " & [pcode]))

Ken Sheridan
Stafford, England