Input Mask or Validation?

[k11ngy]

I need to set Primary key field so that user has to enter 2 Letters and 4
numbers as serial number for hardware. Do I use Input mask or Validation
rule? What would be the expression?

Thanks for help

Steve

 

[Arvin Meyer [MVP]]

This input mask will work:

LL0000

 

[John W. Vinson]

I need to set Primary key field so that user has to enter 2 Letters and 4
numbers as serial number for hardware. Do I use Input mask or Validation
rule? What would be the expression?

Thanks for help

Steve

Either would work; the input mask might be a bit friendlier, as the validation
rule message can be cryptic. Try a mask of

LL0000

and/or a validation rule

LIKE "[A-Z][A-Z]####"

 

[james hall]

This input mask will work:


--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

 

[Tom Wickerath]

Hi Steve,

Personally, I would use a validation rule. Input masks are generally a
pain-in-the-butt to deal with. Try the following validation rule at the table
level (so that it will apply to any query or form that uses this table as a
record source):

LIKE "[A-Z][A-Z][0-9][0-9][0-9][0-9]"

Also, I would set a unique index [Indexed: Yes (No Duplicates) ] on this
field, but I would not set it as the primary key, simply because I have a
strong preference for using the meaningless Autonumber data type as a primary
key. A text-based primary key will require you to check off the Cascade
Update option for a relationship with enforced Referential Integrity, if you
later need to change the value of this field, and have that value cascaded to
related child records. If the field is only present in a parent table as a
uniquely indexed field, you are free to change it's value at any time in the
future, without having to deal with potential locks on related records that
other users may be editing, in a multiuser environment. More discussion on
Cascade Updates, here:

http://www.granite.ab.ca/access/cascadeupdatedelete.htm


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________

 

[Tom Wickerath]

PS. Don't forget to include the appropriate Validation Text, such as:

This field requires two letters followed by four numbers.

You might also set the Field Size to 6, and set a unique index as I
mentioned in my previous reply.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________

Hi Steve,

Personally, I would use a validation rule. Input masks are generally a
pain-in-the-butt to deal with. Try the following validation rule at the table
level (so that it will apply to any query or form that uses this table as a
record source):

LIKE "[A-Z][A-Z][0-9][0-9][0-9][0-9]"

Also, I would set a unique index [Indexed: Yes (No Duplicates) ] on this
field, but I would not set it as the primary key, simply because I have a
strong preference for using the meaningless Autonumber data type as a primary
key. A text-based primary key will require you to check off the Cascade
Update option for a relationship with enforced Referential Integrity, if you
later need to change the value of this field, and have that value cascaded to
related child records. If the field is only present in a parent table as a
uniquely indexed field, you are free to change it's value at any time in the
future, without having to deal with potential locks on related records that
other users may be editing, in a multiuser environment. More discussion on
Cascade Updates, here:

http://www.granite.ab.ca/access/cascadeupdatedelete.htm


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________

I need to set Primary key field so that user has to enter 2 Letters and 4
numbers as serial number for hardware. Do I use Input mask or Validation
rule? What would be the expression?

Thanks for help

Steve

 

[David W. Fenton]

Input masks are generally a
pain-in-the-butt to deal with.

Everyone says this. I don't understand it. Input masks do things
that cannot be done otherwise without substantial coding. I see no
reason whatsoever not to use them where appropriate (although dates
are about the only type of data I use them for regularly).

 

[Tom Wickerath]

Hi David,

Try copying data from one source (say an e-mail message), and pasting it
into a text box on a form that includes an Input Mask. I don't know about
you, but I've found that this generally fails even if the data is in the
correct format for the mask. The mask work fine when typing data in, but I
seem to have lots of problems in the past trying to paste data in from the
clipboard.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________

 

[Armen Stein]

Everyone says this. I don't understand it. Input masks do things
that cannot be done otherwise without substantial coding. I see no
reason whatsoever not to use them where appropriate (although dates
are about the only type of data I use them for regularly).

Hi David,

I have to disagree with you on using input masks for dates. I find
them very presumptuous. How does the developer know how I enter
dates? Maybe I use 2 digit years, maybe 4. Maybe I use slashes or
hyphens. Maybe I type "jan 16". Access handles all of this with *no*
coding. Input masks might make it easy for the developer, but not so
much for the user.

The only place I've seen a *possible* use for input masks is for SSN.
The format is consistent enough that they can be helpful. But even
then, I'd rather accept the SSN with or without hyphens, and then run
it through a quick cleanup, validation and hyphenation function.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

 

[David W. Fenton]

Try copying data from one source (say an e-mail message), and
pasting it into a text box on a form that includes an Input Mask.
I don't know about you, but I've found that this generally fails
even if the data is in the correct format for the mask. The mask
work fine when typing data in, but I seem to have lots of problems
in the past trying to paste data in from the clipboard.

Input masks are for typing. If you are pasting arbitrary data into
the field from the clipboard, and this is by design, then you
shouldn't use a date mask. That's not what they are for, so it's
fine not to use it in that case.

But it's not a flaw in input masks. Input masks do exactly what they
were designed to do, restrict typed input.

 

[David W. Fenton]

I have to disagree with you on using input masks for dates. I
find them very presumptuous. How does the developer know how I
enter dates?

The developer's job is to make sure that the user doesn't enter
invalid or ambiguous data, so restricting the format of the input is
*exactly* within the developer's area of responsibility.

Maybe I use 2 digit years, maybe 4. Maybe I use slashes or
hyphens. Maybe I type "jan 16". Access handles all of this with
*no* coding.

But not in a way that is 100% reliable in terms of expected
interpretation vs. what Access guesses you mean.

When you rely on Access to interpret a 2-digit year, you're assuming
the users will always be inputting data that will conform to the
assumptions behind Access's algorithm for interpreting 2-digit
years. I am not comfortable with that at all, because there are too
many possibilities for that going wrong.

Input masks might make it easy for the developer, but not so
much for the user.

While ease of use is an important goal, for me, data integrity
trumps it. And really, how much or an inconvenience is it, really,
to have to input dates in a particular format? It really isn't any
hardship at all.

The only place I've seen a *possible* use for input masks is for
SSN. The format is consistent enough that they can be helpful.
But even then, I'd rather accept the SSN with or without hyphens,
and then run it through a quick cleanup, validation and
hyphenation function.

US SSN's can include both digits and letters, so I would never use
an input mask for them. I don't use input masks for postal codes or
telephone numbers, either.

But I consider requiring 4-digit years to be an absolute
requirement, regardless of whether the input data range spans the
Access 2-digit year window or not. Rather than write a bunch of code
to validate the user input, I just narrow the possibilities of how
the user can enter the data, and the input mask is the easiest way
to do that.

And I've never had a single user complain about it.

*I* find it annoying, but the users don't.

And I can be sure the data is getting entered properly.

What's not to like?

 

[Armen Stein]

The developer's job is to make sure that the user doesn't enter
invalid or ambiguous data, so restricting the format of the input is
*exactly* within the developer's area of responsibility.

Yes, but there are other ways that don't impose on the user as much.

But not in a way that is 100% reliable in terms of expected
interpretation vs. what Access guesses you mean.

When you rely on Access to interpret a 2-digit year, you're assuming
the users will always be inputting data that will conform to the
assumptions behind Access's algorithm for interpreting 2-digit
years. I am not comfortable with that at all, because there are too
many possibilities for that going wrong.

It's 100% reliable if you use a full year Format like mm/dd/yyyy. The
moment you tab out of the field, Access will show you how it
interpreted the date, or tell you that it is invalid.

While ease of use is an important goal, for me, data integrity
trumps it. And really, how much or an inconvenience is it, really,
to have to input dates in a particular format? It really isn't any
hardship at all.

That's why I like to have both data integrity *and* ease of use. And
I've found that little things like entering dates a restrictive way
does really represent an irritation to some users.

US SSN's can include both digits and letters, so I would never use
an input mask for them. I don't use input masks for postal codes or
telephone numbers, either.

I totally agree with you on the postal codes and phone numbers. Input
masks are obviously too restrictive. But I'm not aware of SSNs
containing letters. Here's the official word:

http://www.socialsecurity.gov/employer/ssnvshandbk/submission.htm

But I consider requiring 4-digit years to be an absolute
requirement, regardless of whether the input data range spans the
Access 2-digit year window or not. Rather than write a bunch of code
to validate the user input, I just narrow the possibilities of how
the user can enter the data, and the input mask is the easiest way
to do that.

Again, Access will display the 4-digit year it interprets, so making
mistakes on the century can be avoided.

And I've never had a single user complain about it.

*I* find it annoying, but the users don't.

Well, I figure that if you and I find it annoying, chances are some of
our users will too. And they might not complain, since many systems
have unnecessarily strict entry rules on date fields. Maybe they
don't know it can be better!

Cheers,

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

 

[Tom Wickerath]

Hi David,

My first bad experience with Input Masks happened over 10 years ago, when I
was doing the Membership database for the PNWADG (Pacific NW Access
Developer's Group). I was e-mailed a copy of the database from the person
whom I was taking over for. At the time, we were tracking address and phone
number information, along with several other attributes. I would get e-mailed
requests to add a person to the database, sometimes with all the information
I needed, and other times only after sending back a reply for additional
information.

I seem to recall that I had trouble with Input Masks for both the phone
number and the zip code. I'm not one that enjoys re-typing data from an
e-mail into a database, especially since I have a known tendency for some
minor dyslexia. Copy and paste is the only reliable way for me. These &^%$#@
Input Masks seemed to balk more often than not, even when the data was in the
correct format for the mask. I eventually said "screw it" and just removed
the masks from both fields.

That's my experience with using them, as the user of an application
developed by someone else. As Armen said, "Input masks might make it easy for
the developer, but not so much for the user." He couldn't have said it
better, as far as I am concerned.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________

 

[David W. Fenton]

I seem to recall that I had trouble with Input Masks for both the
phone number and the zip code.

I don't recommend using input masks for either of those. Input masks
work for some things and not for others -- use them for the ones
that work and don't for the ones that don't.

 

[David W. Fenton]

It's 100% reliable if you use a full year Format like mm/dd/yyyy.
The moment you tab out of the field, Access will show you how it
interpreted the date, or tell you that it is invalid.

But you're placing responsibility on the user to CHECK that they
entered the right date. Those who can touch type heads-down data
entry won't look.

Nor should they be required to! Never make a human being do what the
computer can do, particularly if the human being runs the risk of
forgetting to (or purposefully neglecting to) do it!

That's why I like to have both data integrity *and* ease of use.
And I've found that little things like entering dates a
restrictive way does really represent an irritation to some users.

While I hate date input masks myself, I have found that my end users
don't. They just accept the restrictions and type in all the digits.

Over the years I've found that most end users don't like
alternatives for accomplishing a task -- they are quite comfortable
with being forced to do a single operation exactly the same way each
time with no shortcuts. This is 100% opposed to the way *I* feel
comfortable working, but it's the way a lot of people's minds are
wired. And inputs masks don't interfere with that at all.

I totally agree with you on the postal codes and phone numbers.
Input masks are obviously too restrictive. But I'm not aware of
SSNs containing letters. Here's the official word:

http://www.socialsecurity.gov/employer/ssnvshandbk/submission.htm

I didn't think they included them until I got data from UNOS, the
national organ transplant registry, and they used letters in SSNs. I
check on this and this was not an innovation on the part of UNOS,
but an accepted industry-wide practice for providing SSN's for
non-US residents.

Again, Access will display the 4-digit year it interprets, so
making mistakes on the century can be avoided.

You don't want "can be avoided" -- you want "cannot be mistyped."

Well, I figure that if you and I find it annoying, chances are
some of our users will too. And they might not complain, since
many systems have unnecessarily strict entry rules on date fields.
Maybe they don't know it can be better!

On balance, I don't think it *can* be better. In order to enforce
proper data entry by all, a few users have to be very, very slightly
inconvenienced.

 

[Tom Wickerath]

In reading your other reply to Armen, I see that you are only using Input
masks for date / time data type. I use the mm/dd/yyyy format myself, which
seems to work just fine.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________

 

[Larry Linson]

I don't recommend using input masks for either of those. Input masks
work for some things and not for others -- use them for the ones
that work and don't for the ones that don't.

The only time I've ever found Input Masks to be useful was in doing Y2K
remediation... the corporate IT guidelines to which we had to adhere
required ensuring that the full four-digit year was entered for dates. We
accomplished that, in Access 2.0 (yes, it was a little 'late' to still be
running Access 2.0, but the client didn't wish to fund a version upgrade)
with an Input Mask.

It was still a pain to have to be sure you clicked at the very beginning of
the text box, but it did enforce four digits for the year; perhaps we could
have done it in a more user-friendly way with VBA validation code, but that
would have been more work (and the client wasn't wiling to fund that extra
work, either).

We developers didn't like making life harder for the users, and the users
didn't like having to be so careful (they weren't heads-down data entry
clerks), but the people paying the bill liked it. Another instance of the
Golden Rule of corporate IT: "He who has the gold makes the rules."

Larry Linson
Microsoft Office Access MVP

 

[Tom Wickerath]

I didn't think they included them until I got data from UNOS, the

national organ transplant registry, and they used letters in SSNs. I
check on this and this was not an innovation on the part of UNOS,
but an accepted industry-wide practice for providing SSN's for
non-US residents.

I don't know about non-US residents, but I can tell you that Medicare
appends a "T" onto the end of a person's SSN, when they have a transplant. At
this point, it is no longer an SSN but rather a Medicare number. I have
personal knowledge of this, as a family member is living with a kidney
transplant. This person uses the SSN without the "T" suffix for all other
purposes, such as job applications, etc.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________

 

[Armen Stein]

Nor should they be required to! Never make a human being do what the
computer can do, particularly if the human being runs the risk of
forgetting to (or purposefully neglecting to) do it!

"Never send a human to do a machine's job." - Agent Smith, The Matrix.



I'll all typed out on this one - just two different opinions.

Cheers,

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

 

[David W. Fenton]

In reading your other reply to Armen, I see that you are only
using Input masks for date / time data type. I use the mm/dd/yyyy
format myself, which seems to work just fine.

Formatting the display does nothing at all to restrict input, and
depends on the user looking at what they've entered and thinking
about it. I don't trust users to do that, hence, date input masks to
restrict them to valid input.

Now, that doesn't mean the dates make sense -- you may still need
code to check a particular date value against other dates (date of
enrollment, say, shouldn't be less than date of birth).

I do use input masks for other purposes on an ad hoc basis, but the
one type of data I use it for all the time and in all applications
is for inputting dates (even with a date picker I still allow the
user to manually type in a date, rather than having to navigate the
date picker, which can be particularly annoying if the date you want
is distant from the default range the date picker displays).