Overlapping times

[Jon.N]

I have a database that loggs the jobs each employer does each day. One can enter job details, a start time and an end time.

I would like to prevent someone from entering a start time that is before the end time of the previous job. I have been trying to do this as a validation rule in the form properties but without any success.

Any ideas are most welcome.

Please note: The database is relational. Each job is a record in its own right that is linked to a record with a name and a date, i.e. a 1-many relationship. I suppose what I'm asking is for the start time of one record to be equal to or later than the end time of the previous record.

 

[KeaganQuilty]

Use the DCount function in the BeforeUpdate of your textbox.

If DCount("*","tablename","[EndTime] >= " [txtBox]) > 0 then

msgbox "Overlapping times"

End if

The DCount function returns the number of times the criteria is true. Therefore, if the EndTime of a previous job is Greater than the time in the txtBox, you will get a message, but it will still allow the entry. This function also doesn't check dates, so you would have to add that to the criteria.

Keagan Quilty
Kilbride, Newfoundland

 

[KeaganQuilty]

I must be tired. I just put in the code for you to get a syntax error.

DCount("*", "tablename","[EndTime] >= " &[txtBox])

There. Now try it.

Keagan Quilty
Kilbride, Newfoundland