By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,254 Members | 1,713 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,254 IT Pros & Developers. It's quick & easy.

Constraint

P: n/a
DL
Hi,
I have a time registration database where employees can fill in the time
they have worked on a project.
They can write down their time round on half hours.
So they can fill in :
01:00 or 01:30 or 06:30
but not!
01:15 or 06:45 or 03:05

p.s. The minimum time also has to be at least 30 min. ( -> 00:30 )

Can anybody help me with this constraint (macro or VB)?
Thank you very much!!
Nov 13 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
"DL" <da*******@hotmail.com> wrote in message
news:40***********************@news.wanadoo.nl...
Hi,
I have a time registration database where employees can fill in the time
they have worked on a project.
They can write down their time round on half hours.
So they can fill in :
01:00 or 01:30 or 06:30
but not!
01:15 or 06:45 or 03:05

p.s. The minimum time also has to be at least 30 min. ( -> 00:30 )

Can anybody help me with this constraint (macro or VB)?


Here is how to do this using check contsraints, (you can't run this from the
query design grid - it must be done in VBA using ADO).

CurrentProject.Connection.Execute "create table timecard (" _
& "empID int not null," _
& "dateStarted datetime not null," _
& "hoursWorked decimal(5,2) not null," _
& "constraint pk_timecard primary key(empID, dateStarted)," _
& "constraint ck_hoursWorked_1 check (hoursWorked > 0.5)," _
& "constraint ck_hoursWorked_2 check
((hoursWorked*2)-Int(hoursWorked*2)=0))"
Note that I've changed the hours worked column to a decimal(5,2) - IMO it's
better to store numbers of hours as decimals rather than as a date/time data
type.

Finally, remember that these are engine-level constraints and they don't
give you nice messages when the constraint fails Your form code should
still check for the condition in the frst place and return a pretty error
message to the user.
Nov 13 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.