input masks to accept only numbers and decimal points

[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

 

[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

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.

 

[John Nurick]

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]")

 

[Jamie Collins]

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]")

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.

--

 

[John Nurick]

On 21 Feb 2007 02:31:30 -0800, "Jamie Collins" <[email protected]>
wrote:

[snip]

Is Null
Or (Like "[1-9]")

[snip]

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

Force of habit: it's only a few months since I stopped having to make
everything work in Access 97.