International Address Formats 1

[akaydar]

How to customise the address format for international customers in accordance with their country's preferred usage?

For instance, some countries list the Zip/PostCode before the City and some do it after the City; some include a hyphen between the Zip/PostCode and the City and some don't; some place the recipient's Position/JobDescription on the same line as his/her Name (separated with a comma) and some place it on a separate line after his/her Name; some include a Honorific before the Name and some don't, etc.

I do have separate fields for all the address components, it is just a question of formatting and displaying them differently in each case, depending on the recipient's country. I have several reports (Labels, Invoice, Welcome Aboard letter, etc.) where this needs to be implemented.

This is not a unique problem; I am sure that someone, somewhere must have already faced it. Any help would be greatly appreciated.

Adam Kaydar

 

[tlbroadbent]

I haven't dealt with International Addresses but was curious about how one might handle this non-trivial matter. A web search on Google turned up some results. Here are a few of the links.

Links to Address Issues

http://www.grcdi.nl/linksoth.htm

Address Doctor: Commercial Softeware

http://www.add-in-form.com/en/Default.htm

International Addresses And Salutations

http://www.bspage.com/address.html

Internet Address Management Solutions

http://shop.store.yahoo.com/p4l/inadmantool.html

Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[akaydar]

I have already conducted similar searches on the

http://WWW prior

to posting this problem on this forum and have come up with the same results and a few others. Some list countries with their proscribed address formats and some provide software for post code or zip validation, but none address the specific issue of Access implementation.

Thanks,

Adam Kaydar

 

[UncleJack]

I don't believe there's an easy answer to your question. As you've already discovered, each country or region has its own preferred format. In the U.S. and most Western countries the general format is specific to general, i.e.,

Name of Person
Address
City
Region
Country

This is reversed in Japan and perhaps some other Asian countries where they go from general to specific (a much more logical organization):

Country
Region
City
Address
Person

As a practical matter, however, if you mail something to just about any country the postal folks will figure it out and get it delivered.

Perhaps you could create an Access application that solves this problem and then market it for all the world to use.


Uncle Jack

 

[BoxHead]

Uncle Jack makes a good point about the fact that your letters will get delivered (eventually) as long as all of the information is on the envelope or package.

I'd suggest checking out the web sites of UPS.com, FedEx.com, DHL.com and the USPS.com first to see how they handle it.

Of course, as a matter of protocol, the header on any correspondence should be in keeping with the recipient's customs.

The only suggestion I could make is that you create multiple reports. "Welcome Aboard Letter 1, 2, 3" & so on.

On your event that runs the report, check the Country Name to determine which report will run.

HTH

John

 

[BoxHead]

Adam, I've thought about this problem a bit more and realized that my suggestion for multiple reports would only work for a single record or like records.

Here's another idea.

I took a table with basic address info and added a Memo field to it called "AddressString". On the data entry form's current and Country's afterupdate events I ran code that checked for the country's value.

EG:

Dim MyCountry as Integer

If Country = "USA" Or If Country = "CAN" Then
     MyCountry = 1
ElseIf Country = "ITALY" Or If Country = "FRANCE" Then
     MyCountry = 2
ElseIf Country = "JAPAN" Or If Country = "CHINA" Then
     MyCountry = 3
End If

If MyCountry = 1 Then
     StringAddress = CompanyName & vbNewLine _
            & FirstName & " " & LastName & vbNewLine _
            & StreetAddress & vbNewLine ...

and so on and so forth.

This way, you can use the 'calculated value' of StringAddress in your reports.

If you don't want to mess with your table design, you should be able to create a query that accomplishes the same result and base your reports on the query.

HTH

John

 

[akaydar]

This is very helpful and I've been thinking along somewhat similar lines (&quot;great minds...?)(<g>), experimenting with a simple address labels report.

I have overlaid several text boxes, each formatted for a different country, on top of each other and set their Visible property to &quot;No&quot;. Then, in the report's Detail section's On Format event I have inserted a simple code that changes the text box's Visible property to &quot;Yes&quot;, conditional on the record's country. It seems to work for the 6 countries that I have formatted so far. The daunting task, of course, is plodding systematically through all the countries and creating text boxes for them all.

If it's of any interest, here is a sample format for the UK:

=IIf(IsNull([Title]) And IsNull([FirstName]) And IsNull([LastName]),akMakeLine([Position]),IIf(IsNull([Position]),akMakeLine([Title]+&quot; &quot; & [FirstName] & &quot; &quot; & [LastName]),akMakeLine([Title]+&quot; &quot; & [FirstName] & &quot; &quot; & [LastName]+&quot;, &quot;+[Position]))) & IIf(IsNull([Institution]),akMakeLine([Department]),IIf(IsNull([Department]),akMakeLine([Institution]),akMakeLine([Institution] & &quot;, &quot; & [Department]))) & akMakeLine([Address1]) & akMakeLine([Address2]) & IIf(IsNull([Zip/PostCode]),akMakeLine([City]+&quot;, &quot; & [Region/County]),IIf(IsNull([Region/County]),akMakeLine([City] & Space(6) & [Zip/PostCode]),akMakeLine([City]+&quot;, &quot; & [Region/County] & Space(6) & [Zip/PostCode])))

Below this I have another text box whose Control Source is =Trim([Country]). The only reason for separating this from the rest of the code is that I wanted each country name to be shown in bold caps.

akMakeLine is a little function that I have written to advance the insertion point by a single line.

 

[UncleJack]

What about a more general solution? The problem is not the data elements, but the sequencing of the output. Suppose you assign a value to each of the data elements, something like:

0 = name
1 = title
2 = company
3 = street address
4 = state or province
5 = region
6 = postal code
7 = extended postal code
8 = country
9 = planet or whatever
A = first name
B = middle name
C = last name
D = you get the idea - no limit on number of values

Then construct a table with 2 fields: Country and Sequence.
In the sequence field place a string constructed of the order you want the data elements to be output, such as 01234, for a particular country.

Then use code to step through the string calling the data element indicated by the integer and formatting that field as you want on the form or label or where ever you want the information to appear. You can diddle quite a bit with missing elements like title, spaces, etc. in the actual code. The output code could be general for all outputs, or modified for special cases.

I would be willing to participate in a project constructing a table like this or something similar. Each user of the table can create his/her own code, but the table would be universal. I do a lot of international mailing and a tool like this would be very handy for me.

Uncle Jack

 

[akaydar]

This is a very interesting and innovative approach. Unfortunately, I don't think that, with my rather limited knowledge of VBA and programming capabilities, I would be able to take on such a project. However, I shall give it considerable thought. Thanks all the same.

Adam Kaydar

 

[BoxHead]

Uncle Jack,

Would it really be any more complicated than:

Dim 0 as String, Dim 1 as String, Dim 2 as String, Dim 3 as String, Dim 4 as String

'and so on...

If Name <> &quot;&quot; Then
     0 = Name & vbNewLine
Else
     0 = &quot;&quot;
End If

If Company <> &quot;&quot; Then
     1 = Company & vbNewLine
Else
     1 = &quot;&quot;
End If

If StreetAddress <> &quot;&quot; Then
     2 = StreetAddress & vbNewLine
Else
     2 = &quot;&quot;
End If

'and then continue with determining the sequence for each country.

Although there are well over 200 countries on this planet, I'd imagine that 20 or so formats for addressing would be enough. If that's the case, it would save some time and simplify updates to have one table storing the 20 sequences:

SequenceID:   Seq1, Seq2, Seq3, ... Seq20
StringSequence:  0498736, 74932601, 6378490, ...

The Countries can be stored in another two-field table: Country and SequenceID. This table can be the source for a ComboBox on the form to prevent users from mistyping a Country name.

The big question I have is where do I find out what the proper protocol is for each country?

John

 

[UncleJack]

Hi John:

I thought about creating another table to relate countries to a particular sequenceID, but it seemed to me to inject an additional layer of complication without a whole lot of benefit. I already have a table listing 239 countries so the usage table size would be pretty small. (And then, how often do you have a need to mail something to Tokelau or Djibouti?) If you group countries then you have to manage changes to an additional table if the format for a particular country within a group changes. Sort of a database overkill in my opinion. But this could be a personal preference item.

The important step is, as you suggest, compiling the formats for the individual countries. That's why I think a cooperative effort would be the best approach and why I suggested a group approach.

All we really need to agree on is the names and positions of about 10 data elements. Those desiring more can add them to their copy of the table.

Anyone interested in joining in this effort please contact me. I will act as coordinator and distribute table updates as information comes in and is compiled.


Uncle Jack
avdata@wave.net

 

[DBEngine]

Uncle Jack:

Here's another good site of postal formatting for many countries.

http://www.bitboost.com/ref/international-address-formats.html

--DB