Validation rule between 2 fields

  • Thread starter Thread starter Amir
  • Start date Start date
A

Amir

Hi,

In a certain table I have "Start time" & "End time" fields.

How can I create validation rule, to check that "End time" is always later
than "start time"?

Thanks,
Amir.
 
You need to determine when you want to validate this information. You could
put it in the beforeUpdate event and have VBA code such as the following:

if(Me!Starttime>Me!Endtime)then
Me!Starttime=""
Me!Endtime=""
Msgbox "You entered a start time that is later than the end time. Please
correct this problem",vbCritical

Else
Do what needs to be done if the times are valid

endif

I hope that helps!

Kevin
 
Rick,
You cannot base a FIELD LEVEL validation on another field in the table. It
would require a TABLE LEVEL validation rule:

[EndTime] > [StartTime]

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm


Rick B said:
in the end time put...
[starttime]
Click to expand...



Amir said:
Hi,

In a certain table I have "Start time" & "End time" fields.

How can I create validation rule, to check that "End time" is always later
than "start time"?

Thanks,
Amir.
Click to expand...
Click to expand...
 
The unstated assumption in Kevin's reply is that you are working in a form.
Tables store data, forms display it for add/edit. Access/JET tables have no
"triggers" (where SQL-Server tables do), but Access forms have "events".
Kevin mentioned using the BeforeUpdate event (of your form displaying this
data) to add validation code.
 
Assuming at the moment I am not using any forms, only tables.

None of the suggested validation rules are working.
I am getting (while saving the table) error message saying "Invalid SQL
syntax - cannot use multiple columns in a column level check constraint"

Any suggestions?

Thanks,
Amir.
 
Did you read my response to RickB? You have to use TABLE LEVEL validation if
you are going to do this without a form. Open the table's property sheet and
set the validation rule there:

[EndTime] > [StartTime]
 
Yes I did read your response.

I copied and pasted the exact phrase to the validation rule of the [EndTime]
field.

I always get the message I mentioned

Something is incorrect here.

Thanks,
Amir.


Lynn Trapp said:
Did you read my response to RickB? You have to use TABLE LEVEL validation
if
you are going to do this without a form. Open the table's property sheet
and
set the validation rule there:

[EndTime] > [StartTime]

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm


Amir said:
Assuming at the moment I am not using any forms, only tables.

None of the suggested validation rules are working.
I am getting (while saving the table) error message saying "Invalid SQL
syntax - cannot use multiple columns in a column level check constraint"

Any suggestions?

Thanks,
Amir.


message
Click to expand...
Click to expand...
 
Amir said:
Yes I did read your response.

I copied and pasted the exact phrase to the validation rule of the [EndTime]
field.
Click to expand...

If that's where you put it then you created a FIELD validation rule instead of a
TABLE validation rule. Read Lynn's reply again
Lynn Trapp said:
Did you read my response to RickB? You have to use TABLE LEVEL validation
if
you are going to do this without a form. Open the table's property sheet
and
set the validation rule there:

[EndTime] > [StartTime]
Click to expand...
Click to expand...
 
As Rick Brandt said, you created a FIELD LEVEL validation rule, not a TABLE
LEVEL one. With your table open in design view select View > Properties. Put
[EndTime] > [StartTime] in that Validation rule.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm


Amir said:
Yes I did read your response.

I copied and pasted the exact phrase to the validation rule of the [EndTime]
field.

I always get the message I mentioned

Something is incorrect here.

Thanks,
Amir.


Lynn Trapp said:
Did you read my response to RickB? You have to use TABLE LEVEL validation
if
you are going to do this without a form. Open the table's property sheet
and
set the validation rule there:

[EndTime] > [StartTime]

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm


Amir said:
Assuming at the moment I am not using any forms, only tables.

None of the suggested validation rules are working.
I am getting (while saving the table) error message saying "Invalid SQL
syntax - cannot use multiple columns in a column level check constraint"

Any suggestions?

Thanks,
Amir.


message
The unstated assumption in Kevin's reply is that you are working in a
form.
Tables store data, forms display it for add/edit. Access/JET tables have
no
"triggers" (where SQL-Server tables do), but Access forms have
"events".
Kevin mentioned using the BeforeUpdate event (of your form displaying this
data) to add validation code.

--
Good luck

Jeff Boyce
<Access MVP>

Hi,

In a certain table I have "Start time" & "End time" fields.

How can I create validation rule, to check that "End time" is always
later
than "start time"?

Thanks,
Amir.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top