Validation Rules Error

[laura reid]

Can someone tell me why I'm getting this error in my append query:

"MS access set 'x' fields ot null due to a type conversion failure, and it
didn't add 0 records to the table due to key violations, 0 records due to
lock violations and 0 records due to validation rule violations. Do you want
to run the query anyway"?

When I select ignore errors and run, all my records show up correctly.

Here's what I think. The table I'm appending to is the 'many' side of my
relational database, and the fields that I'm appending to are the FK fields.
However, the data I'm appending is proper FK number so that the relationships
are established. That's why I'm able to ignore the errors and it works
anyway. Am I close?

Do you think this is necessary to fix and if so, where do I start?

Thanks
Laura

 

[Allen Browne]

Everything else is zero, so presumably the "x" stands for a number. If so,
the message indicates a "typeconversion failure" on some fields. That means
all records are appended, but some of the fields are blank where you would
have expected some data.

To find out what data, you would need to identify what was in those fields.
It is possible, for example, that the source data had some zero-length
strings (ZLS) in a column that was to be append to a Number type field. The
ZLS would fail to convert, and be replaced by Null, which is not visibly
different from a ZLS anyway.

For general suggestions on trouble-shooting imports and what these messages
mean, see:
Why can't I append some records? Trouble-shooting imports
at:
http://allenbrowne.com/casu-19.html

 

[Bill Mosca, MS Access MVP]

Laura

Your records are being inserted, but one of the fields has the wrong data
type for the field in the table so it's going in as null. You need to
address this.

 

[laura reid]

ok, i think i know what the error here is then. Thanks.

 

[laura reid]

Found it!! I have two fields showing up as binary when one should be number
and one should be text.

Here's the catch. In my make table query, the record source (which is a
grouping query) is missing two fields (in order to group properly) that I
need for the follow on queries. So to work around, in this make table query,
I add these two fields by entering the following parameter in the field
property:

tbl_AcctID: [hit enter]
rmks: [hit enter]

Here I was, all proud of myself for getting one over on access. RATS!! I
really need these two fields for my follow on queries to work...let me go
muddle.

Thanks

 

[laura reid]

Found it!! I have two fields showing up as binary when one should be number
and one should be text.

Here's the catch. In my make table query, the record source (which is a
grouping query) is missing two fields (in order to group properly) that I
need for the follow on queries. So to work around, in this make table query,
I add these two fields by entering the following parameter in the field
property:

tbl_AcctID: [hit enter]
rmks: [hit enter]

Here I was, all proud of myself for getting one over on access. RATS!! I
really need these two fields for my follow on queries to work...let me go
muddle.

Thanks

Can someone tell me why I'm getting this error in my append query:

"MS access set 'x' fields ot null due to a type conversion failure, and it
didn't add 0 records to the table due to key violations, 0 records due to
lock violations and 0 records due to validation rule violations. Do you want
to run the query anyway"?

When I select ignore errors and run, all my records show up correctly.

Here's what I think. The table I'm appending to is the 'many' side of my
relational database, and the fields that I'm appending to are the FK fields.
However, the data I'm appending is proper FK number so that the relationships
are established. That's why I'm able to ignore the errors and it works
anyway. Am I close?

Do you think this is necessary to fix and if so, where do I start?

Thanks
Laura