input masks to accept only numbers and decimal points

  • Thread starter Thread starter fcinelli
  • Start date Start date
F

fcinelli

Hi all,

I am creating a form where the user must enter a department ID but
this value can only contain numbers (0 - 9) and decimal points. For
example: 12.1; 12.1.1; 12.1.2; 3.5 etc

So it is possible for 2 or more decimal points to be present which
means the field must be text. I tried using an input mask but I think
that only works if you know the exact pattern the value must follow.
In my case, there could be 2 digits before a decimal point...or there
could be 2 decimal points instead of 1 (or even none at all).

Any suggestions. Not sure if input masks are the way to go. Perhaps
a vba function?
Thanks
 
Hi all,

I am creating a form where the user must enter a department ID but
this value can only contain numbers (0 - 9) and decimal points. For
example: 12.1; 12.1.1; 12.1.2; 3.5 etc

So it is possible for 2 or more decimal points to be present which
means the field must be text. I tried using an input mask but I think
that only works if you know the exact pattern the value must follow.
In my case, there could be 2 digits before a decimal point...or there
could be 2 decimal points instead of 1 (or even none at all).

Any suggestions. Not sure if input masks are the way to go. Perhaps
a vba function?
Thanks
Click to expand...

Nevermind...I found a solution. In case anyone else has a similar
issue, see: http://support.microsoft.com/kb/210385
Just replace "A" and "Z" to "1" and "9". Call the function in the
validation rule and you're golden.
 
You're right, an input mask will be worse than useless.

But you can get most if not all the way by using a validation rule. If
you just have a 3-level hierarchy you could use something like this,
which seems to cover your sample data but will probably need
fine-tuning. I've added line breaks for clarity but you'll probably need
to delete them before it will go into the Validation Rule field on the
properties sheet.

Is Null
Or (Like "[1-9]")
Or (Like "[1-9][0-9]")
Or (Like "[1-9].[1-9]")
Or (Like "[1-9][0-9].[1-9]")
Or (Like "[1-9].[1-9].[1-9]")
Or (Like "[1-9][0-9].[1-9].[1-9]")
 
you can get most if not all the way by using a validation rule. If
you just have a 3-level hierarchy you could use something like this,
which seems to cover your sample data but will probably need
fine-tuning. I've added line breaks for clarity but you'll probably need
to delete them before it will go into the Validation Rule field on the
properties sheet.

Is Null
Or (Like "[1-9]")
Or (Like "[1-9][0-9]")
Or (Like "[1-9].[1-9]")
Or (Like "[1-9][0-9].[1-9]")
Or (Like "[1-9].[1-9].[1-9]")
Or (Like "[1-9][0-9].[1-9].[1-9]")
Click to expand...

FWIW your explicit test for the null value is redundant and has been
since Access2000/Jet 4.0.

The key phrase is, "A table check constraint is satisfied if and only
if the specified <search condition> is not false for any row of a
table."

Although there is a temptation to think that "is not false" is the
same as "is true", remember SQL exhibits three value logic (TRUE,
FALSE, UNKNOWN). The result of a comparison (e.g. LIKE) with the null
value will result in UNKNOWN. UNKNOWN is not false therefore an
UNKNOWN result will satisfy a Validation Rule.

Jamie.

--
 
Back
Top