Access Validation Rule Violations on Append Query

tags:

ms-access

views:

162

answers:

Access Validation Rule Violations on Append Query

I'm recieving the following error on trying to run an append query in access.

Microsoft Office Access set .... and it didnt't add... 779280 records(s) due to validation rule violations.

If I choose to run the query anyways, nothing actually happens.

To give some context, I'm simply trying to copy a populated field, consisting of values similar to "16-2009-02, 34-2010-02, et cetera" to another currently unpopulated field.

The fields themselves have no set validation rules, and both have the standard text field options.

I'm hoping to be able to simply remove those hyphens, and fix the issue. But I guess that's what I'm not sure about, are those hyphens actually a problem?

Running SP3 w/ Access 2003.

Thanks in advance!

Does the destination field have Allow Zero Length set to false or Required set to true? If it does, it is possible that some of the data from the source column is null or is set to an empty string. In addition, you should verify that the field sizes on the new column is equal to larger than the source column.

EDIT On appending from one table to another, you must ensure that you populate the columns that makeup the primary key of the destination table. Thus, from your screenshot, you need to include the loggerid and datetime columns from the "Log ID" table. Now, if there are collisions, Access should tell you how many rows generated collisions and let you append the ones that did not collide with the uniqueness restriction on loggerid and datetime

Thomas 2010-03-22 17:29:59

Allow Zero Length = YesRequired = NoI do have some Null fields, but I would think that rather applying to the entire query, it would only fail for those that do.Currently, the whole query is failing.

Jacques Tardie 2010-03-22 17:42:09

And the field size on the destination query is greater than or equal to the field size of the source?

Thomas 2010-03-22 18:05:12

BTW, does the destination table have an AutoNumber column? I take it that you are excluding this column from your Append query.

Thomas 2010-03-22 18:08:18

No, the destination field doesn't have an AutoNumber Column. As far as the General Properties go, here they are:Field Size = 50Format = NullInput Mask = NullCaption = Null... = NullValidation Text= NullRequired= NoAllow Zero Length = YesIndexed = NoCompression = YesIME Mode = No ControlIME Sentence Mode = NoSmart Tags = NoFurthermore, I went and deleted all fields that I'm trying to copy that had Null values, but am still getting the same error.It's bizarre.

Jacques Tardie 2010-03-22 18:32:49

Does the destination table have a primary key? If so, how are you populating that column when you append your records?

Thomas 2010-03-22 18:56:01

I've attached some screenshots here:http://img695.imageshack.us/img695/5421/mainle.jpghttp://img411.imageshack.us/img411/4562/query.jpgThe primary key is a combination of the Loggerid and Date/Time. We use temperature loggers that monitor stream temp every 30min...

Jacques Tardie 2010-03-22 19:20:52

The column I want to populate is the EffortID btw.

Jacques Tardie 2010-03-22 19:24:15

Ok. Thanks for the screenshot. On your append, you need to append records into the PK of the destination table (and any other Required fields) and those new values must be unique across the whole table. Thus, you need to include the columns loggerid and datetime persuming that the source values you are appending will not create collisions.

Thomas 2010-03-22 20:05:18

So, what that entails is simplying adding the Loggerid/Datetime fields into the query if I understood that correctly?

Jacques Tardie 2010-03-22 20:40:08

Yes. Now, if the loggerid and datetime in the source table already exist in the destination table, then you will get collision alerts again.

Thomas 2010-03-22 20:48:38

That data does exist in the destination table.I'm thinking that I'm using the wrong sort of query here. Should I instead be using an update query? If you look at the images I posted, I simply want to add those effortid's on the left to the table on the right.

Jacques Tardie 2010-03-22 21:28:45

Correct. If you wish to simply update the matching values, then an Update query is what you want.

Thomas 2010-03-22 21:34:54

Make sure the target column is not indexed or, if it is, that duplicates are allowed. Access often makes indexing decisions on your behalf and sometimes they are not correct.

Larry Lustig 2010-03-22 17:35:24

Neither fields are indexed.

Jacques Tardie 2010-03-22 17:42:38

ansaurus

tags:

views:

answers:

Access Validation Rule Violations on Append Query

related questions