Validation rules with IIF statements

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • ckpoll2
    New Member
    • Sep 2006
    • 76

    Validation rules with IIF statements

    Hello,

    I have a text box on a form where people enter numbers. How can I get it to not accept any number greater than 2 if a different text box is "Credit Hours" but to accept any number if it is anything other than "Credit Hours"?

    Thanks,

    Charlie
  • pks00
    Recognized Expert Contributor
    • Oct 2006
    • 280

    #2
    Originally posted by ckpoll2
    Hello,

    I have a text box on a form where people enter numbers. How can I get it to not accept any number greater than 2 if a different text box is "Credit Hours" but to accept any number if it is anything other than "Credit Hours"?

    Thanks,

    Charlie

    one way is to use the BeforeUpdate event of the textbox
    i.e.

    private sub mytxtbox_Before Update(cancel as Integer)
    If Me.othertextbox = "Credit Hours" And mytxtbox.Value > 2 Then
    msgbox "Sorry but value cannot be 2 if Credit Hours selected"
    Cancel = True
    End if
    end sub

    Comment

    • ckpoll2
      New Member
      • Sep 2006
      • 76

      #3
      Where would I put that information that you gave me? Does that go in the expression builder or the code builder? Can I enter it just as is?

      Thanks for your help

      Comment

      • NeoPa
        Recognized Expert Moderator MVP
        • Oct 2006
        • 32633

        #4
        Assuming the number TextBox is called txtNum and the other txtType then put
        Code:
        [txtNum]<3 or [txtType<>"Credit Hours"
        into the 'Validation Rule' property of txtNum.
        The 'Validation Text' property can be set to your own error message.

        Comment

        • ckpoll2
          New Member
          • Sep 2006
          • 76

          #5
          That's amazing. Thanks so much for your help.

          Charlie

          Comment

          • PEB
            Recognized Expert Top Contributor
            • Aug 2006
            • 1418

            #6
            for the code provides by pks00 you can put it in a form, in the respective field you want to check....

            But obviously you are working with the table directly :)

            Comment

            Working...