Results 1 to 12 of 12
  1. #1
    4 Star Lounger
    Join Date
    May 2002
    Location
    Roanoke, Virginia, USA
    Posts
    493
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Validation Rule- Is Not Null (A2k SR2)

    I have a tblRental which has which has several Currency fields. The default value is zero. In
    order to prevent the operator from deleting the contents of the field and leaving the field blank, I
    set the validation rule as, Is Not Null with an appropriate error message. This seems to work fine.

    I have another field, RoomNumber which is a number field on which I set the validation rule to
    (>=102 and <= 117) or (>= 200 and <= 216) or 999. This works fine.

    I also need to make sure that the operator does not blank the field. When I set the Required field
    to

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

    Re: Validation Rule- Is Not Null (A2k SR2)

    Have you tried changing the table design so that the Allow Nulls property for that field is set to No?
    Charlotte

  3. #3
    4 Star Lounger
    Join Date
    May 2002
    Location
    Roanoke, Virginia, USA
    Posts
    493
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Validation Rule- Is Not Null (A2k SR2)

    I can not find the Allow Nulls property. The RoomNumber field has 10 properties under the General Tab but no Allow Nulls

  4. #4
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Validation Rule- Is Not Null (A2k SR2)

    Did you try changing field's validation rule to:
    <pre>((>=102 And <=117) Or (>=200 And <=216) Or 999) And Is Not Null</pre>

    This seemed to work OK.
    HTH

  5. #5
    4 Star Lounger
    Join Date
    May 2002
    Location
    Roanoke, Virginia, USA
    Posts
    493
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Validation Rule- Is Not Null (A2k SR2)

    Thank for your suggestion.

    When I added the ( ) and Is Not Null, the number validations work fine. When I add an invalid
    number the validation message print correctly. Then if I delete the contents of the field, I got the error message

  6. #6
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Validation Rule- Is Not Null (A2k SR2)

    I tested this again and in my test table the validation rule works correctly. If you enter a 0 (zero) or try to leave field blank (ie, Null) the validation error message pops up, with the specified Validation Text. If 0 is not a valid value for this field then the Default Value property should be left blank. In no case was I able to duplicate the "You tried to assign the Null value to a variable...." error msg so am not sure what is causing error.

    Note that if there were existing records in table prior to setting the Validation Rule that do not meet the rule's criteria, they will not be changed by applying the rule. (You'll get the standard "existing data may not be valid for the new rules" msg.) If you then browse records in a datasheet or form, the validation rule will NOT kick in in the case of existing records unless you edit that field with validation rule. If you edit a different field in same record, the rule will NOT kick in. The rule WILL kick in for any new records you add to the table; you will not be able to save new record unless all field level and table level validation rules are met. In other words you can't apply a Validation Rule retroactively.

    Assuming you are using form for entering data, you can use form-level validation to attain more control over the validation process. Typically you'd use the form's Before Update event procedure for this purpose; if the criteria you set aren't met, you can cancel the update and advise user of action required, etc.

    HTH

  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 Rule- Is Not Null (A2k SR2)

    Sorry, I'm thinking of SQL Server. Try the Allow Zero Length property.
    Charlotte

  8. #8
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Validation Rule- Is Not Null (A2k SR2)

    In further reply, here is an example of a form-level validation procedure:
    <pre>Private Sub Form_BeforeUpdate(Cancel As Integer)

    Dim strMsg As String
    With Me
    If IsNull(.txtRoomNo) Then
    strMsg = "You must enter a valid Room Number. " & _
    "Room Number cannot be left blank."
    MsgBox strMsg, vbExclamation, "ROOM NUMBER NOT ENTERED"
    Cancel = True
    .txtRoomNo.SetFocus
    Else
    Select Case .txtRoomNo
    Case 0 To 101, 118 To 199, 217 To 998, Is > 999
    strMsg = "The Room Number you entered is not valid. " & _
    "Please enter valid Room Number."
    MsgBox strMsg, vbExclamation, "INVALID ROOM NUMBER"
    Cancel = True
    .txtRoomNo.SetFocus
    Case Else
    'Do nothing, save record
    End Select
    End If
    End With

    End Sub</pre>

    In this example txtRoomNo is textbox on form bound to RoomNo field. The RoomNo validation rule was deleted from field definition. This type of sub gives you more flexibility and control over whole process. Note: if you are using a "Save" button on form you will need error handling to avoid error message when update cancelled by above procedure:
    <pre>Private Sub save_btn_Click()
    On Error GoTo Err_Handler
    DoCmd.RunCommand acCmdSaveRecord
    Exit_Sub:
    Exit Sub
    Err_Handler:
    If Err = 2501 Then Err.Clear
    Resume Exit_Sub
    End Sub</pre>

    In case like this using bound combo box may be good idea. Create table that lists valid room numbers and use as Row Source for combo, set Control Source to RoomNo field. Set Limit to List property to Yes. You can use On Not in List event procedure to customize msg user gets if he/she enters item not in list. You would still need validation to prevent user from leaving combo blank (null) since the Limit to List property will not prevent this. You could use code like that above for this, except you would not need Select Case to test for RoomNo value as Limit to List will take of that. Using table to store room numbers will simplify things if list of valid room numbers needs to be updated.

    HTH

  9. #9
    4 Star Lounger
    Join Date
    May 2002
    Location
    Roanoke, Virginia, USA
    Posts
    493
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Validation Rule- Is Not Null (A2k SR2)

    Isn't the Allow Zero Length property only available for text fields?

    Tom

  10. #10
    4 Star Lounger
    Join Date
    May 2002
    Location
    Roanoke, Virginia, USA
    Posts
    493
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Validation Rule- Is Not Null (A2k SR2)

    Thanks for your continued assistance

    I tried entering data directly into the tblRental and the Validatio rule works OK. There must be something strange going on in the input form. I will take a close look to see what is causing the "You tried to assign the Null... error message.

    Tom

  11. #11
    4 Star Lounger
    Join Date
    May 2002
    Location
    Roanoke, Virginia, USA
    Posts
    493
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Validation Rule- Is Not Null (A2k SR2)

    I was trying to take the easy way out if the validation rule worked OK. In that way I wouldn't have to put the form-level validation on 3 forms. The easy way usually turns out to take more time than doing it right the first time.

    I haven't had an opportunity to look at the form or try the validation rule or form-level validation on one of the other forms that used tblRental. This is next on my "To Do" list. I tried the form-level validation on frmCheckIn and the room number validation works fine but the blank field still gives the "You tried to Assign the Null" error message.

    Once again thanks for your many helpful suggestions. Hopefully, I can track down what is happening when I apply the validation rule or form-level validation to the other forms that use tblRental.

    The ball is in my court.

    Tom

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

    Re: Validation Rule- Is Not Null (A2k SR2)

    Yes. Did I mix up threads as well as Access and SQL Server? <img src=/S/yikes.gif border=0 alt=yikes width=15 height=15>
    Charlotte

Posting Permissions

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