Database Validation - MS Access

By stonemethod ·
Hi there, im building a database in access at the moment, im having difficulty with validation rules (they are mandatory for this DB) ive made an "appointment" table, within it there is a field called "Appointment_Date/Time", what i need is a rule that will allow a date from in 9am and 12pm, and 1pm to 4pm, anything outside those times have to return a message. Can anyone please give me some tips? ive had quiet a bit of trouble trying to find help with this, thanks in advance

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Answers

Collapse -


by jc2it In reply to Database Validation - MS ...

The biggest challenge is the data type. I have not worked with Date and Time data types much in Access, other than storing time stamps.

My gut feeling is that you need to use VBA, to program a Case statement. If they do not have Case statements in VBA then you will create a long nested If..Then. The real trick is finding out how to use the data type in this way.

I am surprised you could not find a validation rule for a date field on the net.

If I were doing this I would start with a Field of data type Date/Time then I would build an input format mask. from your example it would be "h:AM/PM". This restricts the data entered to 1 through 12 and AM or PM. Then you are going to need to use VBA to restrict the data even more. There may be a way to do this in the input mask, but I could not find full specs on this.

This link might be helpful
Read the bottom few sections.

From it you might try this:
between 9AM 12PM and between 1PM 4PM

I am not sure it is exactly right though.

Good Luck!

Collapse -

case statement

by john.a.wills In reply to Time

VBA does have a case statement, of this form:
Select Case <what you want to evaluate>
Case 09:00 to 12:00
Case 13:00 to 16:00
Case Else
msgbox "time out of range"
End Select

Related Discussions

Related Forums