Can't define a letter range

[raymanj]

I am trying to define a range validation for one of my fields. I only want to let the user to enter a one or two letter code. Therefore the range is A...ZZ.

So how do I set this up in the options section for defining fields? I have used:

In range A to ZZ

But this allows for three letters or it allows a letter and then a number.

Can anyone help?

Thanks

[vtrepanier]

Hi,

you can not do that with a range, you will have to define a value list. If you have too much elements (from A to ZZ) I think you will have to change your strategy and use a range of number.

Or you can also use two fields (each having valid values from A to Z)

E-mail me if you need more info.

Vincent

[email protected]

[The Bridge]

quote:

---

Originally posted by Ray:

I am trying to define a range validation for one of my fields. I only want to let the user to enter a one or two letter code. Therefore the range is A...ZZ.

So how do I set this up in the options section for defining fields?

---

Try setting the field's validation to Validated by Calculation, and use something like:

code:

---

Length(Field2) <= 2 and

PatternCount("ABCDEFGHIJKLMNOPQRSTUVWXYZ", Left(Field2, 1)) = 1 and PatternCount("ABCDEFGHIJKLMNOPQRSTUVWXYZ", Right(Field2, 1)) = 1

---

Set validation to Strict, and you can specify whatever error message you want in the Display Custom Message if Validation Fails text area.

This should allow you to restrict entry to a range from A to ZZ.

[Rigsby]

FOOD FOR THOUGHT – not a solution, just a thought process!

A validation by calculation is definitely the right way to do this if you want to give out an error message. However, think about this – there are 26 letters in the alphabet, so if you are only going to work with doubles “aa” “ab” “ac” etc. you could create a script or calculation that will auto enter the next value. There are not as many combinations as one might think – it goes something like this – we start with “a” which can be pared with itself plus any other letter, so assuming you want “ab” but not “ba” the result is 26 variations, then for b 25 for c 24 for d 23 etc, etc. This gives you 351 possible combinations. If you also want to work backwards i.e. ba for example, then it’s 26 x 26 = 676. Think about this – will you have enough unique codes with only two letters? If so, then it might be worth auto entering the codes yourself.

I’ve just used a system like this for user initials, whereby a user account is created, lets say for Robert Brown. He enters his name then FMP grabs the first 2 letters, RB, and looks in a related file (with just 1 record) in the field where I’ve entered all the possibilities with 2 letters separating them with a return. If a relationship is found, the user initials are set to RB and the related field is set to itself substituting “RB” with “”, which means it can never be used again. If no relationship is found (Maybe Richard Black has already taken RB) then FMP grabs the first 2 letters of the surname – so “BR” and tries again. The next step, if no match is found is the first and last letter of the surname “BN” etc. etc. etc.

Ok, at first I thought this was the way to go, but the problem was that the calculations were slowing down the creation of accounts, and some users were complaining that they wanted to set their own 2 letter initials. So what I did was to still use the above system up to a point to check if what they were entering was a valid 2 letter code – i.e. no relationship found then error message, and at the same time set up the field validation with a custom message that it had to be unique. This worked a treat, especially as I still used my first system to auto enter the initials, but simply gave the user the chance of changing them, for this you need 2 fields in the related file – one with all the possible combinations, and another using the substitute command to keep a list of those still free.

[tmanning]

This may be a better option for what you are trying accomplish...

Define a value list enter the 52 values you wish to enter,

A

B

C

D...

XX

YY

ZZ

Then validate that the field must contain a value from the value list.

Calcualations are always good to use but sometimes value lists are the easiest way to go. Please let me know if this helps you in any way.

[The Bridge]

quote:

---

Originally posted by tmanning:

This may be a better option for what you are trying accomplish...

Define a value list enter the 52 values you wish to enter,

.

.

.

Calcualations are always good to use but sometimes value lists are the easiest way to go. Please let me know if this helps you in any way.

---

I have to laugh at myself sometimes... for some reason I thought that by range A to ZZ, Ray meant including values like AB, QE, etc.

IC OK now. UR absolutely right.. the valuelist IS probably the best way to GO.