Validation Rule not allowing appending

[Raptor136]

HI,
I am receiving an error message to the effect that access is not adding a single record due to a validation rule. My help system is non functional. I looked at the single record and cannot find a unique item causeing it to fail. I also checked the table field definitions and non of them are indexed or have validation criteria. Any ideas?
Thanks,
Phil raptor136@adelphia.net

 

[Isadore]

Phil: Could be a mixed field result, i.e., 99% of your data in a particular field are numbers but a string is persistent. I'd check that out. What does the resulting ERRORS table say?

 

[Raptor136]

It says
"Microsoft access set 0 fields to null due to a type conversion error, and it didn't add 0 records to the table due to key violations, and 0 records due to lock violations, and 2 records due to validation rule violations. Do you want to run the action query anyway? To ignore the errors and run the query anyway, click yes. For an explanation click help."

My help is screwed up and the install cd is cracked. I have examined the two records and cannot find any obvious problem with the data. And, I checked the field definitions and none say anything about a validation rule.

Thanks for the reply,
Phil raptor136@adelphia.net

 

[scriverb]

This is a problem with your data type not matching the field type as described in your table. Many times this can be the resulted of a calculated field. If for some reason the expression used to calculate a field does not process correctly as you expect ACCESS will kick out a value that you are not expecting.

You have to look over your data completely. Run the Append query as a Select and look for odd looking outcomes. Start by examining your table for Validation rule field. These are the ones that the error is pointing to. Then look at the manner that you are coming up with that data. This should get you started. Bob Scriver

 

[Raptor136]

Well, I looked over the table definitions again. Nothing there, no validation rules at all. I ran the append as a select and it picked up all the records, but as an append drops 2 of them. I ran each query that processess the data one at a time and checked the resulting table, so it *is* the append query because those two records stay with us up to then.

The only anomaly I noticed was that those two records have a field that is apparently zero length strings. Every thing else is three characters (even if the field is blank, I can still highlight three "spaces" in the cell). In this case there can be no more than one space because as soon as I hit the arrow key I go to the next field.

Thanks for the reply,
Phil raptor136@adelphia.net

 

[Raptor136]

Fixed it. Decided that I was tired of messing with it. Ran an update query on the original table. Changed all the nulls and ""'s in a !. Now there are no blanks but the append query works. Thanks for all the advice.

Phil raptor136@adelphia.net