1

I need to set up a small database as an assignment after 3 hours of an access introduction and I'm failing completely. However I youtubed several hours but still no change in the outcome. Do I try to implment too many excel functions into access?

My question / problem:

I have three tables for a dvd rental business. Customers, Movies, Rentals.

Start and end date of rented movie, both data type "Date/Time" Start Date should be today by default; validation rule: =Date() End date should be equal or after 

start date; validation rule: =[Enddate]>=[Startdate]

I'm gettint an error messasge:

invalid SQL syntax - cannot use multiple columns in a column-level CHECK constraint.

I'm setting it up in the table properties not in the field, so what am I doing wrong?

Nina
  • 11
  • 1
  • 1
  • 4
  • Can you put your table definition here? – Mad Dog Tannen Nov 26 '13 at 10:26
  • It sounds to me that you are putting 2 columns in a FOREIGN KEY CONTRAINT. – Mad Dog Tannen Nov 26 '13 at 10:27
  • http://www.access-programmers.co.uk/forums/showthread.php?t=100895 – Mad Dog Tannen Nov 26 '13 at 10:27
  • See the section "Validation Rules for Tables" in http://office.microsoft.com/en-ca/access-help/validation-rules-HA010285532.aspx – NoChance Nov 26 '13 at 10:46
  • Hi Kay, I found exactly the same URL earlier ago and hi Emmad, the same with the URL you provided. I took the info and compared it to mine. Section is correct. I'm in the design view - fieold name - general and validation rule. I took the same info from the website [enddate]>=[startdate] but then I get the error message. Before that I could save it. – Nina Nov 26 '13 at 10:56

1 Answers1

3

When you receive the error message: invalid SQL syntax - cannot use multiple columns in a column-level CHECK constraint. you are trying to add multiple columns as validation within a Field validation rule. In the table design view this is the box at the bottom with tabs for General and Lookup.

To apply a constraint that uses multiple columns you must do so at the table level. This can be access by right clicking your table while in design view and selecting properties to open up the Property Sheet. Once this is open you can place your check into the available Validation Rule field but put it in brackets without an equal sign: ([Enddate]>=[Startdate])

Zaider
  • 1,950
  • 1
  • 21
  • 31