1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Access 2000- remove hyphen from zip code

Discussion in 'Business Applications' started by nwinchel, Jan 22, 2002.

Thread Status:
Not open for further replies.
Advertisement
  1. nwinchel

    nwinchel Thread Starter

    Joined:
    Mar 19, 2001
    Messages:
    59
    Geez…it’s always something. In our databases that hold address information I have added an input mask for the zip code and have it formatted as 12345-0987. Of course we don’t have the 4-digit extension for many of the zip codes and my superior doesn’t like the way merges look when there are only 5 digits in a zip code. The hyphen remains like this: 12345-. Is it possible to remove the hyphen for zips that only have the 5 digits? Should I discontinue using the input mask? (Of course, they want the hyphen if there actually is an extension.)

    I told them this wasn’t possible without coding because I have other, more pressing things to do. Did I lie?
     
  2. downwitchyobadself

    downwitchyobadself

    Joined:
    Oct 13, 2000
    Messages:
    941
    Pretend your field is called ZipCode. Base the mail merge on a query in which you select
    1. all your necessary fields, except zip
    2. and the following for the zip:
      Code:
      ZipCodeDisplay: iif(Len([ZipCode])>5,[ZipCode],Left([ZipCode],5))
    Which means in plain English "if the zip code is longer than 5 characters, show me all of it. Otherwise, only show me the 5 leftmost (aka first :)) characters." You should be able to just cut and paste the code into a query design, changing the field name of course...
     
  3. nwinchel

    nwinchel Thread Starter

    Joined:
    Mar 19, 2001
    Messages:
    59
    Ok, I'm trying it and it isn't happening. My field name is "Zip" and I substituted that field name for "ZipCode" in your code but keep getting the famous "invalid .(dot) or ! operator or parentheses..." error message. I futzed with it a little but get the same error message. Now, I'm assuming I place this code in the "Criteria" area of the Zip field that I selected for my Query (design view).

    I apologize for my ignorance and thank you for your continued support.
     
  4. downwitchyobadself

    downwitchyobadself

    Joined:
    Oct 13, 2000
    Messages:
    941
    No no, it's a field definition, not a criterion. Post it directly into the line labeled "Field:". That's why you're getting the invalid-operator error: that first part of the code ("ZipCodeDisplay:") "names" the field for the query; you'll see when you run it that ZipCodeDisplay appears at the top of the column.

    It's very important to see the difference between a query field and a query criterion. For a field, you are directly manipulating the output. That's what we're doing with the Iif function (you might want to see Access help about how it works); we're telling the field how to display your Zip field. That's also why we have to rename it, for clarity; if you put "Zip:" instead of "ZipCodeDisplay:" or some other name, you'll get a circular-reference error, in other words you told Access to find the field Zip and to manipulate it in the same output field.

    Criteria, on the other hand, serve only to filter what data you'll get out. In your case, if you put a criteria such as
    Code:
    Like "10*"
    under the Zip field, you'll only get zip codes beginning with 10. (Yes, I do think you know this already :)) But note that there's no field naming in criteria; it's working directly on the corresponding name on the Field: line above, so it doesn't need field names, and will always cause errors.

    Feel free to paste the SQL of your query here if you're still having problems.
     
  5. nwinchel

    nwinchel Thread Starter

    Joined:
    Mar 19, 2001
    Messages:
    59

    I love you, man, I really do. It worked beautifully and not only does it work but each time you answer one of my questions or solve one of my problems, I learn a little bit more. :)

    This forum is very lucky, indeed, to have you here.
     
  6. Anne Troy

    Anne Troy

    Joined:
    Feb 14, 1999
    Messages:
    11,746
    First Name:
    Anne
    I think so too.

    Isn't he the greatest?

    (Mikey--we need the little yahoo smiley with all the hearts on it!!)
     
  7. downwitchyobadself

    downwitchyobadself

    Joined:
    Oct 13, 2000
    Messages:
    941
    And while you're at it, a nice :blushing: smiley.

    It takes a lot of people to make this the best tech-support forum I've ever seen. Thanks for the kind words...
     
  8. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/65839

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice