[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 182
  • Last Modified:

Format field for Zip + 4

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
SteveL13
Asked:
SteveL13
  • 2
  • 2
2 Solutions
 
PatHartmanCommented:
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
 
SteveL13Author Commented:
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
 
hnasrCommented:
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
 
SteveL13Author Commented:
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
 
PatHartmanCommented:
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

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now