Validation Rules

J

Jay

I have two date fields in the same table & I want a validation rule to
ensure that date2 >= date1, for each record. I have tried the same syntax
as, say a select statament (square brackets etc.) but can't get it to work?

In the validation rule field, how do I refer to a field in the same table?
Using this example, what would I need to enter for a validation of >=date2,
assuming a table name of say, Assignments?

I tried:
=[Assignments].date1
Click to expand...

Any help appreciated.

Cheers

-Jay-
 
G

Guest

Private Sub FormDateInputField_BeforeUpdate(Cancel As Integer)
On Error GoTo NamOfDateField2_BeforeUpdate_Err

If (NameOfTable!NameOfDate1 > NameOfTable!NameOfDateField2) Then
Beep
MsgBox "This date must be after date 1", vbOKOnly, ""
End If
If (NameOfTable!NameOfDate1 > NameOfTable!NameOfDateField2) Then
DoCmd.CancelEvent
End If


FormDateInputField_BeforeUpdate_Exit:
Exit Sub

FormDateInputField_BeforeUpdate_Err:
MsgBox Error$
Resume FormDateInputField_BeforeUpdate_Exit

End Sub


I have out the validation rule on the input form rather than on the table.

In this I have created a text box on the input form called
FormDateInputField (you will need to change this). This code will cause a
beep, a message (which you will need to change) and then cancel the input so
the a user will need to do it again.

There are many many many things you can do with code on a form rather than
trying to a force a validation rule on a table so this is a better idea.

Hope this leads you in the right direction

--
Wayne




Jay said:
I have two date fields in the same table & I want a validation rule to
ensure that date2 >= date1, for each record. I have tried the same syntax
as, say a select statament (square brackets etc.) but can't get it to work?

In the validation rule field, how do I refer to a field in the same table?
Using this example, what would I need to enter for a validation of >=date2,
assuming a table name of say, Assignments?

I tried:
=[Assignments].date1
Click to expand...

Any help appreciated.

Cheers

-Jay-
Click to expand...
 
S

Steve Schapel

Jay,

In the design view of the table, select Properties from the View menu.
In the Validation Rule property, simply type...
[date2]>=[date1]

End of story, except you will probably also want to put something
suitable in the Validation Text property.

Oh, and if it's possible for either or both of these fields to be left
blank, you would also need to allow for that.

--
Steve Schapel, Microsoft Access MVP

I have two date fields in the same table & I want a validation rule to
ensure that date2 >= date1, for each record. I have tried the same syntax
as, say a select statament (square brackets etc.) but can't get it to work?

In the validation rule field, how do I refer to a field in the same table?
Using this example, what would I need to enter for a validation of >=date2,
assuming a table name of say, Assignments?

I tried:
=[Assignments].date1
Click to expand...

Any help appreciated.

Cheers

-Jay-
Click to expand...
 
J

Jay

Jay,

In the design view of the table, select Properties from the View menu.
In the Validation Rule property, simply type...
[date2]>=[date1]

End of story, except you will probably also want to put something
suitable in the Validation Text property.

Oh, and if it's possible for either or both of these fields to be left
blank, you would also need to allow for that.
Click to expand...

Cheers Steve,

I'd not thought about allowing for a null value.

D'you mind me asking why input the validation rule to the table properties
tab & not the Validation Rule in the General tab relating to the actual
field Date2?

Is it that validation rules for specific fields can't refer to other fields?

Thanks for your help,


Jay.
 
S

Steve Schapel

Jay said:
Is it that validation rules for specific fields can't refer to other fields?
Click to expand...

Got it in one, Jay. There are table-level validation rules and
field-level validation rules, and the distinction between them is pretty
much as you mentioned.
 
J

Jay

Got it in one, Jay. There are table-level validation rules and
field-level validation rules, and the distinction between them is pretty
much as you mentioned.
Click to expand...

Thanks for confirming it Steve. I've got 3 Access books here (new job,
*steep* learning curve:) and it's surprising how litlle things like this
aren't covered.

So what if you have a_few validation rules that need to refer to other
fields? Can you put them all in the table's Validation Rule property?
Following on from the same example, say I had 4 date fields (1 to 4) and
needed the following rules:

[date2]>=[date1]
[date4]>=[date3]

How would I enter them both in the Val'n Rule property?

Many thanks,

-Jay-
 
S

Steve Schapel

Jay,

[date2]>=[date1] And [date4]>=[date3]

The Validation Rule is evaluated at the point where you try to save the
record, so if *any* of the conditions within a complex rule are
violated, it will tell you. If it gets too complicated, or if you want
the invalidity to be pinpointed for the user, you would tend towards
doing the validation in code behind the form.
 
J

Jay

Jay,

[date2]>=[date1] And [date4]>=[date3]

The Validation Rule is evaluated at the point where you try to save the
record, so if *any* of the conditions within a complex rule are
violated, it will tell you. If it gets too complicated, or if you want
the invalidity to be pinpointed for the user, you would tend towards
doing the validation in code behind the form.
Click to expand...


Steve,

Cheers for this. I appreciate you taking the time to answer my questions.
It's people like you who are really helping me with my new job.

Best Regards

Jay
 
S

Steve Schapel

It's a pleasure, Jay. Best wishes with your work.

Access is a truly wonderful product. But like any powerful tool, it
takes a bit of practice to get comfortable with it.
 
O

onedaywhen

Steve said:
In the design view of the table, select Properties from the View menu.
In the Validation Rule property, simply type...
[date2]>=[date1]

if it's possible for either or both of these fields to be left
blank, you would also need to allow for that.
Click to expand...

That's not the case because NULLs are treated differently for SQL DDL
('database design') than for SQL DML ('queries').

Whereas for SQL DML a NULL will casue rows to be removed from the
resultset, for SQL DDL a NULL causes a row to be given the benefit of
the doubt i.e. doesn't cause the 'validation rule' to fail and the row
is added/amended.

This is easily tested e.g.

CREATE TABLE Test5 (
test_ID INTEGER NOT NULL PRIMARY KEY,
start_date DATETIME DEFAULT DATE() NOT NULL,
end_date DATETIME,
CONSTRAINT test5__date_order
CHECK (start_date <= end_date)
)
;

This passes the rule:
INSERT INTO Test5 (test_ID, start_date, end_date)
VALUES (1, DATE(), DATE() + 1)
;

This (NULL) does not fail the rule:
INSERT INTO Test5 (test_ID, start_date, end_date)
VALUES (2, DATE(), NULL)
;

This fails the rule:
INSERT INTO Test5 (test_ID, start_date, end_date)
VALUES (3, DATE(), DATE() - 1)
;
 
T

Terry Kreft

I think you'll find that is what Steve was getting at when he said:-

"if it's possible for either or both of these fields to be left blank, you
would also need to allow for that."

Substitute the word Null or the word blank in the above quote.


--

Terry Kreft


onedaywhen said:
Steve said:
In the design view of the table, select Properties from the View menu.
In the Validation Rule property, simply type...
[date2]>=[date1]

if it's possible for either or both of these fields to be left
blank, you would also need to allow for that.
Click to expand...

That's not the case because NULLs are treated differently for SQL DDL
('database design') than for SQL DML ('queries').

Whereas for SQL DML a NULL will casue rows to be removed from the
resultset, for SQL DDL a NULL causes a row to be given the benefit of
the doubt i.e. doesn't cause the 'validation rule' to fail and the row
is added/amended.

This is easily tested e.g.

CREATE TABLE Test5 (
test_ID INTEGER NOT NULL PRIMARY KEY,
start_date DATETIME DEFAULT DATE() NOT NULL,
end_date DATETIME,
CONSTRAINT test5__date_order
CHECK (start_date <= end_date)
)
;

This passes the rule:
INSERT INTO Test5 (test_ID, start_date, end_date)
VALUES (1, DATE(), DATE() + 1)
;

This (NULL) does not fail the rule:
INSERT INTO Test5 (test_ID, start_date, end_date)
VALUES (2, DATE(), NULL)
;

This fails the rule:
INSERT INTO Test5 (test_ID, start_date, end_date)
VALUES (3, DATE(), DATE() - 1)
;
Click to expand...
 
O

onedaywhen

Terry said:
I think you'll find that is what Steve was getting at when he said:-

"if it's possible for either or both of these fields to be left blank, you
would also need to allow for that."

Substitute the word Null or the word blank in the above quote.
Click to expand...

Assuming you meant to say "Substitute the word Null for the word blank
in the above quote", that is indeed what I did (otherwise I'd have
asked what 'blank date' means - similar to a 'blind date' <g>?)

Jamie.

--
 
T

Terry Kreft

Actually, I meant to type <g>:-
"Substitute the word Null for the word blank in the above quote."


Well in that case I don't understand why you said:-
"That's not the case because NULLs are treated differently for SQL DDL"

As you appeared to be contradicting Steve, rather than agreeing but
expanding, which is what I would have expected in that case.

Ah well, trickeries of the written word I expect.
 
O

onedaywhen

Terry said:
Well in that case I don't understand why you said:-
"That's not the case because NULLs are treated differently for SQL DDL"

As you appeared to be contradicting Steve
Click to expand...

Yes, my intention was to contradict Steve.

As I understand it, Steve said the validation rule should explicitly
handle null values.

My point is, in the validation rule there is no need to handle NULL
values differently:

Rule: start_date <= end_date

2006-05-01 <= 2006-05-02 -- does not fail the rule

2006-05-01 <= NULL -- does not fail the rule

NULL <= [2006-05-02] -- does not fail the rule

2006-05-01 <= NULL -- does not fail the rule

2006-05-01 <= 2005-01-01 -- fails the rule

Jamie.

--
 
T

Terry Kreft

Looking back at the thread; I can't see where Steve said that the validation
rule should explicitly handle null values.


--

Terry Kreft


onedaywhen said:
Terry said:
Well in that case I don't understand why you said:-
"That's not the case because NULLs are treated differently for SQL DDL"

As you appeared to be contradicting Steve
Click to expand...

Yes, my intention was to contradict Steve.

As I understand it, Steve said the validation rule should explicitly
handle null values.

My point is, in the validation rule there is no need to handle NULL
values differently:

Rule: start_date <= end_date

2006-05-01 <= 2006-05-02 -- does not fail the rule

2006-05-01 <= NULL -- does not fail the rule

NULL <= [2006-05-02] -- does not fail the rule

2006-05-01 <= NULL -- does not fail the rule

2006-05-01 <= 2005-01-01 -- fails the rule

Jamie.
Click to expand...
 
J

Jay

Looking back at the thread; I can't see where Steve said that the validation
rule should explicitly handle null values.
Click to expand...

As the original poster I personally thought Steve meant that the rule
wouldn't necessarily accommodate Null values - not that they would fail the
rule.

He didn't say that the "validation rule should explicitly
handle null values", only to "allow for" null values which I took to mean
bear in mind that they will possibly not be accomodated.

-Jay-
 
S

Steve Schapel

Terry,

I did indeed mean that I thought the validation rule would have to
explicitly cater to nulls, if nulls were allowable in Jay's application.
I would have expected the validation rule to be violated if either
field was null. Jamie is correct, and I have learned something I didn't
know before. Thanks.

In fact, it seems the opposite is true, and on reflection I should have
seen that. If Jay did "not" want to permit nulls in either field, then
*that* would need to be validated.

By the way, Jamie, http://www.woug.info :)
 
J

Jay

Terry,

I did indeed mean that I thought the validation rule would have to
explicitly cater to nulls, if nulls were allowable in Jay's application.
I would have expected the validation rule to be violated if either
field was null. Jamie is correct, and I have learned something I didn't
know before. Thanks.

In fact, it seems the opposite is true, and on reflection I should have
seen that. If Jay did "not" want to permit nulls in either field, then
*that* would need to be validated.

By the way, Jamie, http://www.woug.info :)
Click to expand...

Well, I guess this begs the question - How to validate & accommodate Nulls?
I guess code's needed so I'll put that one an a back burner untill I've got
the basics (just been trying to get my head round 'junction' tables &
many-to-many's, not that I forsee a need just yet - just trying to learn)

Jay
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Access Dcount (multiple criteria) 3
Comparing two fields in a table 1
validation code for dates 6
How to use validation values stores in a second table? 1
Access Validation rule of 1 field based on a field of another table 1
Validation Rule 2
...rules prohibited ... 6
Validation rule problem 7

Top