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 access fill in the city when I type the zip code

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

  1. Alene

    Alene
    Expand Collapse
    Guest

    How do I make MS Access fill in the city when I type the zip code in the form?
     
  2. Loading...


  3. Tom Ellison

    Tom Ellison
    Expand Collapse
    Guest

    Dear Alene:

    You would first need a table that gives the name of the city given the zip
    code.

    Unfortunately, there is sometimes more than one city or town with the same
    zip code. In less populated portions of the country there can be several
    towns with the same zip code. The zip code represents a single post office.
    There could be only one post office for several small towns.

    So, if you have:

    John Smith
    101 Main Street
    55111

    That is, not specifying the city or state, but using the zip code to
    "lookup" the city and state, there's a possible problem. If there are 2
    towns in that zip code, and they both have a Main Street, this address could
    be for two different buildings in different towns.

    Perhaps this is not terribly common, but not impossible.

    You could still do this, but allow users to correct the City if its wrong.
    It would then be quite useful 99% of the time.

    Tom Ellison


    "Alene" <Alene@discussions.microsoft.com> wrote in message
    news:82B6ADB3-3B04-4B37-984E-39BF91A55DCA@microsoft.com...
    > How do I make MS Access fill in the city when I type the zip code in the
    > form?
     
  4. Larry Linson

    Larry Linson
    Expand Collapse
    Guest

    I'd only add to Tom's excellent answer that Zip codes change and are
    augmented by the Post Office, and they publish new files periodically
    ((monthly or thereabouts, if memory serves).

    The easiest way to get up-to-date files in the format you want, if what the
    Post Office provides is not your format, is to subscribe to a third-party
    service that converts and provides the files on a schedule. One provider of
    ZIP code data usable with Access is FMS, Inc.
    http://www.fmsinc.com/products/zipcode/index.html. I have not used, nor
    tried, their product so cannot offer an opinion, though I see there is a
    free trial that you can download. There may be other suppliers, as well.

    Larry Linson
    Microsoft Access MVP

    "Tom Ellison" <tellison@jcdoyle.com> wrote in message
    news:%23WQKlifeGHA.2188@TK2MSFTNGP05.phx.gbl...
    > Dear Alene:
    >
    > You would first need a table that gives the name of the city given the zip
    > code.
    >
    > Unfortunately, there is sometimes more than one city or town with the same
    > zip code. In less populated portions of the country there can be several
    > towns with the same zip code. The zip code represents a single post
    > office. There could be only one post office for several small towns.
    >
    > So, if you have:
    >
    > John Smith
    > 101 Main Street
    > 55111
    >
    > That is, not specifying the city or state, but using the zip code to
    > "lookup" the city and state, there's a possible problem. If there are 2
    > towns in that zip code, and they both have a Main Street, this address
    > could be for two different buildings in different towns.
    >
    > Perhaps this is not terribly common, but not impossible.
    >
    > You could still do this, but allow users to correct the City if its wrong.
    > It would then be quite useful 99% of the time.
    >
    > Tom Ellison
    >
    >
    > "Alene" <Alene@discussions.microsoft.com> wrote in message
    > news:82B6ADB3-3B04-4B37-984E-39BF91A55DCA@microsoft.com...
    >> How do I make MS Access fill in the city when I type the zip code in the
    >> form?

    >
    >
     
  5. Alene

    Alene
    Expand Collapse
    Guest

    I already have the table with city and zip - I just need to know how to join
    the tables so that when I type the zip in the form it will automatically make
    the city show up.

    "Larry Linson" wrote:

    > I'd only add to Tom's excellent answer that Zip codes change and are
    > augmented by the Post Office, and they publish new files periodically
    > ((monthly or thereabouts, if memory serves).
    >
    > The easiest way to get up-to-date files in the format you want, if what the
    > Post Office provides is not your format, is to subscribe to a third-party
    > service that converts and provides the files on a schedule. One provider of
    > ZIP code data usable with Access is FMS, Inc.
    > http://www.fmsinc.com/products/zipcode/index.html. I have not used, nor
    > tried, their product so cannot offer an opinion, though I see there is a
    > free trial that you can download. There may be other suppliers, as well.
    >
    > Larry Linson
    > Microsoft Access MVP
    >
    > "Tom Ellison" <tellison@jcdoyle.com> wrote in message
    > news:%23WQKlifeGHA.2188@TK2MSFTNGP05.phx.gbl...
    > > Dear Alene:
    > >
    > > You would first need a table that gives the name of the city given the zip
    > > code.
    > >
    > > Unfortunately, there is sometimes more than one city or town with the same
    > > zip code. In less populated portions of the country there can be several
    > > towns with the same zip code. The zip code represents a single post
    > > office. There could be only one post office for several small towns.
    > >
    > > So, if you have:
    > >
    > > John Smith
    > > 101 Main Street
    > > 55111
    > >
    > > That is, not specifying the city or state, but using the zip code to
    > > "lookup" the city and state, there's a possible problem. If there are 2
    > > towns in that zip code, and they both have a Main Street, this address
    > > could be for two different buildings in different towns.
    > >
    > > Perhaps this is not terribly common, but not impossible.
    > >
    > > You could still do this, but allow users to correct the City if its wrong.
    > > It would then be quite useful 99% of the time.
    > >
    > > Tom Ellison
    > >
    > >
    > > "Alene" <Alene@discussions.microsoft.com> wrote in message
    > > news:82B6ADB3-3B04-4B37-984E-39BF91A55DCA@microsoft.com...
    > >> How do I make MS Access fill in the city when I type the zip code in the
    > >> form?

    > >
    > >

    >
    >
    >
     
  6. Alene

    Alene
    Expand Collapse
    Guest

    I already have the table with city and zip - I just need to know how to join
    the tables so that when I type the zip in the form it will automatically make
    the city show up.

    "Tom Ellison" wrote:

    > Dear Alene:
    >
    > You would first need a table that gives the name of the city given the zip
    > code.
    >
    > Unfortunately, there is sometimes more than one city or town with the same
    > zip code. In less populated portions of the country there can be several
    > towns with the same zip code. The zip code represents a single post office.
    > There could be only one post office for several small towns.
    >
    > So, if you have:
    >
    > John Smith
    > 101 Main Street
    > 55111
    >
    > That is, not specifying the city or state, but using the zip code to
    > "lookup" the city and state, there's a possible problem. If there are 2
    > towns in that zip code, and they both have a Main Street, this address could
    > be for two different buildings in different towns.
    >
    > Perhaps this is not terribly common, but not impossible.
    >
    > You could still do this, but allow users to correct the City if its wrong.
    > It would then be quite useful 99% of the time.
    >
    > Tom Ellison
    >
    >
    > "Alene" <Alene@discussions.microsoft.com> wrote in message
    > news:82B6ADB3-3B04-4B37-984E-39BF91A55DCA@microsoft.com...
    > > How do I make MS Access fill in the city when I type the zip code in the
    > > form?

    >
    >
    >
     
  7. Ron2006

    Ron2006
    Expand Collapse
    Guest

    two different options:

    The better for always having a value:
    Make it a dropdown and return 3 fields zip, city, state and limit to
    list and then in the afterupdate event
    me.cityfieldname = me.zipcombo.column(1)
    me.statefieldname = me.zipcombo.colmun(2)
    however this does not cover Canadian zips nor foreign countries.

    An alternative:
    have zip as txtbox.
    In the afterupdate event
    dim holdcity as variant

    if isnull(me.zipfieldname) then
    msgbox.................... error
    elseif len(me.zipfieldname) < 5 then
    msgbox ............... error
    elseif maybe, depending on how you want to handle zip+4

    else

    holdcity = dlookup ("[zipfilecityname]",
    "querytogetcityusing zip")
    if isnull(holdcity) then
    msgbox "zip not found"
    else
    me.cityfieldname = holdcity
    holdcity = dlookup ("[zipfilecityname]",
    "querytogetcityusing zip")
    me.statefieldname = holdcity
    endif

    This is aircode, but something like this needs to be done and it can
    vary depending on how elaborate/safe you want to make it.

    Ron

    "The definition of a fool proof system is that some fool comes along
    and proves you wrong."
     
  8. FMS Development Team

    FMS Development Team
    Expand Collapse
    Guest

    Hi Larry,

    Thanks for the referral. Our Total ZipCode Database product is a
    monthly subscription to the official USPS zip code database. We provide
    it to you in an Access table, along with some other lookup tables like
    country and states for a handful of countries.

    We also include code and a form that shows how people can enter the zip
    code and automatically have the city and state filled in. The combo box
    for the city also displays other non-primary but acceptable city names.
    As you can imagine, it increases data entry speed and eliminates typos.


    Visit http://www.fmsinc.com/products/zipcode/index.html for more
    information.

    Luke Chung
    President
    FMS, Inc.
    http://www.fmsinc.com

    P.S. Check out our latest update to Total Access Statistics, the most
    advanced number crunching tool designed specifically for Access:
    http://www.fmsinc.com/products/statistics/index.html
     

Share This Page