Software

Ask something
Question Locked

Database Validation - MS Access

0 helpful
3 replies

by stonemethod

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.
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

3 replies

by jc2it

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
http://www.learnaccessnow.com/chap03a.html
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!

by john.a.wills

VBA does have a case statement, of this form:
Select Case
Case 09:00 to 12:00
'okay
Case 13:00 to 16:00
'okay
Case Else
msgbox "time out of range"
End Select

This discussion is closed to new replies.