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

 

[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.