table validation

[Duane]

Fields used
HearingDate| Date/Time
StartDate| Date/Time

Properties: HearingDate
Validation Rule - [HearingDate]<[StartDate]
Validation Text - Hearing date must be before start date
Required - Yes

Properties: StartDate
Required - Yes

I am using Access 2002, and I receive the following error when I try to save
the table.
Invalid SQL syntax - cannot use multiple columns in a column-level CHECK
constraint.

Any help would be greatly appreciated.

Thanks

 

[Jeff Conrad [MSFT]]

Hi Duane,

Field level validations can only apply to themselves. What you need to do is
remove the curent validation rule you have on HearingDate and move it to the
*table* level validation rule.

--
Jeff Conrad - Access Junkie - MVP Alumni
SDET - XAS Services - Microsoft Corporation

Co-author - Microsoft Office Access 2007 Inside Out
Presenter - Microsoft Access 2007 Essentials
http://www.accessmvp.com/JConrad/accessjunkie.html
Access 2007 Info: http://www.AccessJunkie.com

 

[Duane]

Maybe I am confused. I am trying to apply the the validation rule while the
table is in design mode, to the HearingDate field at the table level. I do
not have any forms as of yet.

Hi Duane,

Field level validations can only apply to themselves. What you need to do
is remove the curent validation rule you have on HearingDate and move it
to the *table* level validation rule.

--
Jeff Conrad - Access Junkie - MVP Alumni
SDET - XAS Services - Microsoft Corporation

Co-author - Microsoft Office Access 2007 Inside Out
Presenter - Microsoft Access 2007 Essentials
http://www.accessmvp.com/JConrad/accessjunkie.html
Access 2007 Info: http://www.AccessJunkie.com

----------
This posting is provided "AS IS" with no warranties, and confers no
rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.mspx
----------

in message:

Fields used
HearingDate| Date/Time
StartDate| Date/Time

Properties: HearingDate
Validation Rule - [HearingDate]<[StartDate]
Validation Text - Hearing date must be before start date
Required - Yes

Properties: StartDate
Required - Yes

I am using Access 2002, and I receive the following error when I try to
save the table.
Invalid SQL syntax - cannot use multiple columns in a column-level CHECK
constraint.

Any help would be greatly appreciated.

 

[Jeff Conrad [MSFT]]

Exactly, you're close, but not quite there.

1. Open the table in Design View
2. Click the Properties button on the toolbar
3. The property sheet for the *table* should open
4. On here you'll see a Validation Rule you can apply to the table
5. Enter your [HearingDate]<[StartDate] on the Validation Rule property line and the text on the
Validation Text property line
6. Remove your existing Validation Rule on the HearingDate field
7. Save and close the table
8. Try it out

Table validations only occur when the record is saved.

--
Jeff Conrad - Access Junkie - MVP Alumni
SDET - XAS Services - Microsoft Corporation

Co-author - Microsoft Office Access 2007 Inside Out
Presenter - Microsoft Access 2007 Essentials
http://www.accessmvp.com/JConrad/accessjunkie.html
Access 2007 Info: http://www.AccessJunkie.com