Oh, Canada! Mailing address issues.

[Guest]

Greetings! I have a nifty form for entering Contacts and their addresses.
My form is adaptable for primary and secondary addresses, by chosing the
appropriate flag.
Now I need to modify my setup to include Canada. I've included my mailing
label query to show field names.

The post office shows Canadian addresses as such:
MS HELEN SAUNDERS
1010 CLEAR STREET
OTTAWA ON K1A 0B1
CANADA

I'd appreciate any suggestions to handle:
OTTAWA ON K1A 0B1
CANADA

Could I consider OTTAWA as a City in my format, ON as a State in my format?
I guess I could have a field for Country that defaults to United States, but
if Canada is selected, have a field visible for K1A 0B1... The new label
query scares me. One step at a time! I'd appreciate any suggestions for
incorporating Canada into my address form. Thanks.

My query:
Nz(c1.ContactNickName,c1.ContactFirstName) & " " & c1.ContactLastName AS
ContactName, IIf([AddressFlag]=1,c1.MailingAddress1 & ",
"+c1.OptAddress1,c1.MailingAddress2 & ", "+c1.OptAddress2) AS Address,
IIf([AddressFlag]=1,c1.City1,c1.City2) AS City,
IIf([AddressFlag]=1,UCase(c1.StateOrProvince1),UCase(c1.StateOrProvince2)) AS
State, IIf([AddressFlag]=1,c1.PostalCode1,c1.PostalCode2) AS PostalCode,
c1.SignificantOtherID, c1.Newsletter

 

[Brian Smith]

Greetings! I have a nifty form for entering Contacts and their addresses.
My form is adaptable for primary and secondary addresses, by chosing the
appropriate flag.
Now I need to modify my setup to include Canada. I've included my mailing
label query to show field names.

The post office shows Canadian addresses as such:
MS HELEN SAUNDERS
1010 CLEAR STREET
OTTAWA ON K1A 0B1
CANADA

I'd appreciate any suggestions to handle:
OTTAWA ON K1A 0B1
CANADA

Could I consider OTTAWA as a City in my format, ON as a State in my format?
I guess I could have a field for Country that defaults to United States, but
if Canada is selected, have a field visible for K1A 0B1... The new label
query scares me. One step at a time! I'd appreciate any suggestions for
incorporating Canada into my address form. Thanks.

Stephanie, you can definitely consider the cities and provinces to the be
same as how you have it setup for US addresses.

Your query does look scary. Do you have a table that lists all of the
states? If so, you could try adding the provinces and territories (13 in
total) to it and add a country field. It shouldn't take long to enter Canada
13 times and then you could use an update query to fill in the blanks with
USA. This should make it easier for formatting the postal codes as well as
you could have a separate input mask for Canadian addresses.

Brian

 

[Debra Farnham]

Hi Stephanie

I guess as the saying goes "I am Canadian"

Hi Stephanie

Yes .... Ottawa is a City, ON can be considered a state (however us
northerners call it a Province - Ontario to be exact) and the K1A 0B1
(letter number letter, number letter number) can be considered a zip
code/postal code.

Your idea for showing the new label is a good one but not really necessary
unless you are using an input mask for the US postal code formats which are
five numbers hyphen four numbers which really makes it more of a data entry
problem than a label problem. It is called a postal code, the same as what
you appear to be using now.

I believe I have some code kicking around here somewhere which will handle
the formatting of either a Canadian or American postal code on the lostfocus
event of the form textbox if you would like it. I live in a bordertown and
it is necessary in nearly every application I build.

I hope this helps

Debra

 

[Albert D.Kallal]

I think I would just build a sepeate label design for the candaiton lables,
as I doublt you would often mix them in a mialing

(make two seperate runs).

Furhter, you are aware that blank text boxes will "move up" in a lable (all
you have to do is set the can shrink to yes).

 

[Guest]

Thanks for the reply Debra. 5th grade Geography is starting to kick in (my
field is called "StateOrProvince".
I guess what I really have is a formatting issue. I do have PostalCode set
as 12345-6789. And I don't have a field for Country. I'd love to see your
formatting code with lostfocus event. We actually only have 1 current member
who is Canadian, but it's always nice to make sure that the form and mailing
labels are correct.

Thanks!

Hi Stephanie

I guess as the saying goes "I am Canadian"

Hi Stephanie

Yes .... Ottawa is a City, ON can be considered a state (however us
northerners call it a Province - Ontario to be exact) and the K1A 0B1
(letter number letter, number letter number) can be considered a zip
code/postal code.

Your idea for showing the new label is a good one but not really necessary
unless you are using an input mask for the US postal code formats which are
five numbers hyphen four numbers which really makes it more of a data entry
problem than a label problem. It is called a postal code, the same as what
you appear to be using now.

I believe I have some code kicking around here somewhere which will handle
the formatting of either a Canadian or American postal code on the lostfocus
event of the form textbox if you would like it. I live in a bordertown and
it is necessary in nearly every application I build.

I hope this helps

Debra

Greetings! I have a nifty form for entering Contacts and their addresses.
My form is adaptable for primary and secondary addresses, by chosing the
appropriate flag.
Now I need to modify my setup to include Canada. I've included my mailing
label query to show field names.

The post office shows Canadian addresses as such:
MS HELEN SAUNDERS
1010 CLEAR STREET
OTTAWA ON K1A 0B1
CANADA

I'd appreciate any suggestions to handle:
OTTAWA ON K1A 0B1
CANADA

Could I consider OTTAWA as a City in my format, ON as a State in my format?
I guess I could have a field for Country that defaults to United States, but
if Canada is selected, have a field visible for K1A 0B1... The new label
query scares me. One step at a time! I'd appreciate any suggestions for
incorporating Canada into my address form. Thanks.

My query:
Nz(c1.ContactNickName,c1.ContactFirstName) & " " & c1.ContactLastName AS
ContactName, IIf([AddressFlag]=1,c1.MailingAddress1 & ",
"+c1.OptAddress1,c1.MailingAddress2 & ", "+c1.OptAddress2) AS Address,
IIf([AddressFlag]=1,c1.City1,c1.City2) AS City,
IIf([AddressFlag]=1,UCase(c1.StateOrProvince1),UCase(c1.StateOrProvince2)) AS
State, IIf([AddressFlag]=1,c1.PostalCode1,c1.PostalCode2) AS PostalCode,
c1.SignificantOtherID, c1.Newsletter

 

[Guest]

Thank you for the reply. I'll look into 2 separate runs. That may be easiest.

On a different note (and I hope I'm not violating discussion group
protocol), I submitted an Access Database Queries question on 08/31/05
entitled: "Albert D. Kallal- WordMerge 20" asking if there is any way that I
could still use your fabulous WordMerge20 example, but modify it to accept
user input? If you have an opportunity to reply to the Queries question, the
answer might help others in the group.

Thanks for your time!

I think I would just build a sepeate label design for the candaiton lables,
as I doublt you would often mix them in a mialing

(make two seperate runs).

Furhter, you are aware that blank text boxes will "move up" in a lable (all
you have to do is set the can shrink to yes).


--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
(e-mail address removed)
http://www.members.shaw.ca/AlbertKallal

Greetings! I have a nifty form for entering Contacts and their addresses.
My form is adaptable for primary and secondary addresses, by chosing the
appropriate flag.
Now I need to modify my setup to include Canada. I've included my mailing
label query to show field names.

The post office shows Canadian addresses as such:
MS HELEN SAUNDERS
1010 CLEAR STREET
OTTAWA ON K1A 0B1
CANADA

I'd appreciate any suggestions to handle:
OTTAWA ON K1A 0B1
CANADA

Could I consider OTTAWA as a City in my format, ON as a State in my
format?
I guess I could have a field for Country that defaults to United States,
but
if Canada is selected, have a field visible for K1A 0B1... The new label
query scares me. One step at a time! I'd appreciate any suggestions for
incorporating Canada into my address form. Thanks.

My query:
Nz(c1.ContactNickName,c1.ContactFirstName) & " " & c1.ContactLastName AS
ContactName, IIf([AddressFlag]=1,c1.MailingAddress1 & ",
"+c1.OptAddress1,c1.MailingAddress2 & ", "+c1.OptAddress2) AS Address,
IIf([AddressFlag]=1,c1.City1,c1.City2) AS City,
IIf([AddressFlag]=1,UCase(c1.StateOrProvince1),UCase(c1.StateOrProvince2))
AS
State, IIf([AddressFlag]=1,c1.PostalCode1,c1.PostalCode2) AS PostalCode,
c1.SignificantOtherID, c1.Newsletter

 

[Guest]

Thanks for the reply. I'll look into your suggestion!

 

[Albert D.Kallal]

I will say that I simply missed that post, and did not see it....

I how now placed and responded with an answer to it.

 

[Debra Farnham]

Hi Stephanie ...... here's the code that goes on the lost focus event of the
textbox that holds that postal code and the Function it is calling:

If Not VBA.IsNull(Me.txtPostal.Value) Then
strZip = Me.txtPostal.Text
CheckMailCodeFormat
End If

Here's the function:

*********

Function CheckMailCodeFormat()

Select Case Len(strZip)
Case 5 '
5-Digit US StrZip code
If Not strZip Like "#####" Then
MsgBox "'" & strZip & "' is not a valid 5-digit US StrZip code."
Me.txtPostal.Value = Null
End If
Case 6 '
Canadian postal code (without space)
If Like2(strZip, "@#@#@#") Then
Me.txtPostal.Value = UCase(Format(strZip, "@@@ @@@"))
Else
MsgBox "'" & strZip & "' is not a valid Canadian postal code."
Me.txtPostal.Value = Null
End If
Case 7 '
Canadian postal code (with space)
If Like2(strZip, "@#@ #@#") Then
Me.txtPostal.Value = UCase(strZip)
Else
MsgBox "'" & strZip & "' is not a valid Canadian postal code."
Me.txtPostal.Value = Null
End If
Case 9 '
9-Digit US StrZip code (without hyphen)
If strZip Like "#########" Then
Me.txtPostal.Value = Format(strZip, "@@@@@-@@@@")
Else
MsgBox "'" & strZip & "' is not a valid 9-digit US StrZip code."
Me.txtPostal.Value = Null
End If
Case 10 '
9-Digit US StrZip code (with hyphen)
If Not strZip Like "#####-####" Then
MsgBox "'" & strZip & "' is not a valid 9-digit US StrZip code."
Me.txtPostal.Value = Null
End If
Case Else '
Non-postal code
MsgBox "'" & strZip & "' is not a valid 5- or 9-digit US StrZip code
or Canadian postal code."
Me.txtPostal.Value = Null
End Select
End Function

Hope this helps in some small way

Debra

Thanks for the reply Debra. 5th grade Geography is starting to kick in (my
field is called "StateOrProvince".
I guess what I really have is a formatting issue. I do have PostalCode set
as 12345-6789. And I don't have a field for Country. I'd love to see your
formatting code with lostfocus event. We actually only have 1 current member
who is Canadian, but it's always nice to make sure that the form and mailing
labels are correct.

Thanks!

Hi Stephanie

I guess as the saying goes "I am Canadian"

Hi Stephanie

Yes .... Ottawa is a City, ON can be considered a state (however us
northerners call it a Province - Ontario to be exact) and the K1A 0B1
(letter number letter, number letter number) can be considered a zip
code/postal code.

Your idea for showing the new label is a good one but not really necessary
unless you are using an input mask for the US postal code formats which are
five numbers hyphen four numbers which really makes it more of a data entry
problem than a label problem. It is called a postal code, the same as what
you appear to be using now.

I believe I have some code kicking around here somewhere which will handle
the formatting of either a Canadian or American postal code on the lostfocus
event of the form textbox if you would like it. I live in a bordertown and
it is necessary in nearly every application I build.

I hope this helps

Debra

Greetings! I have a nifty form for entering Contacts and their addresses.
My form is adaptable for primary and secondary addresses, by chosing the
appropriate flag.
Now I need to modify my setup to include Canada. I've included my mailing
label query to show field names.

The post office shows Canadian addresses as such:
MS HELEN SAUNDERS
1010 CLEAR STREET
OTTAWA ON K1A 0B1
CANADA

I'd appreciate any suggestions to handle:
OTTAWA ON K1A 0B1
CANADA

Could I consider OTTAWA as a City in my format, ON as a State in my format?
I guess I could have a field for Country that defaults to United

States,
but

if Canada is selected, have a field visible for K1A 0B1... The new label
query scares me. One step at a time! I'd appreciate any suggestions for
incorporating Canada into my address form. Thanks.

My query:
Nz(c1.ContactNickName,c1.ContactFirstName) & " " & c1.ContactLastName AS
ContactName, IIf([AddressFlag]=1,c1.MailingAddress1 & ",
"+c1.OptAddress1,c1.MailingAddress2 & ", "+c1.OptAddress2) AS Address,
IIf([AddressFlag]=1,c1.City1,c1.City2) AS City,

IIf([AddressFlag]=1,UCase(c1.StateOrProvince1),UCase(c1.StateOrProvince2))
AS

State, IIf([AddressFlag]=1,c1.PostalCode1,c1.PostalCode2) AS PostalCode,
c1.SignificantOtherID, c1.Newsletter

 

[Guest]

Debra,
Thanks so much for the code and the post. I apologize- I just saw the post
today.

I have a couple of questions, if you have time. My textbox that holds that
postal code is called PostalCode1
On the lost focus event I put this code:

Private Sub PostalCode1_LostFocus()
If Not VBA.IsNull(Me.PostalCode1.Value) Then
strZip = Me.PostalCode1.Value
' strZip = Me.PostalCode1.Text
CheckMailCodeFormat
End If

End Sub

When I was typing the line that begines strZip... the only thing that vba
would let me type was "Value" not "Text". Did I do something wrong when
trying to use:
strZip = Me.PostalCode1.Text

Next, when the code hits the CheckMailCodeFormat line, I receive an
error message:
Compile Error: expected variable or procedure, not a module.

I did put the CheckMailCodeFormat as a module. Was this wrong, or
perhaps it has to do with my first question (text vs. value).

Thanks for the help!

Hi Stephanie ...... here's the code that goes on the lost focus event of the
textbox that holds that postal code and the Function it is calling:

If Not VBA.IsNull(Me.txtPostal.Value) Then
strZip = Me.txtPostal.Text
CheckMailCodeFormat
End If

Here's the function:

*********

Function CheckMailCodeFormat()

Select Case Len(strZip)
Case 5 '
5-Digit US StrZip code
If Not strZip Like "#####" Then
MsgBox "'" & strZip & "' is not a valid 5-digit US StrZip code."
Me.txtPostal.Value = Null
End If
Case 6 '
Canadian postal code (without space)
If Like2(strZip, "@#@#@#") Then
Me.txtPostal.Value = UCase(Format(strZip, "@@@ @@@"))
Else
MsgBox "'" & strZip & "' is not a valid Canadian postal code."
Me.txtPostal.Value = Null
End If
Case 7 '
Canadian postal code (with space)
If Like2(strZip, "@#@ #@#") Then
Me.txtPostal.Value = UCase(strZip)
Else
MsgBox "'" & strZip & "' is not a valid Canadian postal code."
Me.txtPostal.Value = Null
End If
Case 9 '
9-Digit US StrZip code (without hyphen)
If strZip Like "#########" Then
Me.txtPostal.Value = Format(strZip, "@@@@@-@@@@")
Else
MsgBox "'" & strZip & "' is not a valid 9-digit US StrZip code."
Me.txtPostal.Value = Null
End If
Case 10 '
9-Digit US StrZip code (with hyphen)
If Not strZip Like "#####-####" Then
MsgBox "'" & strZip & "' is not a valid 9-digit US StrZip code."
Me.txtPostal.Value = Null
End If
Case Else '
Non-postal code
MsgBox "'" & strZip & "' is not a valid 5- or 9-digit US StrZip code
or Canadian postal code."
Me.txtPostal.Value = Null
End Select
End Function

Hope this helps in some small way

Debra

Thanks for the reply Debra. 5th grade Geography is starting to kick in (my
field is called "StateOrProvince".
I guess what I really have is a formatting issue. I do have PostalCode set
as 12345-6789. And I don't have a field for Country. I'd love to see your
formatting code with lostfocus event. We actually only have 1 current member
who is Canadian, but it's always nice to make sure that the form and mailing
labels are correct.

Thanks!

Hi Stephanie

I guess as the saying goes "I am Canadian"

Hi Stephanie

Yes .... Ottawa is a City, ON can be considered a state (however us
northerners call it a Province - Ontario to be exact) and the K1A 0B1
(letter number letter, number letter number) can be considered a zip
code/postal code.

Your idea for showing the new label is a good one but not really necessary
unless you are using an input mask for the US postal code formats which are
five numbers hyphen four numbers which really makes it more of a data entry
problem than a label problem. It is called a postal code, the same as what
you appear to be using now.

I believe I have some code kicking around here somewhere which will handle
the formatting of either a Canadian or American postal code on the lostfocus
event of the form textbox if you would like it. I live in a bordertown and
it is necessary in nearly every application I build.

I hope this helps

Debra


Greetings! I have a nifty form for entering Contacts and their addresses.
My form is adaptable for primary and secondary addresses, by chosing the
appropriate flag.
Now I need to modify my setup to include Canada. I've included my mailing
label query to show field names.

The post office shows Canadian addresses as such:
MS HELEN SAUNDERS
1010 CLEAR STREET
OTTAWA ON K1A 0B1
CANADA

I'd appreciate any suggestions to handle:
OTTAWA ON K1A 0B1
CANADA

Could I consider OTTAWA as a City in my format, ON as a State in my
format?
I guess I could have a field for Country that defaults to United States,
but
if Canada is selected, have a field visible for K1A 0B1... The new label
query scares me. One step at a time! I'd appreciate any suggestions for
incorporating Canada into my address form. Thanks.

My query:
Nz(c1.ContactNickName,c1.ContactFirstName) & " " & c1.ContactLastName AS
ContactName, IIf([AddressFlag]=1,c1.MailingAddress1 & ",
"+c1.OptAddress1,c1.MailingAddress2 & ", "+c1.OptAddress2) AS Address,
IIf([AddressFlag]=1,c1.City1,c1.City2) AS City,
IIf([AddressFlag]=1,UCase(c1.StateOrProvince1),UCase(c1.StateOrProvince2))
AS
State, IIf([AddressFlag]=1,c1.PostalCode1,c1.PostalCode2) AS PostalCode,
c1.SignificantOtherID, c1.Newsletter

 

[David Ross]

Thanks for the reply Debra. 5th grade Geography is starting to kick in (my
field is called "StateOrProvince".
I guess what I really have is a formatting issue. I do have PostalCode set
as 12345-6789. And I don't have a field for Country. I'd love to see your
formatting code with lostfocus event. We actually only have 1 current member
who is Canadian, but it's always nice to make sure that the form and mailing
labels are correct.

Waaay back in my 80s mini-computer days I wrote software that was used
in both countries and by clients who had addresses on both sides of the
borders.

I stored only the data. No spaces or hyphens.

For output I did the following:

If it had a letter in it at all we put a space in it after the 3rd position.

If it was 5 character or less long we printed as is. If it was 6 long we
printed with a space inserted after the 3rd position. If it was longer
than 6 we put a "-" after the 5th position.

We put the rest of the rules at input time.