Append query and validation rule violations

[merg]

The table design originated outside my company and cannot be modified. Many fields have the Allow Zero Length property set to no and yet have the Required property also set to no. However they also don't want nulls in their data so they have chosen to insert " " to indicate a null.

Using test validation rules on the table, I get the names of specific fields which violate the AllowZeroLength rule. But using a query, I see nothing with a length of either null or zero in that field. Is the problem that Access is treating " " as "" when running the validation rule?

I'm stymied. Am I stuck building an update query instead (I have 10+ tables each with more than 100+ fields) to convert the data instead of appending it? It seemed so much easier to delete the original records and append them from a copy...

Is it possible to turn off the violation rule, append the data, fix the data via code (not that I've managed to do this either) and then turn the rule back on?

Miranda

 

[jgpasko]

Hi Miranda,

Yes it is possible to change the tbl properties, append the data, *Update* the data according to criteria, and set the tbl properties back to the original state.

You can use an Update Query to set the fields meeting the criteria to the value you want.

Good Luck!
John Pasko
john@rts-sd.com
"No matter where you go, there you are."

 

[merg]

Yes, of course.... but can I change the validation rules which are set on each field in one swoop? I have about 2500 fields which would have to have the restriction removed then replaced...

Miranda