How do I make my 5 digit zip codes print correctly with 9 digit f.

[Bjay]

I am trying to start using zip+4 -- I changed the zip code field size in my
table from 5 to 9 and the input mask to 00000\-9999;; The table displays the
5 digit codes and the 9 digit codes correctly. (FYI it is a text field.)

I also changed the formatting on all my printing (reports). HOWEVER, this
is where the problem begins. All 9 digit codes print correctly, the 5 digits
codes DO NOT! The 5 digit codes print like this "3-0344" instead of the
correct "30344-". As if it were starting from the right instead of the left.

I'm sure this is a simple fix, I'm just not an Access expert and I'm
stumped. Thanks for any help.

 

[Ken Snell \(MVP\)]

Instead of using a format for the textbox control in the report, use a
textbox whose Control Source is an expression that properly formats the zip
code field's information. The expression could be something like this:

=Left([ZipCodeField], 5) & "-" & Trim(Mid(([ZipCodeField] & " "), 6, 4))

Alternatively, you could add a calculated field to the report's RecordSource
query that provides you with this output, then you would bind the report's
textbox to this calculated field from the query.

 

[Bjay]

Thanks for the reply Ken -- it worked! However, I hate to be a pain, but is
there anyway to keep the hyphen from showing up when there is only a 5
character zip code? (BTW, you lost me with the alternative solution you
provided. I'm really not very Access savvy.) Thanks again! Bjay

Instead of using a format for the textbox control in the report, use a
textbox whose Control Source is an expression that properly formats the zip
code field's information. The expression could be something like this:

=Left([ZipCodeField], 5) & "-" & Trim(Mid(([ZipCodeField] & " "), 6, 4))

Alternatively, you could add a calculated field to the report's RecordSource
query that provides you with this output, then you would bind the report's
textbox to this calculated field from the query.

--

Ken Snell
<MS ACCESS MVP>

I am trying to start using zip+4 -- I changed the zip code field size in my
table from 5 to 9 and the input mask to 00000\-9999;; The table displays
the
5 digit codes and the 9 digit codes correctly. (FYI it is a text field.)

I also changed the formatting on all my printing (reports). HOWEVER, this
is where the problem begins. All 9 digit codes print correctly, the 5
digits
codes DO NOT! The 5 digit codes print like this "3-0344" instead of the
correct "30344-". As if it were starting from the right instead of the
left.

I'm sure this is a simple fix, I'm just not an Access expert and I'm
stumped. Thanks for any help.

 

[Ken Snell \(MVP\)]

Sure --

=Left([ZipCodeField], 5) & (IIf(Len(Trim([ZipCodeField])) > 5,"-" &
Trim(Mid(([ZipCodeField] & " "), 6, 4)), "")

The "trick" that I used was to concatenate 4 blank spaces to the end of the
ZipCodeField and then to take the 6th through 9th characters of the
ZipCodeField to get the "+4" portion of the zip information.

In the above, I'm testing the length of the ZipCodeField, and only
performing the concatenation with the "-" character if there are more than 5
characters in the field.
--

Ken Snell
<MS ACCESS MVP>

Thanks for the reply Ken -- it worked! However, I hate to be a pain, but
is
there anyway to keep the hyphen from showing up when there is only a 5
character zip code? (BTW, you lost me with the alternative solution you
provided. I'm really not very Access savvy.) Thanks again! Bjay

Instead of using a format for the textbox control in the report, use a
textbox whose Control Source is an expression that properly formats the
zip
code field's information. The expression could be something like this:

=Left([ZipCodeField], 5) & "-" & Trim(Mid(([ZipCodeField] & " "), 6,
4))

Alternatively, you could add a calculated field to the report's
RecordSource
query that provides you with this output, then you would bind the
report's
textbox to this calculated field from the query.

--

Ken Snell
<MS ACCESS MVP>

I am trying to start using zip+4 -- I changed the zip code field size in
my
table from 5 to 9 and the input mask to 00000\-9999;; The table
displays
the
5 digit codes and the 9 digit codes correctly. (FYI it is a text
field.)

I also changed the formatting on all my printing (reports). HOWEVER,
this
is where the problem begins. All 9 digit codes print correctly, the 5
digits
codes DO NOT! The 5 digit codes print like this "3-0344" instead of
the
correct "30344-". As if it were starting from the right instead of the
left.

I'm sure this is a simple fix, I'm just not an Access expert and I'm
stumped. Thanks for any help.

 

[Bjay]

Thanks Ken -- You're a whiz!

Sure --

=Left([ZipCodeField], 5) & (IIf(Len(Trim([ZipCodeField])) > 5,"-" &
Trim(Mid(([ZipCodeField] & " "), 6, 4)), "")

The "trick" that I used was to concatenate 4 blank spaces to the end of the
ZipCodeField and then to take the 6th through 9th characters of the
ZipCodeField to get the "+4" portion of the zip information.

In the above, I'm testing the length of the ZipCodeField, and only
performing the concatenation with the "-" character if there are more than 5
characters in the field.
--

Ken Snell
<MS ACCESS MVP>

Thanks for the reply Ken -- it worked! However, I hate to be a pain, but
is
there anyway to keep the hyphen from showing up when there is only a 5
character zip code? (BTW, you lost me with the alternative solution you
provided. I'm really not very Access savvy.) Thanks again! Bjay

Instead of using a format for the textbox control in the report, use a
textbox whose Control Source is an expression that properly formats the
zip
code field's information. The expression could be something like this:

=Left([ZipCodeField], 5) & "-" & Trim(Mid(([ZipCodeField] & " "), 6,
4))

Alternatively, you could add a calculated field to the report's
RecordSource
query that provides you with this output, then you would bind the
report's
textbox to this calculated field from the query.

--

Ken Snell
<MS ACCESS MVP>



I am trying to start using zip+4 -- I changed the zip code field size in
my
table from 5 to 9 and the input mask to 00000\-9999;; The table
displays
the
5 digit codes and the 9 digit codes correctly. (FYI it is a text
field.)

I also changed the formatting on all my printing (reports). HOWEVER,
this
is where the problem begins. All 9 digit codes print correctly, the 5
digits
codes DO NOT! The 5 digit codes print like this "3-0344" instead of
the
correct "30344-". As if it were starting from the right instead of the
left.

I'm sure this is a simple fix, I'm just not an Access expert and I'm
stumped. Thanks for any help.

 

[Ken Snell \(MVP\)]

You're welcome.
--

Ken Snell
<MS ACCESS MVP>

Thanks Ken -- You're a whiz!

:

< snipped >