Making a report look like a letter

[DFowler]

I am currently working on a report which i am hoping to use as a mailshot, The issue i am having is i have 9 text boxes containing address details in a letter format as shown below. If one of these boxes is null then i am left with a gap in the address which is not ideal for a professional letter look, Any ideas would be gratefully received.

 

[KenHigg]

I think the best way to do this is to concatenate them together using vbcrlf.

 

[DFowler]

I thought about that as an idea but would that not then cancel out the way it looks ie. it would no longer be in a list format? If this is not the case can you nudge my brain in the right direction with a more detailed explanation?

 

[KenHigg]

Are the address fields all you need to fix? I'm not sure how to proceed if there are a lot of fields involved...

 

[midmented]

At first I thought IIF would work but since there are 9 text boxes I don't think it's feasible. You might be able to create a sub report for each condition and choose which to insert using the condition.

My only other thought was to use an LBound/UBound array.....ugh!

 

[midmented]

Hmmmm

At first I thought IIF statements would be the sure but with 9 text boxes maybe not.
Maybe use sub reports for the address line and switch between the sub reports using a condition during report open.
My only other thought was to use a LBound/UBound array and use concatenate based on the results.
Another thought, use the visible property (true/false) and place different textbox scenarios on the report. Make the textboxes visible based on the condition. (Looks sloppy but works)

 

[DFowler]

It is just literally the 8 address fields that require the change i know its a simple task to do on a mail merge but i was trying to avoid moving data in and out of the database, I am guessing that is going to be my only option if i want ease

 

[KenHigg]

In the on open event do something like:

dim strMyAddress as string

strMyAddress = [myField1] & vbcrlf

if myField2 & "" <> "" then
   strMyAddress = strMyAddress & [myField2] & vbcrlf
end if

if myField3 & "" <> "" then
   strMyAddress = strMyAddress & [myField3] & vbcrlf
end if

if myField4 & "" <> "" then
   strMyAddress = strMyAddress & [myField4] & vbcrlf
end if

if myField5 & "" <> "" then
   strMyAddress = strMyAddress & [myField5] & vbcrlf
end if

(etc...)

me!myAddress = strMyAddress

I think this should work.

 

[midmented]

Good work!

Yep, looks like it would work! Never thought about doing it that way. I always use vbNewLine instead of vbCrlf. I read somewhere that it was faster. I don't know how true that is but "there ya go" LOL

Good work KenHigg

 

[KenHigg]

Thank you -

But lets wait and see if mr(ms?) fowler can get it to work -

 

[DFowler]

I have now passed on this as i needed to have it done before 2 so have gone to the mailmerge tactic i will undoubtedly try and use this code in the future, Thanks for all your assistance tho, (I was a Mr last time i checked)

 

[KenHigg]

(I was a Mr last time i checked)

If anything changes let us know -