Validation Rule Violation

[Guest]

Using Access 2000 and I have imported records from Excel into a Temp Table.
Then I am trying to run an append query to add new records and an update
query to update records that already exist in a table.
When I run either query I get an error message stating that there are
Validation Rule Violations, the problem is that there are no Validation Rules
in the table I'm trying to append or update.
I have successfully completed exactly the same exercise last week using the
same queries so I'm at a loss as to what is wrong today?
Anyone have any suggestions?

 

[Guest]

When I have run into the validation rules problem it usually had to do with a
field containing a zero-length string where it is not allowed. What may
appear as a null could very well be a ZLS.

Try importing the data into a new table (letting Access create the table
upon import) and check each text field WHERE MyField = ""

 

[Guest]

Thanks Bill for your quick response, I have checked and yes there are
zero-length strings and my tables field properties are set to "No" for these.

Should I simply set the properties to "yes"? What are the consequences as
I'm a little bit confused as to the difference between zero-length strings
and Null values e.g. if I have a query that searches where a field is null
will this include records where zero-string length is allowed.... or am I
getting even more confused?

Best regards

 

[Guest]

Allan

ZLS's look like nulls so it's easy to not see them. I always disallow them
for that reason. If you know they are there then you can deal with them, but
I'd rather not.

If you have a query looking for nulls, it won't find the ZLS. Nor will a
query looking for "" find nulls. They are not the same.

Of course, you can always allow them and then run a clean-up update query to
set them ot null as in:
UPDATE MyTable
SET MyField = NULL
WHERE MyField = ""

 

[Guest]

Hi Bill

Brilliant and simple solution to change all "" values to Null, can you help
a little bit further with your code for updating multiple fields in mytable
which contain ZLS so I can run one query for the whole table instead of
several covering each seperate field.

UPDATE mytable
SET mytable.field1 = Null, mytable.field2 = Null, mytable.field = Null


Thanks very much for you assistance

Allan