Microsoft Access - CHECK errors

Question

these are the requirements that we have to use SQL to code and we have to build in a check on the table.

I can't find a helpful answer --

CREATE TABLE data
(
data_Name VARCHAR(10) UNIQUE,
data_totals INT,

CONSTRAINT [data_totals_test] CHECK (data_totals between 1 and 5 )
);

error message:

syntax error in constraint clause 

if i do it like this

CREATE TABLE data
(
data_Name VARCHAR(10) UNIQUE,
data_totals INT,

CHECK (data_totals between 1 and 5 )
);

or this

CREATE TABLE data
(
data_Name VARCHAR(10) UNIQUE,
data_totals INT,

CHECK (data_totals > 1 and data_totals < 5 )
);

i get error message in both cases --

syntax error in field definition 

if i take out the , after INT then I get the error message:

syntax error in Create Table Statement. 

also tried this:

create a table this way (successfully)

CREATE TABLE data
(
data_Name VARCHAR(10) UNIQUE,
data_totals INT
);

tried using alter:

alter table data ADD CHECK (data_totals > 1 );

got the same error:

syntax error in field definition 

please advise.

Answer 1

try this https://support.office.com/en-us/article/customize-design-settings-for-objects-in-your-database-b6e0baa0-15d4-47ae-84c2-e0c46dcea23b

to set to ANSI 92 SQL

CREATE TABLE data
(
data_Name VARCHAR(10) UNIQUE,
data_totals INT CHECK (data_totals > 1 and data_totals < 5)
);

alternatively

CREATE TABLE data
(
data_Name VARCHAR(10) UNIQUE,
data_totals INT,
CONSTRAINT CHK_data_totals CHECK (data_totals>1 AND data_totals<5)
);