Results 1 to 8 of 8
  1. #1
    PeakH is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2017
    Posts
    143

    Append query still has Validation Rule Violations after all rules have been deleted

    I imported a new table from excel and created an append query to an old table.


    When I first tried to run the query I got the message: Database can't append all the records in the append query.... it didn't add 431 records due to validation rule violations.

    I changed any settings/properties that were not the same (short text to number) made sure that nothing was indexed on either table, set all required values to No and Allow zero length to Yes. All properties for both tables are now identical and I still get the same message (with the same number of records, which is all of them so nothing has appended with any attempt).
    The only thing left that I can think of is that one field has a format property 0-000-000 (I meant to delete it but now someone is using the database so I have to wait for them to be done to change the table design), but the format is identical to that of the corresponding field in the other table, and all records agree with the formatting.

    I'm at a loss.

  2. #2
    PeakH is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2017
    Posts
    143
    Here is the SQL of the query:

    Code:
    INSERT INTO [RetailersActive] ( Tradename, City, StreetAddress, License, SmallUBI, [SuiteRm], State, County, ZipCode, PrivDesc, PrivilegeStatus, DateCreated, DayPhone )
    SELECT tblAddRetailers.Tradename, tblAddRetailers.City, tblAddRetailers.StreetAddress, tblAddRetailers.License, tblAddRetailers.SmallUBI, tblAddRetailers.SuiteRm, tblAddRetailers.State, tblAddRetailers.County, tblAddRetailers.ZipCode, tblAddRetailers.PrivDesc, tblAddRetailers.PrivilegeStatus, tblAddRetailers.DateCreated, tblAddRetailers.DayPhone
    FROM tblAddRetailers
    WHERE ((([Tradename] & [City] & [StreetAddress] & [License] & [SmallUBI]) Not In (SELECT [Tradename] & [City] & [StreetAddress] & [License] & [SmallUBI] FROM [RetailersActive])));
    When viewing the records, it selects the correct ones so it seems to work fine there.

  3. #3
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    It may be the date - use append to CDate(tblAddRetailers.DateCreated)

  4. #4
    PeakH is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2017
    Posts
    143
    The DateCreated field isn't a date field, it's a short text field on both tables. All the dates look like this: 20170405 (It's a list from the great interwebs and I don't even use that field for anything).
    I have sense changed all fields to short text fields just to make things a bit easier, and since they can all be used at text anyway.

  5. #5
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Did that solve it?

  6. #6
    PeakH is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2017
    Posts
    143
    No, sorry.
    I just created a test table with all the same fields as the RetailersActive destination table, and changed the destination to the new test table, and the query worked fine, so now I'm more stumped.

  7. #7
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    No lookup's on the destination table? It is a non-text field, remove all of those from the query then add them back one at a time to find the problem child.

  8. #8
    PeakH is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2017
    Posts
    143
    Oh goodness, I feel dumb.
    When I was first changing the field types and rules to match, I must have accidentally changed the destination table's Primary key from an AutoNumber to a regular number, and not thinking that I really even needed to look at that, just skimmed right over it.
    Everything works fine.
    Thanks for the help!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Form Validation Rule from Another Query
    By CodenameAter in forum Forms
    Replies: 11
    Last Post: 05-28-2017, 08:41 PM
  2. Append Key Violations
    By Chad Access in forum Access
    Replies: 4
    Last Post: 04-27-2017, 03:32 PM
  3. key violations in append queries
    By louise in forum Queries
    Replies: 4
    Last Post: 06-07-2016, 08:55 AM
  4. Key Violations in an Append Query
    By petefc in forum Queries
    Replies: 1
    Last Post: 11-30-2012, 01:47 PM
  5. Field rules/validation rules
    By sk88 in forum Access
    Replies: 14
    Last Post: 02-26-2012, 01:03 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums