Results 1 to 10 of 10

Thread: Validation Rule

  1. #1
    New Lounger
    Join Date
    Jan 2013
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Validation Rule

    I have a table with 3 fields, text, meter, lastmeter. I want a validation rule, either in a query or a form that doesn't allow entry in first number field to exceed the second number field plus a number.

    I have tried this in a query and in a form control for the first number field. Neither of them work. I am not the least bit familiar with VBA.

    expression I have tried is: <[lastmeter]+672

    Meaning the number in meter cannot exceed the last meter +672.

    I appreciate your assistance.

  2. #2
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts
    Exactly where did you put this Validation Rule? You could put it in the [meter] field in the table. In a form, you'd put code in the Form's BeforeUpdate event to check for this.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  3. #3
    New Lounger
    Join Date
    Jan 2013
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by MarkLiquorman View Post
    Exactly where did you put this Validation Rule? You could put it in the [meter] field in the table. In a form, you'd put code in the Form's BeforeUpdate event to check for this.
    I tried it in the table and it didn't work. I also tried it in the form beforeupdate and it didn't work. I have tried it the validation rule on the form and it didn't work.

  4. #4
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts
    What code did you try it in the forms BeforeUpdate event?
    It should look something like this:

    If Meter > LastMeter +672 Then Cancel = True

  5. #5
    New Lounger
    Join Date
    Jan 2013
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by patt View Post
    What code did you try it in the forms BeforeUpdate event?
    It should look something like this:

    If Meter > LastMeter +672 Then Cancel = True
    I will try that.

  6. #6
    New Lounger
    Join Date
    Jan 2013
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I apologize I made an error in my original post. I am posting from home since my work computer will not load this website.

    I am not using a field "lastmeter". I am using a calculated field from an expression. I think that is the problem.
    I am using this: =[table]![meter]<[query]![expr1]

  7. #7
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts
    Where are you using that code?
    Get back if you need more help.

  8. #8
    New Lounger
    Join Date
    Jan 2013
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by patt View Post
    Where are you using that code?
    Get back if you need more help.
    I tried this in the validation rule of the form for the "meter" box
    IIf("Meter">"LastMeter"+672,"Then Cancel"=True)

    Then I get this error:
    there is a type mismatch in the form control validation rule property

    Both tables the "meter" and "last meter" are set to "number".

  9. #9
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    If Meter and LastMeter are field (or expression) names, then they should be enclosed in square brackets. Try

    IIf([Meter]>[LastMeter]+672,"Then Cancel"=True)

    However, I don't know if you can use and IIF statement in a validation rule - I tend to think not. I believe they work on the True/False concept, and you might find that you need a simpler version something like:
    Me.[Meter]<=Me.[LastMeter]+672 - that presumes you are doing this at the form level. If you do it at the control level on a form it would be for the "Meter" control and would simply be <=Me.[LastMeter]+672

    Try looking at form validation rules in one of the many good Access books that are out there, or take a look at Restrict data input by using Validation Rules
    Wendell

  10. #10
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts
    I meant for you to put that line of code in the form's BeforeUpdate event

Posting Permissions

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