Validation Rules

[Boz]

Hi...need help again....

Access 97 database. I have a form for associate work hours. As associates
work a trailer, they put down their name, start date, start time, end date
and end time. All date fields are defined uning "Short Date". Time fields
are in the 24 hour format. The fields are then calculated to show the amount
of time it took for the associate to work the trailer. Unfortunately,
associates on the floor do not always use 24 hour times when they are filling
out their sheet. If the data entry clerk in the office does not catch this
and convert the time, many times this will lead to negative work times. I
used the following validation rule in the Start Date field to ensure that
they were at least getting the right year:

=#1/1/05# And <#1/1/06#

I would like to be able to use a validation rule in the End Date field that
will show an error message if the date typed in is prior to the date typed in
the Start Date field.

I would also like to be able to use a validation rule for the Start Time and
End Time field to do the same thing.

Any help will be appreciated.

Linda

 

[John Vinson]

I would like to be able to use a validation rule in the End Date field that
will show an error message if the date typed in is prior to the date typed in
the Start Date field.

I would also like to be able to use a validation rule for the Start Time and
End Time field to do the same thing.

You can't use field validation rules in a Table to do this, since
validation rules cannot refer to other table fields.

I'd suggest using the Form's BeforeUpdate event instead:

Private Sub Form_BeforeUpdate(Cancel as Integer)
If Me![Start Date] > Me![End Date] Then
Cancel = True
MsgBox("You can't end days before you start!", vbOKOnly)
Me![End Date].SetFocus
Exit Sub
End If
If Me![Start Time] > Me![End Time] Then
Cancel = True
MsgBox("You can't end before you start!", vbOKOnly)
Me![End Time].SetFocus
End If
ENd Sub

If you're doing data entry directly into a Table rather than a Form...
well, don't. Forms are MUCH more programmable and controllable, as
well as easier for the user.

Note also that Access Date/Time fields can - and usually do - contain
both a date and time in the same field. Do you have some critical
reason not to store StartTime and EndTime as date/time fields? Doing
so will let you more easily calculate worktimes across midnight, and
simplify your structure.

John W. Vinson[MVP]

 

[Steve Schapel]

Boz,

This would involve a Validation Rule for the table, not for a field. In
the design view of your table, select 'Properties' from the View menu.
There, you can enter like this in the Validation Rule property...
([End Date]+[End Time])>([Start Date]+[Start Time])
.... and then in the Validation Text property you can enter the message
that you want to be displayed if the rule is broken.

 

[peregenem]

You can't use field validation rules in a Table to do this, since
validation rules cannot refer to other table fields.

This is surely a misstatement. Not only can a CHECK constraint (a.k.a.
Validation Rule) refer to columns in the same row, since Jet 4.0 it can
refer to columns/rows in other tables.

The only limitation is referring to other rows in the same table; no
great surprise because, although it's in the ANSI SQL-92 Standard, none
of the commercially available SQLs support it.

 

[Brendan Reynolds]

You can't do this using a *field-level* validation rule, but you can do it
using a *table-level* validation rule. To create a table-level validation
rule, open your table in design view, and choose Properties from the View
menu. In the Validation Rule text box in the Properties window, you can
enter an expression such as ...

[End Date] >= [Start Date]

If either of the dates may be Null, you need to allow for that in the
validation rule ...

[End Date] > = [Start Date] Or [End Date] Is Null Or [Start Date] Is Null

In the Validation Text text box, enter the text you want displayed to the
user when data violates the rule.

BTW: You write that "date fields are defined using 'Short Date'" and "time
fields are in the 24 hour format". In an MDB, Date/Time fields are stored as
floating point numbers, which contain a complete date and time, regardless
of how the data is displayed to the user. Here's a link to an on-line
article on the subject that you may find useful ...
http://support.microsoft.com/default.aspx?scid=kb;en-us;130514

 

[mnature]

One way I have handled looking for improper entries, is to use "conditional
formatting" in the form where data is entered. You can directly compare the
entered date to the date in another field, and have the color change (red is
a nice flag) to indicate that the time or date may have a problem. This
works pretty well for simple typos like the one you are describing.