Concatenating address lines 9when they are all over the place) (1 Viewer)

[peskywinnets]

Concatenating address lines (when they are all over the place)

I sell all over the globe...& have a large database of customer addresses, constructed as follows...

ShippingName, ShippingAddressLine1,ShippingAddressLine2,ShippingAddressLine3, ShippingCity,ShippingRegion,ShippingPostcode,ShippingCountry

...in other words, each line of the customer address is separated out as individual records in my access database.

But I'd like to concatenate them, but here's the thing.....

Some addresses are quite short (only use ShippingAddressLine1 used), whereas others are quite long (they use all ShippingAddressLine1, ShippingAddressLine2 & ShippingAddressLine3)

How are you meant to cater for the diversity/permutations so that when you concatenate them, they look presentable, without gaps etc?!!!

i'm looking to run a query that takes all the individual address lines...& makes them 1 and then run a report, which will see the output of the query used to construct a proof of posting for the post office....

Mr Smith 1 Acacia Ave, Newbury, Berkshire, NB12 89T, United Kingdom

or
Mr Da Silva, 2nd floor, Room 53, Sunny View, South Island, Florianopolis, Santa Catarina 2344-023, Brazil

 

[vbaInet]

I was going to say use Null Propagation but that's going to get messy.

Here's another way:
http://allenbrowne.com/func-concat.html

 

[jdraw]

You could run a Report using the Labels template and do a print preview to scan the data.

Are you building shipping labels, or what is the format you use in the business process?

Why would concatenating the fields, without some sort of punctuation, make things easier? Maybe I'm missing your intent, but if I was constructing an address for mailing/shipping, a label or label report format would seem appropriate.

See if this helps.

Good luck.

 

[peskywinnets]

the intent is to populate the form pasted below - to hand over to the post office to stamp when I hand them my postal packages each day (at the moment I'm doing this form in in excel...but it's a faff & since I have to import all the same data into Access anyway, it just makes sense to do it from there)..

...therefore I need to extract Name, full address & postal service used from my database

I'm new to reports ...and struggling how to get this one off the ground in my head!

 

[vbaInet]

Why would concatenating the fields, without some sort of punctuation, make things easier? Maybe I'm missing your intent, but if I was constructing an address for mailing/shipping, a label or label report format would seem appropriate.

Consider three fields, field1 and field3 contain values but field2 doesn't. If you concatenate all three you'll get Field1,,Field3. See there's an extra comma. Even though there's no punctuation, the spacing will be off.

 

[EssexRich]

You need to use an unbound control combined with the nz function. So =nz ([field1]) & " " & nz ([field2]) etc.

Then set visible=false on the original field1/field2 etc.

I'm tapping this out on my phone so apologies for any errors but hope this helps.

Many thanks, Rich

 

[peskywinnets]

You need to use an unbound control combined with the nz function. So =nz ([field1]) & " " & nz ([field2]) etc.

Then set visible=false on the original field1/field2 etc.

I'm tapping this out on my phone so apologies for any errors but hope this helps.

Many thanks, Rich

Hi Rich,

I tried that in a calculated field...access no likey (it bitched about "Oi, knobber......you can't use an nz in a calculated field" ....or similar)

So I'm a little stumped where such funky code would go....a query or report? (('scuse my ignorance!)

 

[EssexRich]

Good effort! nearly there!

But it does go in the design of the report rather than being a calculated field on the table/query.

Many thanks,

Rich

 

[peskywinnets]

Ok thanks, like I say...for all I'm reasonably up to speed on tables & queries...I've never ventured onto reports ...I guess I'm just going to have to bite the bullet & start watching/reading a few tutorials

(I ran a 'report wizard' & for the life of me I couldn't see where the hell sexy code would go!)

 

[EssexRich]

Well I suppose the question is, are you ok with creating forms? If so, then you'll be ok at creating reports. They work pretty much the same way, only you have to be careful of running over the edges of the available paper width on the right hand side.

The other thing to bear in mind is on a report you have some good options for grouping data together by whatever field you need.

So, what I'm suggesting you do is to use the report wizard to start with, then customise the report it generates.

Once it's built the report, click on field1 and change the "Visible" property to false (or "no" - can't remember what it says now, I do it without thinking really). Do the same for the other address fields.

Then you want to add a new text box (not a label). Place it where you want it to go on the report and make it bigger so it has all the room the text will need.

Next, edit the "control source" property with your version of what I typed earlier and hopefully it'll show you what you need. Be careful to include a space before and after each & sign. Access can be fussy about that sometimes.

Hope this helps.

Rich

 

[jdraw]

Here's a video tutorial to help with Label Report and Print Preview.