Revise query

[gmaxey]

I have a query:

USAO_LOC: ([USAO_PHY_LOC] & Chr(11))+([USAO_ADDR_1]) & (Chr(11)+[USAO_ADDR_2]) & (Chr(11)+[USAO_ADDR_3])

Which is intended to create mailing address block e.g.,

Thomas A. Bubblebutt Federal Building
123 Main Street
Anytown, FL 12345

Sometimes it is just two lines:
P.O. Box 123
Anytown, FL 12345


As it is, I'm getting a undesired leading line break Chr(11) when there is no data defined for USAO_PHY_LOC

How can I revise the query to eliminate that break. Thanks.

Cross posted at: http://answers.microsoft.com/en-us/office/forum/office_2010-access/query-and-suppres-blank-data/4c58f95e-5ee9-4506-b8fb-3a5236866fe0?tm=1393379438895

[jonh]

Create a public function. If you use a paramarray you can easily add more fields in furture if you like.


Public Function Address(ParamArray p() As Variant) As String
For Each itm In p
If Len(Trim(CStr(itm))) Then
If Len(Address) Then Address = Address & vbCrLf
Address = Address & itm
End If
Next
End Function

And call it from you query: e.g.

addr: Address([field1],[field2],[field3], ... )

[gmaxey]

John,

Thanks. I had received a similar suggestion to use a GetShrinkLines function. That worked great for the query in access, but when I tried to get to my data using and RecordSet SQL statement it crashes. I've found a solution.