Results 1 to 9 of 9
  1. #1
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Jerusalem, Israel
    Posts
    708
    Thanks
    0
    Thanked 1 Time in 1 Post

    Validation of times (xp/2k)

    Non-functioning link corrected by HansV

    This should be simple, but I saw a disagreement about this on the Web whether validation on a table can refer to a different field in the table (see http://help.lockergnome.com/office2/Valida...pict714341.html ), and I cannot get it to work.
    Can and how do you set a table validation rule that the EndTime field is after the StartTime field?
    Thanks

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Validation of times (xp/2k)

    Table-level validation rules must be set in the table properties.
    - Open the table in design view.
    - Select View | Properties.
    - Enter something like this in the Validation Rule property:

    [EndTime]>[StartTime]

    - Enter an insulting message in the Validation Text field.

    Also see <post:=542,106>post 542,106</post:>.

  3. #3
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Jerusalem, Israel
    Posts
    708
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Validation of times (xp/2k)

    Now if the degree of insult could be commensurate with the stupidity of the mistake. That would be a great function to write.
    Thanks

  4. #4
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Re: Validation of times (xp/2k)

    I'm personally in favour a large pointing hand (a la Monty Python), getting bigger with each mistake.
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  5. #5
    3 Star Lounger
    Join Date
    Jun 2002
    Location
    Nottingham, Nottinghamshire, United Kingdom
    Posts
    257
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Validation of times (xp/2k)

    Isn't this sort of validation best performed from the form being used to enter data? Adding the test either when the user leaves the second date or (my preference) when the record is being saved allows a message to be given and the focus put back to the offending entry. I much prefer having control over things and not relying on Access to validate.

    David

  6. #6
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Jerusalem, Israel
    Posts
    708
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Validation of times (xp/2k)

    If it is on the table then the validation is automatically on any form that uses that field, and saves that hassle. Different approaches.
    Thanks

  7. #7
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Validation of times (xp/2k)

    The drawback to table validation is that it is absolute and it doesn't allow you to present graceful messages to warn the user they can't do that. It may be a worksaver, but generally data should be entered into the table from a single point, so there should never be a lot of forms that need to trap the errors.
    Charlotte

  8. #8
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Jerusalem, Israel
    Posts
    708
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Validation of times (xp/2k)

    That is the difference between Charlotte and Hans. Charlotte wants a graceful message to the user, while Hans wants to insult him. <img src=/S/laugh.gif border=0 alt=laugh width=15 height=15>
    Thanks to both of you for all your help guidance and insight. Without the people here, my projects would all take twice as long.

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Validation of times (xp/2k)

    <img src=/S/devil.gif border=0 alt=devil width=15 height=15>

    In fact, I generally use the Before Update event of the data entry form to check the data.

    If you use table level validation with an insulting message, you can still have the form display a more graceful message, by writing code in the On Error event of the form:

    Private Sub Form_Error(DataErr As Integer, Response As Integer)
    Select Case DataErr
    Case 3316 ' Validation
    ' Let me handle this
    MsgBox "Goodness gracious me, you appear to have entered an incorrect value." & _
    vbCrLf & "Please enter a time later than " & Me.[Start Time] & ".", vbInformation
    Me.[End Time].SetFocus
    Response = acDataErrContinue
    Case Else
    ' Let Access handle the error
    Response = acDataErrDisplay
    End Select
    End Sub

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •