I am working on a report that needs international addresses that are correct to the country’s standard to where the mail is going.
Some countries want the postal code before the City, others don’t and some have regions/states, while others dont.
I have this expression:
=IIf(Eval([country] In (“Italy”,”Czech Republic”,”Argentina”,”Austria”,”Belgium”,”China”)),([zip] & ” ” & [city]),([city] & “, ” & [region] & ” ” & [zip]))
I might have another 5-10 countries to put in.. Is there a better way of handling this in expression builder with like a lookup or something? I did not know if there is a limit to how long a expression can be in Access.
Thanks for any input.
It is working right now, but looking for a better solution.
Consider using a lookup table that flags zip/country order preference. You can build such a table from existing data (ideally using country ID):
SELECT DISTINCT country, 0 AS zip_first
INTO country_preferences
FROM myTable
Then adjust default by UPDATE queries or in table UI:
UPDATE country_preferences
SET zip_first = 1
WHERE country IN (
'Italy',
'Czech Republic',
'Argentina',
'Austria',
'Belgium',
'China'
);
Finally, join the new lookup table to original table for much simpler, efficient, and scalable IIF expression:
SELECT
t.[country],
IIF(
c.[zip_first] = 1,
t.[zip] & ' ' & t.[country],
t.[city] & ', ' & t.[region] & ' ' & t.[zip]
) AS location
FROM myTable t
INNER JOIN country_preferences c
ON t.[country] = c.[country]
2
Consider creating a function. This function accepts Country, City, Region, and Zip as parameters. Within the function use a SELECT CASE statement on Country and return the appropriate formatted string for each case.
In a MODULE add:
Public Function CityZip(Optional strCountry As String="",Optional strCity As String="",Optional strZip As String="",Optional strRegion As String="") As String
Select Case strCountry
Case "Italy", "Czech Republic", "Argentina", "Austria", "Belgium", "China"
CityZip = strZip & " " & strCity
Case Else
CityZip = strCity & " " & strRegion & " " & strZip
End Select
End Function
You can then use the function as an expression instead of IIf:
=CityZip([country],[city],[zip],[region])
15