Dealing with addresses on a report

[LimitedAccess]

Hi Guys, more help required for what must be a common requirement. I've had a search but sure it must have been covered before.

As part of my company database I create purchase orders as a report. The address displayed on the purchase order is taken from that saved in the SupllierDetails table. It is held as separate fields for AddressLine1, AddressLine2, AddressLine3, AddressCity, AddressCounty, AddressPostcode, AddressCountry. now as I have it laid out on the report with each field one above the other it looks just fine if all fields have an entry in them, but more common is for the AddressLine3 and sometimes AddressLine2 fields to be empty. This looks rather rubbish on the purchase orders but I can't think of an easy way around this to result in a nice one above the next layout without any nasty gaps when a field has no entry.

Can anyone offer me any assistance with this?

Thanks

 

[Uncle Gizmo]

I posted an example of this on my website here, not working on a report however I am sure you could adapt the code to work on a report.

There is a free calendar form available for download from my web site which you may find useful it has some advanced features there is a set of videos showing you what these are and how to take advantage of them.

 

[stopher]

AddressLine1, AddressLine2, AddressLine3, AddressCity, AddressCounty, AddressPostcode, AddressCountry.

The way to do it is to concatenate them into a single field:

FullAddress:
([AddressLine1]+(Chr(13) & Chr(10))) &
([AddressLine2]+(Chr(13) & Chr(10))) &
([AddressLine3]+(Chr(13) & Chr(10))) &
([AddressCity]+(Chr(13) & Chr(10))) &
([AddressCounty]+(Chr(13) & Chr(10)))&
([AddressPostcode]+(Chr(13) & Chr(10)))&
([AddressCountry]+(Chr(13) & Chr(10)))

The key point about this expression is the use of the + as a concatenator. If a part of the address is null then the + makes that line null and hence no blank line.

So
J. Bloggs
Buckingham Palace Road
Pall Mall

London
SW1A 1AA

Will appear as:

J. Bloggs
Buckingham Palace Road
Pall Mall
London
SW1A 1AA



hth
Chris

 

[Uncle Gizmo]

>>>use of the + as a concatenator<<<

That's nice Chris, I will now go and update my code!

 

[stopher]

Thanks. Yes it's a pretty cool trick. I read it in an article by Mark Whitehorn in Personal Computer World (journal).
Chris

 

[LimitedAccess]

Thank you guys much appreciated, I will go away and concatenate

I'll let you know how I get on, such a learning curve.

 

[LimitedAccess]

Yep, works a treat.

Thanks

 

[wilkob]

Where do I put this code?

I have created a text field in my report with the code as an expression and still I get the empty line.
I also entered the code in a new field in the query which is the base for my and this has also no effect.

I am using Access 2007 does this matter?

What am I doing wrong here:

Full Address: ([Customer Name]+(Chr(13)+Chr(10))) & ([Address3]+(Chr(13)+Chr(10))) & ([City]+Chr(13)+Chr(10))+([Country]+(Chr(13)+Chr(10)))


Give me

Customer Name


Country

 

[gemma-the-husky]

rather than store addresses as line1, line2, line3 etc
i actually tend to store them in a string, copmlete with cr/lfs

its only an issue if you need to interrogate the address for particular towns or postcodes. if its just for display purposes. then its not an issue

any thoughts anyone?

 

[wilkob]

For me it is just for display purposes.

So how would you suggest storing them in a string when the fields are:

Customer Name
Address1
Address2
Address3
City
Postal Code
Country

Would love to fix this problem.

In the meantime I continued to find out why I still had the empty line and found that even though the field in the table is empty it is still not seen by Access as Null
When I go into the field (that is empty) and press the DELETE key and then check the address it is OK.

So any idea how I can solve that problem?

 

[stopher]

Sounds like you've got some empty strings or even some strings just containing spaces that you can't see.

I would suggest running a couple of update queries to remove these strings.

Make sure you fully back up your database before you do this kind of thing though is case you delete more than your expected

Chris

 

[wilkob]

Sorry, I am not that advanced that I understand completely what you mean.

The difficulty I also have is that the data comes from our central ERP system and I am just linking via ODBC.

How could I really empty these fields?

 

[Uncle Gizmo]

I would be inclined to check the data you are being provided with to see if it does indeed include records with spaces in.

You could run a query with something like this in a new column:

IIf([YourField] = " ", "SPACE", [YourField])

 

[gemma-the-husky]

if yuo're stick wioth a given format, then you're stuck with it, so yuo have to use it

if you have a field that APPEARS to be blank, it could really br blank (null), but it could be a zero length string ("") (a zls)

now concatenating with + sign will work ok, if you are dealing with nulls, but not if you have zls 's.

you can wrap both cases with a nz statement (vbnullstring isan access constant for "")

if nz(addressline,vbnullstring)=vbnullstring then etc

but its hard to nest these directly in a statement. you probably need a function to concatenate a series of these.