Validate a "Monday" Date

[Guest]

I am trying to restrict date entry so that only a Monday date can be entered
into a table. Has anyone tried this and if so, how is it accomplished?

 

[RoyVidar]

I am trying to restrict date entry so that only a Monday date can be
entered into a table. Has anyone tried this and if so, how is it
accomplished?

If you want to restrict for the table, you could try the validation
rule property of the field - I think something like the following
might work

datepart("w", [NameOfYourDateField], 1) = 2

Remember to use the validation text property too, to give a meaningful
message to the user.

You can also use code in a form - for instance in the before update
event of the control or the form itself, to achive similar.

 

[Guest]

Thank you so much. It worked! I am really green at using any type of
syntax. I am ok with using formulas in Excel, but Access is a completely new
ballgame.
--
JJ

I am trying to restrict date entry so that only a Monday date can be
entered into a table. Has anyone tried this and if so, how is it
accomplished?

If you want to restrict for the table, you could try the validation
rule property of the field - I think something like the following
might work

datepart("w", [NameOfYourDateField], 1) = 2

Remember to use the validation text property too, to give a meaningful
message to the user.

You can also use code in a form - for instance in the before update
event of the control or the form itself, to achive similar.

 

[Guest]

Which is better, to use this in the table or in the form that feeds into the
table? Or, do I have to do both? I have not even ventured into any "before
update" events. I really am green at this, and trying to teach myself.--
JJ

I am trying to restrict date entry so that only a Monday date can be
entered into a table. Has anyone tried this and if so, how is it
accomplished?

If you want to restrict for the table, you could try the validation
rule property of the field - I think something like the following
might work

datepart("w", [NameOfYourDateField], 1) = 2

Remember to use the validation text property too, to give a meaningful
message to the user.

You can also use code in a form - for instance in the before update
event of the control or the form itself, to achive similar.

 

[RoyVidar]

Which is better, to use this in the table or in the form that feeds
into the table? Or, do I have to do both? I have not even
ventured into any "before update" events. I really am green at
this, and trying to teach myself.-- JJ

That is a good question.

I think that engine level validation - i e having the database engine
do the validation, as we're doing here, is probably the only way to
be 100% sure there's no bad data in the table.

Then, the common opinion around here is that one should never allow
the users direct access to tables and queries, but give them forms
and reports to maintain and view the information, using form or
control events should be enough. When getting "the hang of it", it's
often easier to make more user friendly validation with form coding

Lot of developers will use both, depending on the requirements.
Engine level validation, to ensure no bad data is entered into the
table, and form level validation to pick up errors when entering
information through forms.

It's not unusual that you'll need to enter/alter information in(to)
the table in other ways than through forms. For instance from other
applications connecting to your database table, through action
queries etc..., in which case form level validation won't stop bad
data, but engine level validation will.

So, I think the requirements should decide. Sometimes one is to be
preferred over the other, sometimes one will need both to ensure
sufficient integrity.

 

[Guest]

The only reason to avoid table level or field level validation is if you
plan to or think you might upsize at some point in the future. Jet
validation may not be understood by all database engines and would have to be
rewritten.
Other than that, you are correct, data level (table or field) will ensure
nothing unwanted gets in.
You can even get bad data in a field in your own application with form
validation. Before and After Update events do not fire if a control is
updated programmatically.

 

[RoyVidar]

The only reason to avoid table level or field level validation is if
you plan to or think you might upsize at some point in the future.
Jet validation may not be understood by all database engines and
would have to be rewritten.

I think that if data quality matters, there is no reason to avoid
validation at engine level. There is simply no other way of ensuring
the quality of the data, unless one is 100% sure that the information
will *only* be manipulated through forms with appropriate code (and
how realistic is that?). Also, databases and database engines are
specially designed to deal with data integrity and validation, why
throw that away and roll your own? Much easier to let the database do
what it's intended for, and write form validation where you need
more/better flexibility?

Rewriting validations in the flavour of a new engine would be a one
time operation that, in my opinion, is worth the time and effort.

Other than that, you are correct, data level (table or field) will
ensure nothing unwanted gets in.

Hm, Jet allows for table level validation, but as far as I know, only
row level validation is availabe through the Access interface, or am
I missing something (can only refer to another column in the same row,
not all rows in the table)?

You can even get bad data in a field in your own application with
form validation. Before and After Update events do not fire if a
control is updated programmatically.

Good argument to distrust form validation, and allow the database
engine to handle the validation, ;-)

However, if you set focus to the control, and assign to the .Text
property of it, those events are fired (though I don't use the after
update for valdiation).

 

[Guest]

You must have thought I disagree with you.. Sorry, that is not the case. I
was only adding some comments regarding the use of form level validation.

the quality of the data, unless one is 100% sure that the information
will *only* be manipulated through forms with appropriate code (and
how realistic is that?).

Not unrealistic at all if you control your application correctly. I will
agree there are cases where that is beyond your control. For example, I had
one application where the timekeeping system used my datatabase, but I had no
control over the code. For this engine level validation would be the only
safe validation.

Rewriting validations in the flavour of a new engine would be a one
time operation that, in my opinion, is worth the time and effort.

Agreed, I just wanted to point that out.

However, if you set focus to the control, and assign to the .Text
property of it, those events are fired (though I don't use the after
update for valdiation).

Are you sure you are not thinking VB6? I am not aware they will fire even
under these circumstances in Access. I may test this to be sure.