Results 1 to 4 of 4
  1. #1
    stanbridge is offline Novice
    Windows XP Access 2007
    Join Date
    May 2011
    Posts
    12

    DateDiff Validation in Table

    In a table called GROUP_VISIT_T, I have two times stored as Date/Time (ShortTime) datatypes.

    Their respective fields are "Visit_Start_Time" and "Visit_Finish_Time".

    I want to add a validation rule so that a user cannot enter times that have a difference of more than 8 hours.

    How do I do this?

    I have tried entering Validation Rules like:
    DateDiff ("n", [Visit_Start_Time], [Visit_Start_Time])/60 < 8
    ....but without any luck so far!




    Cheers,

    Stanbridge

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,847
    Do it on a Form. You could use Control's After Update event. You shouldn't let user deal directly with a Table.

  3. #3
    ssanfu is offline Master of Nothing
    Windows 2K Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Quote Originally Posted by stanbridge View Post
    <snip>

    I have tried entering Validation Rules like:
    DateDiff ("n", [Visit_Start_Time], [Visit_Start_Time])/60 < 8
    ....but without any luck so far!

    <snip>
    Well, I would have used "[Visit_Finish_Time]" somewhere in the DateDiff() function.

    Also, I would use the Before Update event in a form rather than validation in a table.... you have more control over validation. You can change color of the control, default the value to a desired number, set the focus to the control. Can't do that doing validation at the table level.

  4. #4
    stanbridge is offline Novice
    Windows XP Access 2007
    Join Date
    May 2011
    Posts
    12
    Thanks Orange and Steve!

    Point taken, I will do it at the form level. I think I was getting ahead of myself!

    And yes Steve, I was getting lazy with my copy and paste and forgot to change the second "Visit_Start_Time" to "Visit_Finish_Time"!


    Cheers,
    Stanbridge

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 0
    Last Post: 03-18-2011, 06:38 AM
  2. Replies: 2
    Last Post: 10-23-2010, 09:38 AM
  3. Replies: 7
    Last Post: 11-22-2009, 02:38 PM
  4. Linked Table Field Validation
    By yuriyl in forum Access
    Replies: 4
    Last Post: 04-20-2009, 01:31 PM
  5. Referencing table data in field validation rule
    By toad848 in forum Database Design
    Replies: 3
    Last Post: 03-19-2009, 07:03 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums