Jump to content

Limiting field to only be 2 capital letters


This topic is 6498 days old. Please don't post here. Open a new topic instead.

Recommended Posts

I know this has to have a simple solution... but I can't find it

How do you make a field be only 2 Capital letters?

For example, for the state field, we need it to be CA. And that is the default, but users can override it to another state. So I want to ensure that it will WA or OH not "Wa" or "Oh"...

As always, thanks in advance.

Link to comment
Share on other sites

use the Upper Text Function.

You can use the Auto Enter Calculation with a calculation like

Upper ( State )

And be sure to [color:red]Deselect the button that says "[color:red]Do not replace existing values (if any)

"

Lee

Link to comment
Share on other sites

If you are going for states, I have found that it is best to make that field a "Drop-down List" of state abbreviations. In my world, people don't always know what the state abbreviation is so they make up their own.

First create a value list of state abbreviations.(list attached so you don't have to re-type them all).

Then...

In the Field tab of the Define Database window you can select your state field and click the Options button at the bottom right. From there you can click on the validation tab and set the field to require that the data be a "memeber of value list" and then select the state abbreviation value list that you created.

Also, another way to force capitalization is to format your text style/case to UPPERCASE. I do this by right-clicking on the field in layout mode then selecting Text Format. Not sure if all of this works for Macs but I can't imagine that it would be much different.

state_abbrev.txt

Link to comment
Share on other sites

That's a good idea... but I am not sure if we need it, since most of our addresses will be for CA... and only have a handful of others.

Do you know how to make the field be 5 char or 2 Capital char? I would like to tie down the zip code too....

What happens too often, during data entry, are typos and I am trying to make the system catch the "oppsies".... like a US zip is always 5 digits with a 4 digit suffix (if they know it)...

Your assistance is truly appreciated.

Link to comment
Share on other sites

Your drop down list is a good suggestion.

Making the field Uppercase in layout mode would have it display correctly, however the data isn't changed, so if you export it, or use it in another field, it will come out as entered. i.e. the Ca, etc.

Lee

Link to comment
Share on other sites

You say additional protections are overkill and then you say "So I want to ensure that it will WA or OH not "Wa" or "Oh"." And also say, "I would like to tie down the zip code too." Do you want to stop them from entering a state of ZZ or not? You will simply not have good data without good validation.

I would give you my entire US zip db. It is a simple table and would solve all your problems with it. Because I can assure you ... if you do it half-baked it will bite you later and you'll wish you had done it right to begin with. If you want my db just ask. I'd be happy to give you only states or city with state and zip (multiple zips within cities). If not, here is how to lock down the state field. All US states will pass. Anything else will fail. Validation of:

PatternCount (

"ACDFGHIKLMNOPQRSTUVW" ; Left ( State ; 1 ) ) and

PatternCount (

"ACDEHIJKLMNOPRSTUVWXYZ" ; Middle ( State ; 2 ; 1 )

)

That's the US states. Any other combination will break. It will NOT validate them as cap and it shouldn't. That is totally unnecessary. Just fix it for them using an Auto-Enter (Replace) with the suggested = Upper (state)

Now for your zip (text). Validation would be:

Length ( Filter ( Left ( zip ; 5 ) ; "0123456789" ) ) = 5

But if they enter 97424-sss, it will NOT stop them. You only asked for validation on the first five. If you want all numbers to be valid, we can do that as well. See where this is going? If they enter zip 96727 but the TRUE zip is 06727, it's just as invalid as if they had entered sssss, so why bother half-baking a solution. If they enter New York, NJ - it is just as wrong. Using an attached table would be simplest of all, particularly when you can then pre-fill the cities for them when they enter the zip codes, etc. :wink2:

LaRetta

Link to comment
Share on other sites

Ah very good point Lee. I need to fix mine!

agtjazz,

You could set your value list to only include the 5-6 states that will be needed but each time you need to add one (and it will eventually happen) you will have to go back in and spend time on it. I'm with LaRetta, fool proof it once so you don't have to mess with it anymore.

Link to comment
Share on other sites

LaRetta,

If you are still willing, I would be interested in your zip db. We have been looking to do somehting like that for some time but were unsure how we would get updated information into the system. Everything I read has a website to go to in order to get the updates (which is fine) but when we try the link it is no good or the site hasn't been updated in years.

Edited by Guest
Link to comment
Share on other sites

All US states will pass. Anything else will fail. Validation of:

PatternCount (

"ACDFGHIKLMNOPQRSTUVW" ; Left ( State ; 1 ) ) and

PatternCount (

"ACDEHIJKLMNOPRSTUVWXYZ" ; Middle ( State ; 2 ; 1 )

)

That's the US states. Any other combination will break.

If I am not mistaken, this allows for 20*22=440 valid codes - while there are only 59 postal codes (for example, "AA" will pass).

Perhaps a validation by 'Member of value list' would be better suited here?

Link to comment
Share on other sites

You guys have given me tons to think about... I think I am leaning towards doing the drop down.

LaRetta, I have downloaded the zip database... and will take a look- thanks

Yeah- I was torn between making it error proof-and overkill... but am leaning towards error-proof.

Thanks again for everyone's advice

Link to comment
Share on other sites

LaRetta,

How do you keep your zip database updated? Is there an active/current website that updates on a regular basis or is your source something that you would like to keep to yourself? :B

Link to comment
Share on other sites

"How do you keep your zip database updated?"

:blush2:

Every time I see a website with zip codes - every time someone posts a file with zips - I merge into mine. I cheat, per my style. :wink2:

Link to comment
Share on other sites

  • 1 month later...

"How do you keep your zip database updated?"

Every time I see a website with zip codes - every time someone posts a file with zips - I merge into mine. I cheat, per my style. :wink2:

Hi,

I read through this thread with great interest. I have been wondering about a Zip code file for a solution I've been working on. Rather than have a large file of Zip codes, I'd rather have a small one that is added to "on the fly" if necessary. In other words, if a user enters a Zip code not already entered, he/she would have to complete the City and State. If the Zip Code was already entered, it would do a lookup for the City and State. I know having a user enter could mean bad data, though. Which is better? This is for a pet hotel and most of our clients are fairly local. Is it ok to have table with the 40K records of Zip codes?

Thanks! I have learned a huge amount reading this forum.

Link to comment
Share on other sites

This topic is 6498 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.