Validation Rule

[SG]

I have setup a validation rule of Not "" but it still allows the form to be
saved even though the field has been left empty??

Any Suggestions?

Many Thanks

S

 

[John Vinson]

I have setup a validation rule of Not "" but it still allows the form to be
saved even though the field has been left empty??

Any Suggestions?

Many Thanks

S

Set the field's Required property to true instead.

"" is a String value, a zero length string.

NULL is a *different* value, or better non-value. A field that is NULL
is undefined, empty, unspecified; "" is defined, non-empty, specified
- you know exactly what it is, it's a zero length string.

Setting a field as Required in a table will prohibit saving a record
where that field is NULL. Note that if you also set the Allow Zero
Length String property to Yes (it's No by default), you can have "" as
a valid, non-NULL value; this combination should be used with great
caution and only when needed, since it's very hard to tell a NULL from
a zero-length string!

John W. Vinson[MVP]

 

[Jeff L]

If a field is empty, it has a value of Null which is not the same thing
as "".

 

[Marshall Barton]

I have setup a validation rule of Not "" but it still allows the form to be
saved even though the field has been left empty??

Check the table field's Required property. This prevents
Null values from being saved. I think you want this set to
Yes. This translates to the validation rule Is Not Null.

Check the table field's AllowZeroLength property. This
prevents empty string values from being saved. It sounds
like you want this property set to No. I think this
corresponds to a validation rule similar to <> ""

 

[SG]

Marshall,

I have checked the fields required porperty to yes and allow zero length
property to no but the problem I seem to have now is its not very user
friendly with regards to the message which is presented to the user when
they forget to complete the field eg The field 'tblcustomers.title' cannot
contain a null value because the required property for this field is set to
true, Enter a value in this field. The system then asks be to close the form
anyway and I select no and get an action failed dialogue message.

Does anyone have any suggestions?

 

[Marshall Barton]

You can trap those errors using the form's Error event:

Private Sub Form_Error(DataErr As Integer, Response As
Integer)
Select Case DataErr
Case 3314
MsgBox "You must specify ..."
Response = acDataErrContinue
Case 3315
MsgBox "You can not use an empty ..."
Response = acDataErrContinue
End Select
End Sub

OTOH, if that's not the approach you want to take, you can
allways use the validation rules I posted earlier. I
wouldn't bother with the zero length string rule because a
user would have to type "" to enter a ZLS.