Validation Rule

[Guest]

hi all

I want to make a validation rule for date treated and I want this date to be
any date before that data was captured. Also I have a final concentrate
weight and a total sample weight. I want the final concentrate weight to be
smaller than total weight.

I just cannot get the expressions right, please help.

 

[Guest]

This appears to be two separate rules. There are two possible ways to
approach this, depending on how you want your form to work. One way would be
to do the validation for each rule in the Before Update event of the control
for the controls. The other would be to do both validations in the Before
Update event of the form. I will give an example below using the assumption
that the data capture date is entered before the date treated and that the
total weight is entered before the final concentrate weight.

In the Before Update event of the control for date treated:

If Me.txtDateTreated >= Nz(Me.txtDateCaptured,0) Then
MsgBox "Date Treated must be before Data Captured Date"
Cancel = True
End If

In the Before Update event of the control for final concentrate weight:

If Me.txtFinalConcentrateWeight >= Me.txtTotalSampleWeight Then
MsgBox "Final Concentrate Weight must be less than Total Sample Weight"
Cancel = True
End If

If you want to do the validation at the form level, then the above code
would go in the Before Update event of the form.

 

[Tim Ferguson]

I want to make a validation rule for date treated and I want this date
to be any date before that data was captured. Also I have a final
concentrate weight and a total sample weight. I want the final
concentrate weight to be smaller than total weight.

put something like this in the *Table level* validation rule:

((TreatmentDate < CaptureDate)
OR (TreatmentDate IS NULL)
OR (CaptureDate IS NULL))
AND
((FinalConcentrateWeight < TotalSampleWeight)
OR (FinalConcentrateWeight IS NULL)
OR (TotalSampleWeight IS NULL))

You need to put it in all on one line, but you get the picture. To get at
the Table Validation rules: open the table in Design View, open the
Properties Sheet, and it's half way down.

PS you would be well advised to use the BeforeUpdate event to intercept
errors in the form as well, because like that you can react intelligently
to the error. The Validation Rule just responds with a pretty dull db
engine error message.

Hope that helps


Tim F

 

[Guest]

I know a lot of people use table level and field level validation rules, but
I never do. The rules are not as apparent to another developer reading my
code and if the database is ever upsized, you may not be able to use them. I
always suggest that all validation be done at the form level.

 

[Rick Brandt]

I know a lot of people use table level and field level validation
rules, but I never do. The rules are not as apparent to another
developer reading my code and if the database is ever upsized, you
may not be able to use them. I always suggest that all validation be
done at the form level.

You would be quite in the minority with that opinion. It is a mindset that
is usually taken when the developer considers the database to be "owned" by
his application and never considers that some other application or data
access method will ever be used.

I personally consider the database to be an entity unto itself and that
while it might *predominantly* be interacted with from my application I
never presume that that will always be the case. With that mindset you have
to place as much data integrity into the database itself if you don't want
to find yourself with a bunch of garbage data.

 

[Guest]

Whether it is a minority position, I don't know. You point regarding other
applications using the data is valid; however, I still stand by my basic
reasons to avoid using database level validation.

 

[Tim Ferguson]

I know a lot of people use table level and field level validation
rules, but I never do.

You are wrong not to.

The rules are not as apparent to another developer

Cobblers. Any decent developer will look first at the data structure, and
this includes above all the constraints on unique keys, foreign keys,
check constraints etc.

Remember that a jet database can be altered by anyone with appropriate
permissions, using a variety of tools from querydefs, VBA, VBS, Word,
PERL, dot-Net etc etc. I wrote a database app in Corel-Draw once using a
mdb to control the data. DB engines are there to protect your data, and
they will do it regardless of where the commands come from.

reading my code and if the database is ever upsized, you may
not be able to use them.

This is a joke, right? If you move the database from an mdb, then the
ONLY things you take with you will be the schema. No forms, no VBA, no
BeforeUpdate events. If it's not in the schema then you lose all your
protection.

I always suggest that all validation be done
at the form level.

Okay, but not for any of the reasons you are suggesting.

All the best


Tim F

 

[Guest]

Tim,

The rude tone of your post overshadows perfectly valid points. We should be
able to discuss theory and philosophy without insults. Please see my comments
below:

You are wrong not to.

Not necessarliy. Consider a situation where data has to be entered into a
table, but some of the data is not available. The business rules require
that incomplete records be entered; however, some of the missing data
requires validation. Programmatically it can be allowed, but with
field/table validation, it cannot.
Your response will probably be "Don't allow that". It would be great if it
were that easy. I have been involved in the development of two large
applications where it was required. What we did was create a status field to
mark the record "incomplete" and programmaticly deal with it.
I still maintain that you have more control over the situation with
control/form validation.

Cobblers. Any decent developer will look first at the data structure, and
this includes above all the constraints on unique keys, foreign keys,
check constraints etc.

You left out relationships. They should, but do they always?

Remember that a jet database can be altered by anyone with appropriate
permissions, using a variety of tools from querydefs, VBA, VBS, Word,
PERL, dot-Net etc etc. I wrote a database app in Corel-Draw once using a
mdb to control the data. DB engines are there to protect your data, and
they will do it regardless of where the commands come from.

True, and that is a good case for table/field level validation. I have yet
to be faced with that issue.

This is a joke, right? If you move the database from an mdb, then the
ONLY things you take with you will be the schema. No forms, no VBA, no
BeforeUpdate events. If it's not in the schema then you lose all your
protection.

I was not joking, this is the comment I consider rude. We are talking
backend data, not other frontend objects. If you upsize the backend to a
different database engine, you cannot be certain the rules imposed will be
honored or understood by another engine.

Okay, but not for any of the reasons you are suggesting.

We disagree, Tim. And that is okay. We learn more from those with
differing opinions than those who use the same techniques. Certainly, some
things in database and application design are bad ideas and should never be
done. There are, however, multiple valid ways to accomplish the same goals.
Your post has some valid points and I will do some research and
experimenting with table/field validation. To suggest I am wrong is narrow
minded. Notice I never said table/field level validation is wrong, I only
said I don't use it and I recommend against it.
I can see some value to it in that if you have multiple forms that use the
same table, then you only have to write the rule once.
In any case, I appreciate your comments.

 

[Tim Ferguson]

Not necessarliy. Consider a situation where data has to be entered
into a table, but some of the data is not available. The business
rules require that incomplete records be entered; however, some of the
missing data requires validation.

This is a problem of faulty analysis. Either the data are mandatory (in
which case the user has to collect them) or they aren't (in which case
they should not be marked as required). You can't have your cake and eat
it too.

If you need an intermediate stage of collecting incomplete and unchecked
data, then that needs to be built in to the design -- there is no place
for lowering the protection around clean data just to make the designer's
job easier.

You left out relationships.

"... foreign keys, ..."

I was not joking, this is the comment I consider rude. We are talking
backend data, not other frontend objects.

No: you said in your first post:-

and that explicitly means managing data integrity in front end objects.
It's an approach that has its feet in dBase and Paradox, and really
deserves to stay mired there.

There are, however, multiple valid ways to
accomplish the same goals.

There is more than forty years of mathematical research in R theory: it's
not opinion or value judgements, and some things are simply Known Facts.
Implementing formal procedures at the DB engine is the only way to
guarantee the correctness of the data model.

B Wishes


Tim F

 

[John Nurick]

Not necessarliy. Consider a situation where data has to be entered into a
table, but some of the data is not available. The business rules require
that incomplete records be entered; however, some of the missing data
requires validation. Programmatically it can be allowed, but with
field/table validation, it cannot.
Your response will probably be "Don't allow that". It would be great if it
were that easy. I have been involved in the development of two large
applications where it was required. What we did was create a status field to
mark the record "incomplete" and programmaticly deal with it.
I still maintain that you have more control over the situation with
control/form validation.

Are you talking here about the limitations of engine-level validation in
Jet? If your back end was a full-blown SQL server database would you
refuse the opportunity to handle this at engine-level? (e.g. set a
table-level constraint along the lines of
[Status] must be "incomplete" if any of fields A, B, C... IS NULL
and field-level constraints along the lines of
IS NULL OR is a legal value

I entirely agree that the UI has to be right and that engine-level
validation doesn't contribute much in that regard. But I'm 100% with Tim
on its importance in protecting the data (from myself if not from anyone
else!).

 

[Marshall Barton]

Are you guy's arguing/discussing the difference between
table integrity validation and business rules validation?

IMO, as Tim says, the former **must** be in the table.
OTOH, as klatuu states, the latter can (and probably) should
be in the front end.

Is that enough ante to get in this game ;-)

 

[Guest]

I will accept what you and John have to say on this issue with one exception.
You must always bend to the business rules. You do not change the way a
large international corporation does business so you can have a perfect
database. Too many times I have seen databases so over-normalized they were
almost useless. This is just as poor a design as an unnormalized,
unprotected database.
Here is why, in my case, we have some tables that have to allow incomplete
data.
A new project is approved and we have about 70% of all the information we
need to enter correct data. Getting the rest of the data can take days. In
the meantime, employess in several locations around the country have to have
this project available in a separate timekeeping system to be able to charge
time to it. Therefore, we have to have a minimal amount of information in
the database so the timekeeping system will have it available.
Were we to apply all the complex rules we have on the data, there could be a
serious lag in getting time entered. From a business prespective, this is
not acceptable. There are rules like if it is a recurring project it must
have values in these fields and these fields must be empty, unless it is a
capitol expense, then the rules change again.
The only way for us to get information in a timely fashion is to enter
partial (incomplete) data is to have a status flag that marks the record as
incomplete. This allows us to bypass validation rules and also warns the
user before any reporting or batch process that incomplete data exists.

The other problem I have experienced in more that one application is that
table/field validation is not robust enough to do complex cross field
validations.

I respect both of your opinions, and as I said before, I will investigate it
further. This then, brings me to a question. Let's say in a table Field_1
must be either "RR" or "NR" (easy to do). But then, if Field_1 is "RR", the
Field_2 must be a one of a list of 5 values but if it is "NR" then Field_2
has to be empty and Field_3 can be either empty or one of a list of 10
possible values. And this is only the beginning.

I don't believe it can be done without VBA. Prove me wrong, and I will use
it.

Now, I don't want to hear "BAD DESIGN" These are the Business Rules, The
values have to match values in the accounting system. This is the real world.

 

[Tim Ferguson]

IMO, as Tim says, the former **must** be in the table.
OTOH, as klatuu states, the latter can (and probably) should
be in the front end.

Actually, Tim agrees with the second one too...


Tim F

 

[Tim Ferguson]

Too many times I have seen databases so
over-normalized they were almost useless. This is just as poor a
design as an unnormalized, unprotected database.

I think we must have different ideas of what normalisation means. There
is no such thing as "over-normalisation" -- there is only inadequate
analysis. If a database does not model what it's there to model, then it
has been done wrong, and any application of R theory or denormalisation
is not going to rescue it.

Here is why, in my case, we have some tables that have to allow
incomplete data.

Normalisation has nothing to say about completeness of data. R theory has
a little to say about the use of NULL data, but completeness or otherwise
is purely a matter of semantics and that is a matter of analysis.

A new project is approved ... that incomplete data exists.

I did not follow the example, but suffice it to say that analysis is not
neccessarily easy. Complex business procedures are harder to model than
simple one. As the USians say "suck it up"... it's why good systems
analysts get paid a lot of money.

The other problem I have experienced in more that one application is
that table/field validation is not robust enough to do complex cross
field validations.

If you mean that real-life dbms implementations have limitations, then of
course you are right. Moving up from cheap desktop platforms to more
sophisticated enterprise systems allows more complex constraints. There
will always be something that someone needs that cannot be achieved with
present day equipment. But it's pretty rare in practice and not relevant
to this discussion. Way back in the mists of time, this thread's OP
wanted to compare two dates: even jet/access 1.1 could manage that!

This then, brings me to a question. Let's
say in a table Field_1 must be either "RR" or "NR" (easy to do). But
then, if Field_1 is "RR", the Field_2 must be a one of a list of 5
values but if it is "NR" then Field_2 has to be empty and Field_3 can
be either empty or one of a list of 10 possible values.

There is a complex set of functional dependencies here, and it needs a
lot more close inspection. I strongly suspect that a different design
would be in order, but only with a great deal deeper understanding of
what is on offer here.

B Wishes


Tim F

 

[Guest]

Tim,
I don't disagree with the theory behind anything you say; however, please
try to live in the real world. We cannot always control the data we are
provided or the contraints put on us by complex business logic.

The database I support, I beg on a daily basis to be allowed to totally
redesign and remodel it, but am not allowed the privledge.

 

[Guest]

SQL Server has much more robust egine level validation capabilites. I would
use it there. After following this thread, I am even considering how I might
apply for the app I have to support now. I really don't think the
limitations of Jet will allow it.

Not necessarliy. Consider a situation where data has to be entered into a
table, but some of the data is not available. The business rules require
that incomplete records be entered; however, some of the missing data
requires validation. Programmatically it can be allowed, but with
field/table validation, it cannot.
Your response will probably be "Don't allow that". It would be great if it
were that easy. I have been involved in the development of two large
applications where it was required. What we did was create a status field to
mark the record "incomplete" and programmaticly deal with it.
I still maintain that you have more control over the situation with
control/form validation.

Are you talking here about the limitations of engine-level validation in
Jet? If your back end was a full-blown SQL server database would you
refuse the opportunity to handle this at engine-level? (e.g. set a
table-level constraint along the lines of
[Status] must be "incomplete" if any of fields A, B, C... IS NULL
and field-level constraints along the lines of
IS NULL OR is a legal value

I entirely agree that the UI has to be right and that engine-level
validation doesn't contribute much in that regard. But I'm 100% with Tim
on its importance in protecting the data (from myself if not from anyone
else!).

 

[Guest]

Aha, someone who understands the difference. My discussion is about business
rule validation. Jet has limited capability in that area.

Are you guy's arguing/discussing the difference between
table integrity validation and business rules validation?

IMO, as Tim says, the former **must** be in the table.
OTOH, as klatuu states, the latter can (and probably) should
be in the front end.

Is that enough ante to get in this game ;-)
--
Marsh
MVP [MS Access]

The rude tone of your post overshadows perfectly valid points. We should be
able to discuss theory and philosophy without insults. Please see my comments
below:


Not necessarliy. Consider a situation where data has to be entered into a
table, but some of the data is not available. The business rules require
that incomplete records be entered; however, some of the missing data
requires validation. Programmatically it can be allowed, but with
field/table validation, it cannot.
Your response will probably be "Don't allow that". It would be great if it
were that easy. I have been involved in the development of two large
applications where it was required. What we did was create a status field to
mark the record "incomplete" and programmaticly deal with it.
I still maintain that you have more control over the situation with
control/form validation.
You left out relationships. They should, but do they always?
True, and that is a good case for table/field level validation. I have yet
to be faced with that issue.
I was not joking, this is the comment I consider rude. We are talking
backend data, not other frontend objects. If you upsize the backend to a
different database engine, you cannot be certain the rules imposed will be
honored or understood by another engine.
We disagree, Tim. And that is okay. We learn more from those with
differing opinions than those who use the same techniques. Certainly, some
things in database and application design are bad ideas and should never be
done. There are, however, multiple valid ways to accomplish the same goals.
Your post has some valid points and I will do some research and
experimenting with table/field validation. To suggest I am wrong is narrow
minded. Notice I never said table/field level validation is wrong, I only
said I don't use it and I recommend against it.
I can see some value to it in that if you have multiple forms that use the
same table, then you only have to write the rule once.
In any case, I appreciate your comments.

 

[Guest]

This has been an enlightening and valuable discussion. I appreciate all the
thoughts and opinions. Marshall put it in perspective the best. I have to
think in terms of the difference between data integraty and business rules.
There is some overlap there, so I will have to be sure which I am working
with.

So then, let me ask a technical question regarding this. If, as I stated
eariler, I have to be able to accept a record where the business rules are
allowed to be violated that would also violate the data rules and I have the
"incomplete" status field. Would it be reasonalbe to include in the engine
validation a check of the status in addition to any other rules that apply?
And, If data rules change for one field based on the value of another field,
how would that be done at the engine level?

 

[John Nurick]

I have to be able to accept a record where the business rules are
allowed to be violated

ISTM that if business rules have to be violated in order for the
organisation to function effectively then there's something wrong with
the rules. (Whether management can understand that is a different
question entirely!)

 

[Guest]

Mostly true, John. But this is one of the largest organizations in the
world. The flow of information takes time.

A project is approved. Work can start immediately and the project number
must be available to the timekeeping sytem. The minimum data necessary for
the timekeeping system is available, but some other accounting data has not
yet been established by another business unit at another location. We need to
enter the information available into the system so the timekeeping system can
pick it up. We cannot wait until the accounting data is available.
For a period of from a day to a week, the accounting data is not in the
system; however, to make a complete record, the accounting data is required.
To delay work until until all data is available costs about $50,000 a day.
I seriously doubt we would be allowed to delay work until all data is
available.

What I would like to do, is redesign the system so the minimum data
constititues a complete and separate record in a child table and the
accounting data in another child table, both related to the basic project
information. Nope, they will not approve the budget.

I have inherited probably the worst database design there ever was.