Valiation rule Q:

[pvdalen]

Hi all,

I hope this is a slightly new twist, as I've actually looked for an answer
in past posts, but have found nothing that helps. Or, found nothing I
understand well enough to apply.

So, I have a Date/Time field in a table, the entries of which I'd like to
restrict to quarter-hour increments 00, :15, :30, :45).

I tried various incantations of the Minute() fumction in the Expression
Builder at the Validation Rule entry in the table, but spun my wheels
fiercely. I can't imagine this is complex enough to require a module, is it?

As always, help is appreciated, and belittling sarcasm is tolerated.

Thanks.

 

[Brian Camire]

You might try setting the validation rule for the field to an expression
like:

(Minute([Your Date Field]) Mod 15=0) And ([Your Date
Field]=DateSerial(Year([Your Date Field]),Month([Your Date Field]),Day([Your
Date Field]))+TimeSerial(Hour([Your Date Field]),Minute([Your Date
Field]),0))

 

[John Vinson]

So, I have a Date/Time field in a table, the entries of which I'd like to
restrict to quarter-hour increments 00, :15, :30, :45).

I'd suggest a different approach: let the user type any time they
like, and have Access automagically round it to the nearest quarter
hour. You can do this on a Form using the AfterUpdate event of the
time textbox:

Private Sub DateTimeField_AfterUpdate()
Me!DateTimeField = CDate(cdbl(clng(cdbl(Me!DateTimeField)/96.))*96.)
End Sub

John W. Vinson[MVP]

 

[pvdalen]

Hi John and Brian,

Sorry it took so long to respond, but I just tried your solutions. Brain's
long solution was quite daunting, so I decided to try it in increments, just
to see how it worked, and just the first part of it, "(Minute([Date field])
Mod 15=0) worked like a champ! I suppose this is because I have separate
time and date fields?

Thanks again, guys. I appreciate your help.

Paul

 

[Brian Camire]

I was trying to accomodate the general case where the date field could
include both date and time of day, and the time of day could include hours,
minutes, seconds and fractional seconds.

Even if we assume that your field contains only the time of day, though, a
validation rule of

(Minute([Your Date Field]) Mod 15=0)

will not prevent you from entering a value of:

02:45:01

even though it is not on a quarter hour.

That was the motivation for the

TimeSerial(Hour([Your Date Field]),Minute([Your Date Field]),0))

term.

To further complicate matters, date/time fields are stored as floating point
numbers, and so are not restricted to full-second values. The TimeSerial
term should also catch these.

If you have separate date and time fields, consider defining the validation
rules to enforce this (for example, to ensure that the "time" of the "date"
field is zero/midnight, and the "date" of the "time" field is zero/December
30, 1899). You might also consider storing the date and time in only one
field to make comparisons easier (and to reduce storage).

Hi John and Brian,

Sorry it took so long to respond, but I just tried your solutions. Brain's
long solution was quite daunting, so I decided to try it in increments, just
to see how it worked, and just the first part of it, "(Minute([Date field])
Mod 15=0) worked like a champ! I suppose this is because I have separate
time and date fields?

Thanks again, guys. I appreciate your help.

Paul

I'd suggest a different approach: let the user type any time they
like, and have Access automagically round it to the nearest quarter
hour. You can do this on a Form using the AfterUpdate event of the
time textbox:

Private Sub DateTimeField_AfterUpdate()
Me!DateTimeField = CDate(cdbl(clng(cdbl(Me!DateTimeField)/96.))*96.)
End Sub

John W. Vinson[MVP]