Avoiding blank Address lines in reports

[mazza]

I have got a database with a table called Company details.
I am using the following fields (CompName, Address1, Address2, Address3, City, PostCode)
Based on this table I have a report which has a block showing the company details.
The problem is that sometimes one of the address fields (2 and/or 3) is left blank. This looks not very professional.
Any suggestions how to avoid blank lines, whilst ensuring it all looks neat and professional.

(I have thought about using memo fields, but I don't know how to control the lay out to ensure that an address is shown properly on a report)

 

[KenHigg]

Hum...

You could just concatenate all 6 flds into one in the underlying query. Something like:

MyNewFld: [CompName] & vbcrlf & [Address1] & vbcrlf & [Address2] & vbcrlf & [Address3] & vbcrlf & [City] & ", " & [PostCode]

Edit: Hum - That wouldn't work either, would it... Let me think...

 

[KenHigg]

MyNewFld: [CompName] & vbcrlf & iif(isnull([Address1]),"", [Address1] & vbcrlf) & iif(isnull([Address2], "",[Address2] & vbcrlf & , etc...

 

[mazza]

Don't think so, maybe I am asking to much but I want to ensure that e.g. address1 and address 2 abd address 3 appear as seperate lines like

CompName
Address1
Address2
Address3
City
Postcode

However when lines (eg address3) are left blank I want it to print as follows
CompName
Address1
Address2
City
Postcode

 

[mazza]

It works now

many thanks

 

[KenHigg]

Did you use the method I suggested or did you find another way?

 

[mazza]

Used your second suggestion

works like a treat

thanks

 

[dragginthru]

MyNewFld: [CompName] & vbcrlf & iif(isnull([Address1]),"", [Address1] & vbcrlf) & iif(isnull([Address2], "",[Address2] & vbcrlf & , etc...

Hi

Im trying to deal with this exact problem now!

Where would this above code go (in the underlying query?)... and also could someone just explain what it is doing?

I THINK its saying that if a null value is found in field Address1, then just print a blank character essentially, but I dont know what the & vbcrlf & means or iif

Gazz

 

[mazza]

in your report add an unbound text box. Call that for give it a useful name eg Customerdetails
Select your report / section header. In the properties select the on format event.
select code builder

than write the code as you would like it to appear:

me.customerdetails = ...................

the vbcrlf code tells it to go to the next line

 

[mazza]

in your report add an unbound text box. Call that for give it a useful name eg Customerdetails
Select your report / section header. In the properties select the on format event.
select code builder

than write the code as you would like it to appear:

me.customerdetails = ...................

the vbcrlf code tells it to go to the next line

 

[mazza]

in your report add an unbound text box. Call that for give it a useful name eg Customerdetails
Select your report / section header. In the properties select the on format event.
select code builder

than write the code as you would like it to appear:

me.customerdetails = ...................

the vbcrlf code tells it to go to the next line

 

[KenHigg]

1. Yes, in the underlying query.
2. Close, if nothing is there, put in an empty string (basically nothing)
3. The '&' is like an 'add these two togather', basically just take the two pcs of string data and combine them
4. vbcrlf is whats known as a vb constant which is evident by the 'vb' in the name. This one, the 'vbcrlf' basically inserts a carriage rerturn and a line feed charactor in the text string so that the address will start on the next line when it prints.

This may help: Link

 

[dragginthru]

OK i think that makes sense. So the & character is concatenation?

I've done this in PHP before but not in Access. That all sounds good so i shall try it, thanks again.

 

[KenHigg]

Actually, there is a way to use '+' to do this instead of '&' and iif(), but if everyone is happy, why bother...

 

[boblarson]

If you don't use labels but just the address fields on the report, you can just put the address fields in the order you want them:

Address1
Address2
City
State
ZipCode

And then, set the textbox's property to CAN SHRINK to YES and then, if the Address2 field doesn't contain data it will show up like:

Address1
City
State
ZipCode

 

[goozer321]

Or another way (found on another forum - apologies to the author; you know who you are )

=IIf(IsNull([Address1]),"",[Address1] & ", " & IIf(IsNull([Address2]),"",[Address2] & ", " & Chr(13) & Chr(10)) & IIf(IsNull([Address3]),"",[Address3] & "," & Chr(13) & Chr(10)) & IIf(IsNull([Town]),"",[Town] & ", " & Chr(13) & Chr(10)) & IIf(IsNull([County]),"",[County] & ", " & Chr(13) & Chr(10)) & IIf(IsNull([PostCode]),"",[PostCode]))

Works in VBA (me.makeuoyourowntextboxname = codeabove

and in the ControlSource box.

 

[Transentient]

Suppresing blank lines

Goozer

Your code sounds along the line of what i need also, same problems as this thread, below is my SQL query which extracts the appropriate address and contact. I found this in a sample database I downloaded for handling multiple labels, and allowing you to set the start position etc, works well.

What i need to know how do i integrate the elements of your code into this to allow for the removal of blank lines. Or should the code go into the report?

Thanks for any help

SELECT LabelList.Title, LabelList.FirstName, LabelList.Surname, LabelList.CompanyName,LabelList.Address1, LabelList.Address2,LabelList.Address3,LabelList.Address4, LabelList.Town, LabelList.County, LabelList.Country,LabelList.PostCode,LabelList.Greeting
FROM LabelList INNER JOIN tblAddressMatch ON LabelList.Label = tblAddressMatch.MatchID
UNION ALL select blankfield, blankfield, blankfield, blankfield, blankfield, blankfield, blankfield, blankfield, blankfield, blankfield, blankfield, blankfield, blankfield from tblOffset;

 

[GaryPanic]

Or another way (found on another forum - apologies to the author; you know who you are )

=IIf(IsNull([Address1]),"",[Address1] & ", " & IIf(IsNull([Address2]),"",[Address2] & ", " & Chr(13) & Chr(10)) & IIf(IsNull([Address3]),"",[Address3] & "," & Chr(13) & Chr(10)) & IIf(IsNull([Town]),"",[Town] & ", " & Chr(13) & Chr(10)) & IIf(IsNull([County]),"",[County] & ", " & Chr(13) & Chr(10)) & IIf(IsNull([PostCode]),"",[PostCode]))

Works in VBA (me.makeuoyourowntextboxname = codeabove

and in the ControlSource box.

put this into the qry as an extra field call it Addresscompact:IIf(IsNull([Address1]),"",[Address1] & ", " & IIf(IsNull([Address2]),"",[Address2] & ", " & Chr(13) & Chr(10)) & IIf(IsNull([Address3]),"",[Address3] & "," & Chr(13) & Chr(10)) & IIf(IsNull([Town]),"",[Town] & ", " & Chr(13) & Chr(10)) & IIf(IsNull([County]),"",[County] & ", " & Chr(13) & Chr(10)) & IIf(IsNull([PostCode]),"",[PostCode]))
then add this to your report asa single field and ahve can expand yes