Validation of text

Is it possible to validate entered text so that it must start with a
predefine letter, say 'H', followed by a numeric value of varying length?

Sure, in the Before Update event of the text box:

If Left(Me.SomeControl, 1) "H" Then
MsgBox "Must Start with H"
Cancel = True
ElseIf Not IsNumeric(Right(Me.SomeControl, Len(Me.SomeControl) -1)) Then
MsgBox "Non - Numeric Characters found after the H"
Cancel = True
End If

"Confused Slug" wrote:

Is it possible to validate entered text so that it must start with a
predefine letter, say 'H', followed by a numeric value of varying length?

Klatuu wrote:
Is it possible to validate entered text so that it must start with a
predefine letter, say 'H', followed by a numeric value of varying length?

Sure, in the Before Update event of the text box

The OP is also advised to have a validation rule or CHECK constraint in
the database.

The basic pattern is

NOT LIKE 'H*[!0-9]*'

To do this properly, both ANSI (%) and non-ANSI (*) wildcard characters
should be accommodated, otherwise simply switching between DAO and ADO
could (inadvertently) circumvent data integrity checks e.g.

CREATE TABLE Test3 (
data_col VARCHAR(10) NOT NULL,
CONSTRAINT data_col__pattern
CHECK (
data_col NOT LIKE 'H%[!0-9]%'
AND data_col NOT LIKE 'H*[!0-9]*'
)
);

Jamie.

--

I would advise the OP Not to use database level validation. Your post,
itself, is a good argument against it.

"onedaywhen" wrote:


Klatuu wrote:
Is it possible to validate entered text so that it must start with a
predefine letter, say 'H', followed by a numeric value of varying length?

Sure, in the Before Update event of the text box

The OP is also advised to have a validation rule or CHECK constraint in
the database.

The basic pattern is

NOT LIKE 'H*[!0-9]*'

To do this properly, both ANSI (%) and non-ANSI (*) wildcard characters
should be accommodated, otherwise simply switching between DAO and ADO
could (inadvertently) circumvent data integrity checks e.g.

CREATE TABLE Test3 (
data_col VARCHAR(10) NOT NULL,
CONSTRAINT data_col__pattern
CHECK (
data_col NOT LIKE 'H%[!0-9]%'
AND data_col NOT LIKE 'H*[!0-9]*'
)
);

Jamie.

--

Klatuu wrote:
I would advise the OP Not to use database level validation.

Because...?

Your post,
itself, is a good argument against it.

I don't understand. I'm in favour of database validation, not arguing
against it. What do you mean?

TIA,
Jamie.

--

"onedaywhen" wrote:


Klatuu wrote:
I would advise the OP Not to use database level validation.

Because...?

To do this properly, both ANSI (%) and non-ANSI (*) wildcard characters
should be accommodated, otherwise simply switching between DAO and ADO
could (inadvertently) circumvent data integrity checks e.g.

Because table and field level validation are product specific. Should you
switch between ADO and DAO or decide to upsize to SQL Server, Oracle, etc. It
requires a lot of rewrite. Validation done at the form level doesn't care
what the DB engine is.

Your post,
itself, is a good argument against it.

I don't understand. I'm in favour of database validation, not arguing
against it. What do you mean?

There are arguments on both sides of this issue. I prefer form level
validation because it is not DB engine specific and because I feel I have
more control over the error handling that way.

If any MVPs have weighed in in favor of table/field validation, I have not
seen it. If any choose to and have a convincing argument in it's favor, I am
open to new ideas.

TIA,
Jamie.

--

Klatuu wrote:

I would advise the OP Not to use database level
validation [because] to do this properly, both
ANSI (%) and non-ANSI (*) wildcard characters
should be accommodated, otherwise simply switching
between DAO and ADO could (inadvertently)
circumvent data integrity checks

I'm not sure what you are saying here.

If you are saying that designing effective database constraints is hard
work then I entirely agree. If you conclude that database constraints
are not worth the effort then I couldn't disagree more.

Consider that if you have no database level constraints then your data
integrity is can be destroyed by DAO *and* ADO...actually any
technology that can connect to your database e.g. do your users have
Excel? But most significantly, your data integrity is at the mercy of
bugs in your front end application.

Because table and field level validation are product specific. Should you
upsize to SQL Server, Oracle, etc. It
requires a lot of rewrite.

You have portability issues in mind and that is a good thing.

I have been suggesting that you write your database constraints in
standard SQL *because* it can be easily ported to other platforms. Can
your VBA code by easily ported to other front end application
programming languages? Does it even port to VB.NET? Other forms-based
apps in the MS Office suite?

There are arguments on both sides of this issue.

Of course. But have you considered that you can (and if you are doing
your best, should) write form-level validation *and* database level
validation? Yes, more hard work but that's what being a professional is
all about, don't you think?

If any MVPs have weighed in in favor of table/field validation, I have not
seen it. If any choose to and have a convincing argument in it's favor, I am
open to new ideas.

I'm glad to hear you are open-minded but the idea of putting
constraints in the database is not new.

OK, if MVPs is what it takes, I'll search on my favourite MVPs (in no
particular order):

John Vinson
http://groups.google.com/group/micro...21397365f07afe
"Good point Jamie - though the OP crossposted to forms and formscoding
as well. It would probably be wise to do both - on the Form so you can
control the error message and make it friendlier (or more hostile if
you prefer g), and in the table to prevent "backdoor" entry of
invalid data."

Allen Browne
http://groups.google.com/group/micro...2525459bb1531e
"Start with a bound form (bound to a table), with bound controls (bound
to
fields.) You may find that you don't actually need any code.
Particularly if
you used the Validation Rule property of the fields in your table, you
can
probably avoid any code at all."

Albert D.Kallal
http://groups.google.com/group/micro...d1f38048d27ac8
By the way, we could have skipped the "code" example, and not used the
before update. One could simply enter the following expression as a
validation rule in the properties sheet.

Ken Snell
http://groups.google.com/group/micro...f746a8b0ee99fd
"where you use the Validation Rule sometimes is a matter of
preference; other times, it's a matter of where it's easier to
write/test/handle the validation. If you put the Validation Rule in the

table, ACCESS will generate an error message that may or may not be
meaningful to your user"

Michel 'Vanderghast' Walsh
http://groups.google.com/group/micro...6ec7e912d24be8
"I wrote many CHECK constraint between tables, before, in Jet"

Tibor Karaszi
http://groups.google.com/group/micro...3934af18bc8c0a
['Why do we do double work?'] "Nothing new here, just wanted to push
the point that constraints can be a great timesaver by
finding incorrect code immediately!"

I'm not saying the above people agree with any particular point of
view, rather I would suggest they at least seem *not* to opposed the
practise of putting constraints in the database layer.

BTW why are you fixating on MVPs? I'm sure every one of them would
agree there are countless more enthusiastic volunteers (Tim Fergusson
and david Epsom come quickly to mind), eminent authors, etc whose
opinions are just as valuable.

I would urge you to spend a few minutes reading this series of three
short articles on the location of database constraints:

http://www.dbazine.com/ofinterest/oi-articles/celko27/

Jamie.

--

onedaywhen wrote:
Klatuu wrote:
Is it possible to validate entered text so that it must start with a
predefine letter, say 'H', followed by a numeric value of varying length?
The basic pattern is

NOT LIKE 'H*[!0-9]*'

Further thought: you may want to avoid 'leading' zeros in the numeric
portion e.g. basic pattern:

NOT LIKE 'H*[!0-9]*' AND NOT LIKE 'H0*'

Jamie.

--

Good morning.
Great Feedback.


"onedaywhen" wrote:


Klatuu wrote:

I would advise the OP Not to use database level
validation [because] to do this properly, both
ANSI (%) and non-ANSI (*) wildcard characters
should be accommodated, otherwise simply switching
between DAO and ADO could (inadvertently)
circumvent data integrity checks

I'm not sure what you are saying here.

If you are saying that designing effective database constraints is hard
work then I entirely agree. If you conclude that database constraints
are not worth the effort then I couldn't disagree more.

It is not that they are not worth the effort. It is that I find it more
difficult to respond to a database generated error than an application
generated error. But, that may be just my problem. I would have to admit,
like most people, I tend to stay with what is familiar. I began programming
before there were databases. Everything was done at the application level.
There were no relationships or referential integrity that you didn't write
yourself.


Consider that if you have no database level constraints then your data
integrity is can be destroyed by DAO *and* ADO...actually any
technology that can connect to your database e.g. do your users have
Excel? But most significantly, your data integrity is at the mercy of
bugs in your front end application.

I agree.


Because table and field level validation are product specific. Should you
upsize to SQL Server, Oracle, etc. It
requires a lot of rewrite.

You have portability issues in mind and that is a good thing.

I have been suggesting that you write your database constraints in
standard SQL *because* it can be easily ported to other platforms. Can
your VBA code by easily ported to other front end application
programming languages? Does it even port to VB.NET? Other forms-based
apps in the MS Office suite?

This is not a valid argument. All applications are product specific. But
then, and perhaps to a lesser degree, so are databases. Standard SQL isn't.
And that is not surprising. Since there have been computers, committies of
one kind or another have come up with "Standards". Then all the vendors get
on board and say "We support THE Standard insert technology here, but we
also offer 'extensions'". To name a few: COBOL, BASIC, XBASE, Java, SQL,
etc, etc, etc.
So we programmers start using the extensions and soon have a product that is
difficult to port. Even considering upsizing an mdb app to an adp app using
SQL Server takes more than running the upsize wizard.
That being said, we still want to consider the impact on upsizing when we
design or apps and databases.


There are arguments on both sides of this issue.

Of course. But have you considered that you can (and if you are doing
your best, should) write form-level validation *and* database level
validation? Yes, more hard work but that's what being a professional is
all about, don't you think?

Sure. I will certainly agree that using database level validation where it
is appropriate, in a perfect world, would be the most ideal situation for all
the obvious reasons. My main reservation is that dealing with a database
level error is much more difficult in Access than dealing with an application
level error.

If any MVPs have weighed in in favor of table/field validation, I have not
seen it. If any choose to and have a convincing argument in it's favor, I am
open to new ideas.

I'm glad to hear you are open-minded but the idea of putting
constraints in the database is not new.

After 30 years in this business, there is very little, at the conceptual
level, that is new. My first encounter with the concept of enforced data
integrity was in a COBOL/ISAM shop where all IO was done using canned
procedures that enforced the data rules.


OK, if MVPs is what it takes, I'll search on my favourite MVPs (in no
particular order):

John Vinson
http://groups.google.com/group/micro...21397365f07afe
"Good point Jamie - though the OP crossposted to forms and formscoding
as well. It would probably be wise to do both - on the Form so you can
control the error message and make it friendlier (or more hostile if
you prefer g), and in the table to prevent "backdoor" entry of
invalid data."

Allen Browne
http://groups.google.com/group/micro...2525459bb1531e
"Start with a bound form (bound to a table), with bound controls (bound
to
fields.) You may find that you don't actually need any code.
Particularly if
you used the Validation Rule property of the fields in your table, you
can
probably avoid any code at all."

Albert D.Kallal
http://groups.google.com/group/micro...d1f38048d27ac8
By the way, we could have skipped the "code" example, and not used the
before update. One could simply enter the following expression as a
validation rule in the properties sheet.

Ken Snell
http://groups.google.com/group/micro...f746a8b0ee99fd
"where you use the Validation Rule sometimes is a matter of
preference; other times, it's a matter of where it's easier to
write/test/handle the validation. If you put the Validation Rule in the

table, ACCESS will generate an error message that may or may not be
meaningful to your user"

This is my main aversion to database level validation:

If you put the Validation Rule in the table, ACCESS will generate an error
message that may or may not be meaningful to your user"



Michel 'Vanderghast' Walsh
http://groups.google.com/group/micro...6ec7e912d24be8
"I wrote many CHECK constraint between tables, before, in Jet"

Tibor Karaszi
http://groups.google.com/group/micro...3934af18bc8c0a
['Why do we do double work?'] "Nothing new here, just wanted to push
the point that constraints can be a great timesaver by
finding incorrect code immediately!"

I'm not saying the above people agree with any particular point of
view, rather I would suggest they at least seem *not* to opposed the
practise of putting constraints in the database layer.

BTW why are you fixating on MVPs? I'm sure every one of them would
agree there are countless more enthusiastic volunteers (Tim Fergusson
and david Epsom come quickly to mind), eminent authors, etc whose
opinions are just as valuable.

Not fixating on MVPs. There are some who are not, I think, that are more
knowledgeable than some MVPs. It is only a credential. MVP is a
recongnition of knowledge and participation. But, let is not confuse
knowledge with wisdom


I would urge you to spend a few minutes reading this series of three
short articles on the location of database constraints:

http://www.dbazine.com/ofinterest/oi-articles/celko27/

Good articles by an arogant author. Thanks for the reference.

I certinly have enjoyed our discussion. You make some good points worth
serious consideration.

Jamie.

--

Klatuu wrote:
After 30 years in this business, there is very little, at the conceptual
level, that is new. My first encounter with the concept of enforced data
integrity was in a COBOL/ISAM shop where all IO was done using canned
procedures that enforced the data rules.

I'm just about 'wise' enough to have used pre-SQL ISAMs. That's why I
just love thing like CHECK constraints in the database: simple,
flexible, powerful. They do port to other SQLs quite well, noting that
I have to dumb-down when porting to SQL Server because Jet's are better
implemented.

I certinly have enjoyed our discussion. You make some good points worth
serious consideration.

Likewise ;-)

Jamie.

--