validation rule question

[Guest]

Hi All,

I want to ensure the following:

In a table I would like the data to be entered in a field called OverTime
only to be greater than zero when the data for the corresponding record in
the previous field called NormalTime is 8. In other words, only when the
value of NormalTime is 8 can the value in OverTime be more than 0.

How should I go about placing these 'restrictions'?

As background info, in Table Design view I have set the default value for
OverTime as 0 and put in a validation rule for NormalTime that ensures that
entries will be 8 or less.

Cheers!

 

[Allen Browne]

Use a validiation rule on the table.

1. Open the table in design view.

2. Open the Properties box (View menu.

3. Set the Validation Rule in the Properties box to:
([NormalTime] = 8) OR ([OverTime] < 0)

Notes:
a) Since you are comparing fields, it is important to use the rule in the
Properties box (record level), not the Rule in the lower pane of table
design (field-level.)

b) In some versions of Access, you may need to explicitly allow nulls, i.e.:
([NormalTime] = 8) OR ([OverTime] < 0) OR ([NormalTime] Is Null) OR
([OverTime] Is Null)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

message
news:[email protected]...

 

[utkal]

Answered

 

[Jamie Collins]

Use a validiation rule on the table.

1. Open the table in design view.

2. Open the Properties box (View menu.

3. Set theValidationRule in the Properties box to:
([NormalTime] = 8) OR ([OverTime] < 0)

I think you meant

([NormalTime] = 8 OR [OverTime] = 0)

When more than one column is involved, I prefer to write out logic
'long-hand'; also, we can assume the other columns have their own
domain checking e.g. to prevent their values being negative or
extreme; something like:

CREATE TABLE Test (
NormalTime INTEGER,
CHECK (NormalTime BETWEEN 0 AND 8),
OverTime INTEGER,
CHECK (OverTime BETWEEN 0 AND 12),
CHECK (
(NormalTime < 8 AND OverTime = 0)
OR (NormalTime = 8 AND OverTime > 0)
));

Jamie.

--

 

[Guest]

Hi Jamie, Allen,

Thanks very much, it works a treat!

--
david bruyntjes

Use a validiation rule on the table.

1. Open the table in design view.

2. Open the Properties box (View menu.

3. Set theValidationRule in the Properties box to:
([NormalTime] = 8) OR ([OverTime] < 0)

I think you meant

([NormalTime] = 8 OR [OverTime] = 0)

When more than one column is involved, I prefer to write out logic
'long-hand'; also, we can assume the other columns have their own
domain checking e.g. to prevent their values being negative or
extreme; something like:

CREATE TABLE Test (
NormalTime INTEGER,
CHECK (NormalTime BETWEEN 0 AND 8),
OverTime INTEGER,
CHECK (OverTime BETWEEN 0 AND 12),
CHECK (
(NormalTime < 8 AND OverTime = 0)
OR (NormalTime = 8 AND OverTime > 0)
));

Jamie.