Formatting Addresses in Reports

[dtay1132]

Can anyone help with a couple of formatting questions:

1. I have a report that needs to take the appearance of a letter. How do I size the data fields on the report to shrink and grow horizontally to have proper appearance. I tried to concatonate within the query but if any of the values are missing nothing displays in the field.

2. On this same report, there may or may not be a second addressee or 2nd address line. How would I keep the vertical spacing uniform to account for records that don't have the 2nd address info?

Example:
Sam D Smith Jr.
12345 Main Street
Hudson Building
Rockville, MD 20817

Sometimes the "Hudson Building" line will be empty.

 

[KenReay]

Hi

There may be more elegant solutions, but:

Function AddressBlock(strLine1, strLine2, strLine3, strLine4, strLine5, strLine6) As String
' Given 5 Lines returns a Single Block Truncated to 255 chars, in Address Block Style
Dim str As String
str = ""
If IsNull(strLine1) Or Len(Trim(strLine1)) = 0 Then
Else
If IsNull(str) Or Len(Trim(str)) = 0 Then
Else
str = str & vbCrLf
End If
str = str & strLine1
End If
'
If IsNull(strLine2) Or Len(Trim(strLine2)) = 0 Then
Else
If IsNull(str) Or Len(Trim(str)) = 0 Then
Else
str = str & vbCrLf
End If
str = str & strLine2
End If
'
If IsNull(strLine3) Or Len(Trim(strLine3)) = 0 Then
Else
If IsNull(str) Or Len(Trim(str)) = 0 Then
Else
str = str & vbCrLf
End If
str = str & strLine3
End If
'
If IsNull(strLine4) Or Len(Trim(strLine4)) = 0 Then
Else
If IsNull(str) Or Len(Trim(str)) = 0 Then
Else
str = str & vbCrLf
End If
str = str & strLine4
End If
'
If IsNull(strLine5) Or Len(Trim(strLine5)) = 0 Then
Else
If IsNull(str) Or Len(Trim(str)) = 0 Then
Else
str = str & vbCrLf
End If
str = str & strLine5
End If
'
If IsNull(strLine5) Or Len(Trim(strLine5)) = 0 Then
Else
If IsNull(str) Or Len(Trim(str)) = 0 Then
Else
str = str & vbCrLf
End If
str = str & strLine6
End If
'
AddressBlock = Left(str, 255)
End Function Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
UK

 

[dtay1132]

Ken Reay - thanks for the help. That's a chunk of code. I'm pretty much a novice at VB, but will give this a try.