1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Access 2007: Validation Rule for Time

Discussion in 'Business Applications' started by Renamed&Closed, Aug 10, 2009.

Thread Status:
Not open for further replies.
Advertisement
  1. Renamed&Closed

    Renamed&Closed Account Closed Thread Starter

    Joined:
    Jan 6, 2009
    Messages:
    59
    Hiya!

    This is probably super easy, I just can't seem to figure out how to do it.

    I have a Date/Time field in a table, which is in "Short Time" format. I want people to only be able to enter times in increments of 30 minutes.

    Is there some way to put in a validation rule to "only allow times that have a 'minutes' value of '00' or '30'"?

    Many thanks!
     
  2. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    This can be doen using VBA in the Field's After Update Event Procedure.
    This works for a Filed called "Hours"
    Dim min As String
    minutes = Right((Format(Me.Hours, "hh:mm")), 2)
    If minutes <> "30" And minutes <> "00" Then
    MsgBox "minutes must be either 30 or 00"
    Me.Hours = ""
    End If
     
  3. Renamed&Closed

    Renamed&Closed Account Closed Thread Starter

    Joined:
    Jan 6, 2009
    Messages:
    59
    Thanks for the quick reply! I forgot to mention, I need it without using VBA. And please don't ask why haha, I'm tired of explaining why I can't use VBA. :) thanks!
     
  4. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    The time/date is stored as a number, it is only the format that makes it 00 or 30 so put this in the Validation Rule
    Right((Format([Hours],"hh:mm")),2)="00" Or Right((Format([Hours],"hh:mm")),2)="30"
     
  5. Renamed&Closed

    Renamed&Closed Account Closed Thread Starter

    Joined:
    Jan 6, 2009
    Messages:
    59
    Except that it needs to be in Time/Date format.

    Okay I guess I need to be more clear.... haha sorry.

    These are musts:

    -the field is in Time/Date format
    -users can only enter times in increments of 30 minutes
    -not a drop-down list - users need to be able to enter the time by typing it into the table directly
    - No VBA/SQL: needs to be set up right in the table design window

    Many thanks!
     
  6. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    The answer I provided does what you want, I have tested it.
     
  7. Stacers

    Stacers

    Joined:
    May 8, 2009
    Messages:
    51
    I think this will do exactly what you need:

    Minute([NameOfYourField])=0 Or Minute([NameOfYourField])=30
     
  8. Renamed&Closed

    Renamed&Closed Account Closed Thread Starter

    Joined:
    Jan 6, 2009
    Messages:
    59
    Many thanks!
     
  9. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/851150

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice