Addresses

  • Thread starter Thread starter Diane
  • Start date Start date
D

Diane

I have a customer table with addresses containing 3, 4 or
5 lines. I'm getting blank lines when I view the report.
Is there an expression or VB code I can write to omit
these blank lines?

Thanks!
 
Diane,

When you say the address contains 3, 4 or 5 lines, I assume you mean all 5
lines are contained in the same field, and that each line is separated by a
CrLf.

Each CrLf is represented by two ASCII characters; the carriage return (ASCII
13) and the linefeed (ASCII 10) - in that order. So a blank line can be
detected by the existence of the CrLf of the previous line plus the CrLf of
the blank line - two CrLf characters together. So all you need to do is
remove the two CrLf characters if they appear together. You can do that
using the Replace() function.

strMyAddress = Replace(rs!MyAddress, vbCrLf & vbCrLf, vbCrLf)

If you're doing it in a query, then the following will work.

SELECT Replace(MyAddress, Chr(13) & Chr(10) & Chr(13) & Chr(10), Chr(13)
& Chr(10)) As TheAddress FROM tblMyTable

Access versions prior to 2000 don't have a Replace function, so if you're
using these versions, you can replace the Replace function (no pun intended)
with this.

Public Function Repl(sExpression As String, _
sFind As String, sReplace As String) As String
Dim iPos As Integer

If Len(sExpression) > 0 And Len(sFind) > 0 Then
'Check to see if sFind exists
iPos = InStr(1, sExpression, sFind)
Do While iPos > 0
'It exists - replace it
Repl = Left(sExpression, iPos-1) & sReplace & _
Mid(sExpression, iPos + Len(sFind))

'As long as we're not at the end of sExpression,
'check to see if there is room for more instances
If iPos < Len(sExpression) Then
'Yep, there's room - check for sFind again
iPos = InStr(iPos + 1, sExpression, sFind)
Else
'Nope, there's no more room, so there can't be
'any more instances
iPos = 0
End If
Loop
Else
'There are no instances - set the return value = sExpression
Repl = sExpression
End If
End Function

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.html
 
Usually you can simply set the controls can shrink property to yes. When you
do this, all fields and data in the labels (report) will move up.

It is not clear if the address is made up of several boxes, or a one box
that the user might have entered blank lines in:

Address: First Line of address

3rd line of address


In the above text box, the user pressed control-enter (or the properties
were set that enter key = new line). The above is only ONE box, and note
that 2nd line is blank. Setting the "can shrink" property of the text box
will NOT work in he above.

On the other hand, if you have+


Address: FirstLine of Address
2nd line of address
City:

Or, you used two fields like

Address: First Line of Address
Address2: 2nd line of addresss
City:

Then, using the can shrink properties of each text box will remove the blank
lines, and this requites no code. So, it depends on how your address is
setup to be printed.
 
I use 5 fields for the address: custname, addrs1, addrs2,
CSZ, and Country.

The shrink fields control worked perfectly, I can't
believe it was that simple.

Thank you!
 
Back
Top