Welcome to SPN

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

Sign Up Now!

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 >
     

Share This Page