erratic format result

[MarinaW]

I have table with ZipCodes, whech have InputMask = "00000\-9999" .
When I try to print the zipcode on an envelope, it is erratically appearing
without the hyphen in the 9 digit code.
My code for printing is:
DLookup("[Zip]", "Insaddresses", " [AddIndex] = " & [AddIndex])
All of codes are entered by means of controls on data entry forms whose
InputMask is the same as mentioned above
Anyone have any idea why this erratic behavior, or how to correct?

 

[Jim Burke in Novi]

Input mask doesn't work for printing purposes, only for data entry. If you
want them to print that way, use the Format function:

Format(DLookup("[Zip]", "Insaddresses", " [AddIndex] = " &
[AddIndex]),"00000-9999")

 

[MarinaW]

Jim, I tried that, but the same erratic behaviorr continued.

Input mask doesn't work for printing purposes, only for data entry. If you
want them to print that way, use the Format function:

Format(DLookup("[Zip]", "Insaddresses", " [AddIndex] = " &
[AddIndex]),"00000-9999")

I have table with ZipCodes, whech have InputMask = "00000\-9999" .
When I try to print the zipcode on an envelope, it is erratically appearing
without the hyphen in the 9 digit code.
My code for printing is:
DLookup("[Zip]", "Insaddresses", " [AddIndex] = " & [AddIndex])
All of codes are entered by means of controls on data entry forms whose
InputMask is the same as mentioned above
Anyone have any idea why this erratic behavior, or how to correct?

 

[MarinaW]

Jim, maybe you didn't understand what i meant by erratic behavior. the
hyphen appears in the zip code when printed for some addresses, and it
doesn't for others.
MW

Input mask doesn't work for printing purposes, only for data entry. If you
want them to print that way, use the Format function:

Format(DLookup("[Zip]", "Insaddresses", " [AddIndex] = " &
[AddIndex]),"00000-9999")

I have table with ZipCodes, whech have InputMask = "00000\-9999" .
When I try to print the zipcode on an envelope, it is erratically appearing
without the hyphen in the 9 digit code.
My code for printing is:
DLookup("[Zip]", "Insaddresses", " [AddIndex] = " & [AddIndex])
All of codes are entered by means of controls on data entry forms whose
InputMask is the same as mentioned above
Anyone have any idea why this erratic behavior, or how to correct?

 

[Jim Burke in Novi]

Without being able to look at your tables and your code I can't tell you why
you'd get that erratic behavior, but if you print the zip codes using the
Format I showed it should work every time - like I said, InputMask is only
for input/display on a form, it doiesn't have any effect when printing a
report. Try the format and see what happens. Normally with a nine digit zip
code you would define a text field with a length of 9 and with your Input
mask, then when printing you would use the Format function as I showed it. Is
it possible that you had some zip codes defined before you had the input mask
defined and some were entered with a '-'?

Jim, maybe you didn't understand what i meant by erratic behavior. the
hyphen appears in the zip code when printed for some addresses, and it
doesn't for others.
MW

Input mask doesn't work for printing purposes, only for data entry. If you
want them to print that way, use the Format function:

Format(DLookup("[Zip]", "Insaddresses", " [AddIndex] = " &
[AddIndex]),"00000-9999")

I have table with ZipCodes, whech have InputMask = "00000\-9999" .
When I try to print the zipcode on an envelope, it is erratically appearing
without the hyphen in the 9 digit code.
My code for printing is:
DLookup("[Zip]", "Insaddresses", " [AddIndex] = " & [AddIndex])
All of codes are entered by means of controls on data entry forms whose
InputMask is the same as mentioned above
Anyone have any idea why this erratic behavior, or how to correct?

 

[John Spencer]

I suspect that you have zip codes stored with and without the dash. You
might try the following expression in your query or as the control
source of the control. It should properly format 9 digit codes without
the dash and display 5 digit codes without a trailing dash, and display
those ZipCodes that have been stored with the dash.

If(Len([Zipcode] & "")=9,Format([ZipCode],"@@@@@-@@@@"),[ZipCode])

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

Without being able to look at your tables and your code I can't tell you why
you'd get that erratic behavior, but if you print the zip codes using the
Format I showed it should work every time - like I said, InputMask is only
for input/display on a form, it doiesn't have any effect when printing a
report. Try the format and see what happens. Normally with a nine digit zip
code you would define a text field with a length of 9 and with your Input
mask, then when printing you would use the Format function as I showed it. Is
it possible that you had some zip codes defined before you had the input mask
defined and some were entered with a '-'?

Jim, maybe you didn't understand what i meant by erratic behavior. the
hyphen appears in the zip code when printed for some addresses, and it
doesn't for others.
MW

Input mask doesn't work for printing purposes, only for data entry. If you
want them to print that way, use the Format function:

Format(DLookup("[Zip]", "Insaddresses", " [AddIndex] = " &
[AddIndex]),"00000-9999")

:

I have table with ZipCodes, whech have InputMask = "00000\-9999" .
When I try to print the zipcode on an envelope, it is erratically appearing
without the hyphen in the 9 digit code.
My code for printing is:
DLookup("[Zip]", "Insaddresses", " [AddIndex] = " & [AddIndex])
All of codes are entered by means of controls on data entry forms whose
InputMask is the same as mentioned above
Anyone have any idea why this erratic behavior, or how to correct?

 

[MarinaW]

actually, even your coding doesn't work properly either. When I use suggested
coding, which is same as I was usuing, as control source for the control on
my envelope, I get following two strange situations.
If zipcode is all 9 digits, and I use format 00000\-0000, then I get correct
result.But if zipcode is only 5 digits, then I get result like "00001-0598"
for zipcode that should read "10598".

If I use 00000\-9999, then this works right for 5-digit code, but
gives"782659769-9999 for 9-digit code.
I find that to bypass the problem, I'm using :
If Len(DLookup("[Zip]", "Insaddresses", "[AddIndex] = " & [AddIndex])) =5
Then
Me.Zip = DLookup(DLookup("[Zip]", "Insaddresses", " [AddIndex] = " &
[AddIndex])
ElseIf Len(DLookup("[Zip]", "Insaddresses", "[AddIndex] = " & [AddIndex])) =
9 Then
Me.Zip = DLookup(Format(DLookup("[Zip]", "Insaddresses", " [AddIndex] = " &
[AddIndex]), "00000\-0000")
End if
The above works, but I still can't figure out the problem with my table data
which causes the confusion.

Without being able to look at your tables and your code I can't tell you why
you'd get that erratic behavior, but if you print the zip codes using the
Format I showed it should work every time - like I said, InputMask is only
for input/display on a form, it doiesn't have any effect when printing a
report. Try the format and see what happens. Normally with a nine digit zip
code you would define a text field with a length of 9 and with your Input
mask, then when printing you would use the Format function as I showed it. Is
it possible that you had some zip codes defined before you had the input mask
defined and some were entered with a '-'?

Jim, maybe you didn't understand what i meant by erratic behavior. the
hyphen appears in the zip code when printed for some addresses, and it
doesn't for others.
MW

Input mask doesn't work for printing purposes, only for data entry. If you
want them to print that way, use the Format function:

Format(DLookup("[Zip]", "Insaddresses", " [AddIndex] = " &
[AddIndex]),"00000-9999")

:

I have table with ZipCodes, whech have InputMask = "00000\-9999" .
When I try to print the zipcode on an envelope, it is erratically appearing
without the hyphen in the 9 digit code.
My code for printing is:
DLookup("[Zip]", "Insaddresses", " [AddIndex] = " & [AddIndex])
All of codes are entered by means of controls on data entry forms whose
InputMask is the same as mentioned above
Anyone have any idea why this erratic behavior, or how to correct?

 

[MarinaW]

No, I've even removed the input mask for the datble field, and then re-added
the hyphens to all 9-digit codes, and the problem remains. See recent reply
to Jim for further details about this hard-to-believe access behavior.

I suspect that you have zip codes stored with and without the dash. You
might try the following expression in your query or as the control
source of the control. It should properly format 9 digit codes without
the dash and display 5 digit codes without a trailing dash, and display
those ZipCodes that have been stored with the dash.

If(Len([Zipcode] & "")=9,Format([ZipCode],"@@@@@-@@@@"),[ZipCode])

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

Without being able to look at your tables and your code I can't tell you why
you'd get that erratic behavior, but if you print the zip codes using the
Format I showed it should work every time - like I said, InputMask is only
for input/display on a form, it doiesn't have any effect when printing a
report. Try the format and see what happens. Normally with a nine digit zip
code you would define a text field with a length of 9 and with your Input
mask, then when printing you would use the Format function as I showed it. Is
it possible that you had some zip codes defined before you had the input mask
defined and some were entered with a '-'?

Jim, maybe you didn't understand what i meant by erratic behavior. the
hyphen appears in the zip code when printed for some addresses, and it
doesn't for others.
MW

:

Input mask doesn't work for printing purposes, only for data entry. If you
want them to print that way, use the Format function:

Format(DLookup("[Zip]", "Insaddresses", " [AddIndex] = " &
[AddIndex]),"00000-9999")

:

I have table with ZipCodes, whech have InputMask = "00000\-9999" .
When I try to print the zipcode on an envelope, it is erratically appearing
without the hyphen in the 9 digit code.
My code for printing is:
DLookup("[Zip]", "Insaddresses", " [AddIndex] = " & [AddIndex])
All of codes are entered by means of controls on data entry forms whose
InputMask is the same as mentioned above
Anyone have any idea why this erratic behavior, or how to correct?

 

[Jim Burke in Novi]

Looks like Access doesn't handle 9 digit zips well. The seemingly obvious
ways to format it don't seem to work. But this WILL work:

myZip = DLookup("[Zip]", "Insaddresses", "[AddIndex] = " & [AddIndex]))
printedZip = Left([myZip],5) & IIf(Len([myZip])=5,"","-" & Right([myZip],4))

Kind of ridiculous that Access makes you go through all that for something
as simple as a zip code. I've always stuck with five digits, since my appl
has no need for the nine. If it ever needs it in the future I'm in trouble!

actually, even your coding doesn't work properly either. When I use suggested
coding, which is same as I was usuing, as control source for the control on
my envelope, I get following two strange situations.
If zipcode is all 9 digits, and I use format 00000\-0000, then I get correct
result.But if zipcode is only 5 digits, then I get result like "00001-0598"
for zipcode that should read "10598".

If I use 00000\-9999, then this works right for 5-digit code, but
gives"782659769-9999 for 9-digit code.
I find that to bypass the problem, I'm using :
If Len(DLookup("[Zip]", "Insaddresses", "[AddIndex] = " & [AddIndex])) =5
Then
Me.Zip = DLookup(DLookup("[Zip]", "Insaddresses", " [AddIndex] = " &
[AddIndex])
ElseIf Len(DLookup("[Zip]", "Insaddresses", "[AddIndex] = " & [AddIndex])) =
9 Then
Me.Zip = DLookup(Format(DLookup("[Zip]", "Insaddresses", " [AddIndex] = " &
[AddIndex]), "00000\-0000")
End if
The above works, but I still can't figure out the problem with my table data
which causes the confusion.

Without being able to look at your tables and your code I can't tell you why
you'd get that erratic behavior, but if you print the zip codes using the
Format I showed it should work every time - like I said, InputMask is only
for input/display on a form, it doiesn't have any effect when printing a
report. Try the format and see what happens. Normally with a nine digit zip
code you would define a text field with a length of 9 and with your Input
mask, then when printing you would use the Format function as I showed it. Is
it possible that you had some zip codes defined before you had the input mask
defined and some were entered with a '-'?

Jim, maybe you didn't understand what i meant by erratic behavior. the
hyphen appears in the zip code when printed for some addresses, and it
doesn't for others.
MW

:

Input mask doesn't work for printing purposes, only for data entry. If you
want them to print that way, use the Format function:

Format(DLookup("[Zip]", "Insaddresses", " [AddIndex] = " &
[AddIndex]),"00000-9999")

:

I have table with ZipCodes, whech have InputMask = "00000\-9999" .
When I try to print the zipcode on an envelope, it is erratically appearing
without the hyphen in the 9 digit code.
My code for printing is:
DLookup("[Zip]", "Insaddresses", " [AddIndex] = " & [AddIndex])
All of codes are entered by means of controls on data entry forms whose
InputMask is the same as mentioned above
Anyone have any idea why this erratic behavior, or how to correct?

 

[Douglas J. Steele]

Access also doesn't support Canadian postal codes, United Kingdom postcodes,
nor any of the other 58 post code formats described in
http://en.wikipedia.org/wiki/Postal_code

Remember, not all of the world needs to be able to format nine character zip
codes!

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Looks like Access doesn't handle 9 digit zips well. The seemingly obvious
ways to format it don't seem to work. But this WILL work:

myZip = DLookup("[Zip]", "Insaddresses", "[AddIndex] = " & [AddIndex]))
printedZip = Left([myZip],5) & IIf(Len([myZip])=5,"","-" &
Right([myZip],4))

Kind of ridiculous that Access makes you go through all that for something
as simple as a zip code. I've always stuck with five digits, since my appl
has no need for the nine. If it ever needs it in the future I'm in
trouble!

actually, even your coding doesn't work properly either. When I use
suggested
coding, which is same as I was usuing, as control source for the control
on
my envelope, I get following two strange situations.
If zipcode is all 9 digits, and I use format 00000\-0000, then I get
correct
result.But if zipcode is only 5 digits, then I get result like
"00001-0598"
for zipcode that should read "10598".

If I use 00000\-9999, then this works right for 5-digit code, but
gives"782659769-9999 for 9-digit code.
I find that to bypass the problem, I'm using :
If Len(DLookup("[Zip]", "Insaddresses", "[AddIndex] = " & [AddIndex])) =5
Then
Me.Zip = DLookup(DLookup("[Zip]", "Insaddresses", " [AddIndex] = " &
[AddIndex])
ElseIf Len(DLookup("[Zip]", "Insaddresses", "[AddIndex] = " &
[AddIndex])) =
9 Then
Me.Zip = DLookup(Format(DLookup("[Zip]", "Insaddresses", " [AddIndex] = "
&
[AddIndex]), "00000\-0000")
End if
The above works, but I still can't figure out the problem with my table
data
which causes the confusion.

Without being able to look at your tables and your code I can't tell
you why
you'd get that erratic behavior, but if you print the zip codes using
the
Format I showed it should work every time - like I said, InputMask is
only
for input/display on a form, it doiesn't have any effect when printing
a
report. Try the format and see what happens. Normally with a nine digit
zip
code you would define a text field with a length of 9 and with your
Input
mask, then when printing you would use the Format function as I showed
it. Is
it possible that you had some zip codes defined before you had the
input mask
defined and some were entered with a '-'?

:

Jim, maybe you didn't understand what i meant by erratic behavior.
the
hyphen appears in the zip code when printed for some addresses, and
it
doesn't for others.
MW

:

Input mask doesn't work for printing purposes, only for data entry.
If you
want them to print that way, use the Format function:

Format(DLookup("[Zip]", "Insaddresses", " [AddIndex] = " &
[AddIndex]),"00000-9999")

:

I have table with ZipCodes, whech have InputMask = "00000\-9999"
.
When I try to print the zipcode on an envelope, it is erratically
appearing
without the hyphen in the 9 digit code.
My code for printing is:
DLookup("[Zip]", "Insaddresses", " [AddIndex] = " & [AddIndex])
All of codes are entered by means of controls on data entry forms
whose
InputMask is the same as mentioned above
Anyone have any idea why this erratic behavior, or how to
correct?

 

[MarinaW]

Thats a neat way to express the coding. The oddest part is that I did not
have this problem until very recently-----everything was fine until then!

Looks like Access doesn't handle 9 digit zips well. The seemingly obvious
ways to format it don't seem to work. But this WILL work:

myZip = DLookup("[Zip]", "Insaddresses", "[AddIndex] = " & [AddIndex]))
printedZip = Left([myZip],5) & IIf(Len([myZip])=5,"","-" & Right([myZip],4))

Kind of ridiculous that Access makes you go through all that for something
as simple as a zip code. I've always stuck with five digits, since my appl
has no need for the nine. If it ever needs it in the future I'm in trouble!

actually, even your coding doesn't work properly either. When I use suggested
coding, which is same as I was usuing, as control source for the control on
my envelope, I get following two strange situations.
If zipcode is all 9 digits, and I use format 00000\-0000, then I get correct
result.But if zipcode is only 5 digits, then I get result like "00001-0598"
for zipcode that should read "10598".

If I use 00000\-9999, then this works right for 5-digit code, but
gives"782659769-9999 for 9-digit code.
I find that to bypass the problem, I'm using :
If Len(DLookup("[Zip]", "Insaddresses", "[AddIndex] = " & [AddIndex])) =5
Then
Me.Zip = DLookup(DLookup("[Zip]", "Insaddresses", " [AddIndex] = " &
[AddIndex])
ElseIf Len(DLookup("[Zip]", "Insaddresses", "[AddIndex] = " & [AddIndex])) =
9 Then
Me.Zip = DLookup(Format(DLookup("[Zip]", "Insaddresses", " [AddIndex] = " &
[AddIndex]), "00000\-0000")
End if
The above works, but I still can't figure out the problem with my table data
which causes the confusion.

Without being able to look at your tables and your code I can't tell you why
you'd get that erratic behavior, but if you print the zip codes using the
Format I showed it should work every time - like I said, InputMask is only
for input/display on a form, it doiesn't have any effect when printing a
report. Try the format and see what happens. Normally with a nine digit zip
code you would define a text field with a length of 9 and with your Input
mask, then when printing you would use the Format function as I showed it. Is
it possible that you had some zip codes defined before you had the input mask
defined and some were entered with a '-'?

:

Jim, maybe you didn't understand what i meant by erratic behavior. the
hyphen appears in the zip code when printed for some addresses, and it
doesn't for others.
MW

:

Input mask doesn't work for printing purposes, only for data entry. If you
want them to print that way, use the Format function:

Format(DLookup("[Zip]", "Insaddresses", " [AddIndex] = " &
[AddIndex]),"00000-9999")

:

I have table with ZipCodes, whech have InputMask = "00000\-9999" .
When I try to print the zipcode on an envelope, it is erratically appearing
without the hyphen in the 9 digit code.
My code for printing is:
DLookup("[Zip]", "Insaddresses", " [AddIndex] = " & [AddIndex])
All of codes are entered by means of controls on data entry forms whose
InputMask is the same as mentioned above
Anyone have any idea why this erratic behavior, or how to correct?