Validation rule between 2 fields

[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.

 

[Rick B]

in the end time put...

[starttime]

 

[Kevin]

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

 

[Lynn Trapp]

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

in the end time put...

[starttime]

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.

 

[Jeff Boyce]

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.

 

[Amir]

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.

 

[Lynn Trapp]

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]

 

[Amir]

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.

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

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

 

[Rick Brandt]

Yes I did read your response.

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

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

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]

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

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.

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

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.