Removing Address fields on a report

[gico1972]

As a novice i want to be able to removed lines within an address block that contain blank fields.

In my table i have following Fields

Address 1
Address 2
Town/City
County
Post Code

However the address 2 field is optional and often is left blank. I want to however display the address block removing the blank left from Address 2 field.

As an example my report shows as follows

10 High Road

Morden
Surrey
CR4 4AA

I need to remove the blank line that address 2 field has left.

Appreciate any help on this.

Regards

 

[jal]

The only solution that comes to mind is a slightly more complex query (two separate queries appended one to the other by a UNION).
In the first query, pull all the addresses where both Address1 and Address2 are populated.

In the second query, pull all the cases where Address2 is empty. In this case, we won't populate Address1 in the result set (we'll put an empty string there). The content of address1 will be moved to Address2. In other words try something like this:

SELECT * FROM Customers
WHERE Len(Address2) > 0

UNION ALL

SELECT City, Town, PostalCode, '' AS Address1, Address1 as Address2
FROM Customers
WHERE Len(Address2) = 0

This doesn't preserve the original order of addresses that you started with, though. If that's a problem, maybe we can find a way to rewrite the above as to preserve it.

 

[jal]

Maybe another method is to put Address1 and Address2 on the same line (I'm guessing you can do this with your report textboxes). Then, in cases where Address2 has some text, insert a carriage return after Address1 as to push Address2 down to the next line. To do this, you need the encoding for a carriage return, and I'm not sure what it is - something like Chr(0) but I don't know the number.

SELECT City, Town, PostalCode, Address2, IIF(Len(Address2) > 0, Address1
+ Chr(0), Address1)
FROM Customers

 

[gemma-the-husky]

if this is for a report, then concatenate all the fields into a single string

fulladdress: address1 & chr(13) & chr(10) & address2 & chr(13) & chr(10) etc

now this doesnt lose the problem, since yoo are still concatenating blank lines, but you can here make use of a trick with nulls to say instead

fulladdress: address1 & (chr(13) & chr(10) + address2) & (chr(13) & chr(10) + town) etc

(i think thats the way), since plus-ing something to a null remains a null, so you avoid concatenating the blank lines (i think that will do it offhand, but i might have the syntax slightly off)