?
Solved

Format field for Zip + 4

Posted on 2015-01-13
5
Medium Priority
?
163 Views
Last Modified: 2015-01-13
In a table we have existing data for zip codes, (one field), that in some cases is just the zip like for example "12345".  In other records we have the zip+4 as "23456-2345".  But in other cases we may have the zip as "456781234".  We're working with only data files, hence the inconsistency.

But regardless, in a query designer I need to format the zip code, whatever it is to look like:

"12345" if that is all we have.

But if we have "23456-2345" OR "234562345" it needs to look like "23456-2345"

Hopefully this makes sense.  How can I format the field in the query designer?

--Steve
0
Comment
Question by:SteveL13
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 38

Assisted Solution

by:PatHartman
PatHartman earned 1000 total points
ID: 40547286
On the form/report/query, use the input mask property.  Choose the option for Zip code.  Do not apply the property at the table level since it will mask the actual value.  You never want to apply formats on the table itself.  When you look at the table data directly, you should ALWAYS see what is actually there rather than apply formatting.
0
 

Author Comment

by:SteveL13
ID: 40547300
The field in the underlying table is set as short text.  So when I try the format the field on the form I have no choices.

????
0
 
LVL 30

Accepted Solution

by:
hnasr earned 1000 total points
ID: 40547354
xxx is the field name
Try in your query:
Switch(Len(xxx)=5,xxx,Len(xxx)>5,Left(xxx,5) & "-"& Right(xxx,4)) 

Open in new window

0
 

Author Closing Comment

by:SteveL13
ID: 40547398
Awarding both because there may be times the field is text and other times where it is a number.

This did end up working:

Switch(Len(xxx)=5,xxx,Len(xxx)>5,Left(xxx,5) & "-"& Right(xxx,4))
0
 
LVL 38

Expert Comment

by:PatHartman
ID: 40547542
Zip code should ALWAYS be stored as short text.  It should never be stored as a number because although, at least in the US, it is always numeric, it is a code rather than a number and therefore it needs to retain leading zeros.

If you go back to my answer, you will see I told you to set the Input Mask Property not the Format.   You were probably confused when I said to not apply "formats" to tables.  that was a generic statement since the Input Mask is a format as are Lookups.

The problem with the solution suggested by hnasr is that it renders the field not updateable so you can't use it in a form where you are trying to enter the zip code.  You can ONLY use it for reports or non-updateable forms where you are displaying the zip code.
0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Suggested Courses

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question