Hello,

Mac 5.5 server with Mac OS X clients environment.

I am using this forumula "Length(TextToNum( Account Number) ) = 10" to validate if the user entered 10 integers and prompt if they have not with a message window. Formula works fine, except that this formula fails to recognize starting with zero "0" as a first valid integar. Example, if I enter "1234567890" formula works fine. But if I start with a zero "0123456789" the formula does not accept the starting zero as a valid number and fails as if the user has not entered 10 integers. All is well if the zero is in the middle and/or the end of the 10 integars.

How can I modify this formula so a starting zero '0' can be accepted as a valid integer within the 10 integars...

Thanks,

Denis

Hi

the right calc for the validation of field "Account Number" must be:

Length(Account Number) = 10

...and you have to check the "strict data type: numeric only" box

To add to what Daniele just posted,

FileMaker drops the leading zero and the trailing zero after the decimal in calculations.

Lee

How about:

Length ( TextToNum ( "1" & Account Number) ) = 11

Hi comment

not a good thought...so, for example, this will pass the test:

abc0123456789

Yes it will. And so will 1234567890abc pass the current test, so maybe that's not the problem.

So you think that it is possible that denisg wished exactly 10 integer and some chars...

uhmm... maybe (just because he used the TextToNum() function and really didn't specify ! )

thanks to all. I have to have 10 integers ONLY...A zero at beginning of the integers is a must and at times a zero at the end of the 10 integers is also needed for the user to enter. Example "0123456789" and "0012340500" both example where zero is in beginning, middle and end must test and be valid...but can not exceed 10 integers and especiall characters of 'abc' can not be accepted and want the test to fail and prompt the user as error. Remember, my big problem is the user enters 'opqrst' where they start with an alphabet 'o' instead of zero.

...but can not exceed 10 integers ...

So, my first replay is exact !

But if you wish to allow minus than 10 integers too, the calc becomes:

Length(Account Number) ≤ 10

Also, if the "0" at the beginning is a must, you can apply this calc:

Length(Account Number) ≤ 10 and Left(Account Number;1)= 0

denisg,

Assuming your Account Number field is a Text field then your formula "Length(TextToNum( Account Number) ) = 10" will fail when you have leading zeros because you are converting it to a number and leading zeros have no revelance to a number. In this case, if the Account Number field is a Text field then the formula in Daniele's 1st post is your answer.

adios

Edited March 6, 200619 yr by Guest

Thanks so much to all. This formula "Length( TextToNum( "1" & Account Number) ) = 11" did the job...it's works exactly like I want it...

Thanks again...Denis

Ok

Now try to insert this text into that field:

01234A56789

and say us if the result is good for you.

The formula fails with the letter 'A' as I want it to because I have the field strict data type as "number" so it blocks the 'A' from passing as valid data type. Also, have maximum number set to 10.

It works exactly like I want it. Maximum 10 integers and '0' are accepted at the beginning, middle and end but no alphabets.

Thanks again for your help...Denis