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
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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(
This did end up working:
Switch(Len(xxx)=5,xxx,Len(
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.
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.
ASKER
????