Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!
  1.   Become a Supporter    ::   Make a Contribution   
    Target (Recurring Monthly): $200 :: Achieved: $98

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

Discussion in 'Information Technology' started by Bjay, Jul 28, 2006.

  1. Bjay

    Bjay
    Expand Collapse
    Guest

    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.
     
  2. Loading...

    Similar Threads Forum Date
    Looking For A Device To Create Digital Paintings Or Art Information Technology Feb 10, 2016
    Heritage Khalsa College 81 Year-old Museum to be Digitized History of Sikhism Oct 9, 2011
    Punjab Digital Library: Doing Seva for the Panth Sikh Sikhi Sikhism Jul 16, 2011
    1984 The Digital Revolution for 1984? History of Sikhism Jun 9, 2011
    Panjab Digital Library: Bringing The Past Alive Punjab, Punjabi, Punjabiyat Nov 9, 2010

  3. Ken Snell \(MVP\)

    Ken Snell \(MVP\)
    Expand Collapse
    Guest

    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>



    "Bjay" <Bjay@discussions.microsoft.com> wrote in message
    news:4353BD97-945C-4CA1-85C8-7AD7FD5D1498@microsoft.com...
    >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.
    >
    >
     
  4. Bjay

    Bjay
    Expand Collapse
    Guest

    Re: How do I make my 5 digit zip codes print correctly with 9 digi

    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



    "Ken Snell (MVP)" wrote:

    > 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>
    >
    >
    >
    > "Bjay" <Bjay@discussions.microsoft.com> wrote in message
    > news:4353BD97-945C-4CA1-85C8-7AD7FD5D1498@microsoft.com...
    > >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.
    > >
    > >

    >
    >
    >
     
  5. Ken Snell \(MVP\)

    Ken Snell \(MVP\)
    Expand Collapse
    Guest

    Re: How do I make my 5 digit zip codes print correctly with 9 digi

    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>



    "Bjay" <Bjay@discussions.microsoft.com> wrote in message
    news:5D3842F8-0152-48D5-AF95-F3456A95D153@microsoft.com...
    > 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
    >
    >
    >
    > "Ken Snell (MVP)" wrote:
    >
    >> 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>
    >>
    >>
    >>
    >> "Bjay" <Bjay@discussions.microsoft.com> wrote in message
    >> news:4353BD97-945C-4CA1-85C8-7AD7FD5D1498@microsoft.com...
    >> >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.
    >> >
    >> >

    >>
    >>
    >>
     
  6. Bjay

    Bjay
    Expand Collapse
    Guest

    Re: How do I make my 5 digit zip codes print correctly with 9 digi

    Thanks Ken -- You're a whiz!

    "Ken Snell (MVP)" wrote:

    > 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>
    >
    >
    >
    > "Bjay" <Bjay@discussions.microsoft.com> wrote in message
    > news:5D3842F8-0152-48D5-AF95-F3456A95D153@microsoft.com...
    > > 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
    > >
    > >
    > >
    > > "Ken Snell (MVP)" wrote:
    > >
    > >> 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>
    > >>
    > >>
    > >>
    > >> "Bjay" <Bjay@discussions.microsoft.com> wrote in message
    > >> news:4353BD97-945C-4CA1-85C8-7AD7FD5D1498@microsoft.com...
    > >> >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.
    > >> >
    > >> >
    > >>
    > >>
    > >>

    >
    >
    >
     
  7. Ken Snell \(MVP\)

    Ken Snell \(MVP\)
    Expand Collapse
    Guest

    Re: How do I make my 5 digit zip codes print correctly with 9 digi

    You're welcome.
    --

    Ken Snell
    <MS ACCESS MVP>

    "Bjay" <Bjay@discussions.microsoft.com> wrote in message
    news:BA8B4542-997B-466A-BFAF-904B0A9CC81E@microsoft.com...
    > Thanks Ken -- You're a whiz!
    >
    > "Ken Snell (MVP)" wrote:
    >


    < snipped >
     
Since you're here... we have a small favor to ask...

More people are visiting & reading SPN than ever but far fewer are paying to sustain it. Advertising revenues across the online media have fallen fast. So you can see why we need to ask for your help. Donating to SPN's is vote for free speech, for diversity of opinions, for the right of the people to stand up to religious bigotry. Without any affiliation to any organization, this constant struggle takes a lot of hard work to sustain as we entirely depend on the contributions of our esteemed writers/readers. We do it because we believe our perspective matters – because it might well be your perspective, too... Fund our efforts and together we can keep the world informed about the real Sikh Sikhi Sikhism. If everyone who writes or reads our content, who likes it, helps us to pay for it, our future would be much more secure. Every Contribution Matters, Contribute Generously!

    Become a Supporter      ::     Make a Contribution     



Share This Page