MS Access update query -Validation rule error

  • Thread starter Thread starter just a guy
  • Start date Start date
J

just a guy

I've written an update query that returns an error when it
runs. The error states that "...MS Access did not update
27 records due to a validation rule error"

I've looked at both tables. The data types, and lengths
are the same. THere are no validation rules on either any
field, or at the table level. I've even removed the
relationships in the relationship window in case
validation rules were being inherited from other tables.

Any one have a clue what's happening here?
 
Perhaps some of the properties.

For example, if you have AllowZeroLength set to Null (as you should), you
will need to convert any zero-length strings in the source data into Nulls.
Likewise, if you have Required set to Yes but there is no data in the field,
the rule will block the record.
 
Thanks! You put me on to the solution.

Trouble was related to the zero-length property for the
text fields. They were all set to "No". When I changed
them all to "yes", the query ran, and updated without
error.

Now, I need to see if the AllowZeroLength property can be
set to default to "Yes" for future table definition or
import.
 
You cannot set a default for the Allow Zero Length property.
It defaults to No in all versions up to A97, and Yes in all versions since
then.

Setting AZL to Yes will probably give you problems later. There is no
visible difference between a zero-length-string (ZLS) and a Null, so the
user can't tell the difference anyway. Actually, Access itself can't tell
the difference properly, e.g. DLookup() gets it wrong and returns Null for a
ZLS.

Worse, in *every* query, filter, etc that you write, you have handle both
ZLS and Null for every field. IMHO you are creating a maintenance nightmare
for yourself: that would be much more work than figuring out how to convert
ZLS to Null in the import. Here is how we do it:

Function Str2Null(strIn) As Variant
'Purpose: Convert the input to Null if it's a zero-length string.
If strIn = "" Then
Str2Null = Null
Else
Str2Null = strIn
End If
End Function
 
Back
Top