If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Mailing Address
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 |
#2
|
|||
|
|||
Mailing Address
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 -- Advice to Posters. Check your post for replies or request for more information. Consider providing some feed back to the response you have recieved. Kindest Regards Mike B "Roger Bell" wrote: 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 |
#3
|
|||
|
|||
Mailing Address
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 -- Advice to Posters. Check your post for replies or request for more information. Consider providing some feed back to the response you have recieved. Kindest Regards Mike B "Roger Bell" wrote: 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 |
#4
|
|||
|
|||
Mailing Address
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 "Roger Bell" wrote: 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 |
Thread Tools | |
Display Modes | |
|
|