Append query fails on validation rule violations

[CoffeeGuru]

Hi
Anyone have any idea why I get this message:

I am running an Append query which basically looks for records in my OPPORTUNITY table where the EVENT DATE is this month and the STATUS = invoice and INVOICED is False

What I want to do is to copy these to my INVOICES table ready for a mail merge with word (then set INVOICED to true - but I'm doing that bit in another query)

When I View the query - as in Datasheet View, I get the correct result but when I run the query I get Append query fails on validation rule violations

• I have a Key field which is AutoNumber that is not part of the Append Query
• All my fields in INVOICE are set to max where text
• Integers are set to Long Integer
• Indexed is always set to NO - with the exception of the Key
• Required is always set to NO
• Allow zero length is always set to YES

This is my Append Query

INSERT INTO Invoices ( ContactID, Status, Company, Address, Town, County, PostalCode, Event, EventDate, Performer, InvoiceDate )
SELECT Contacts.ContactID, "Invoiced" AS Status, Contacts.School, Contacts.Address, Contacts.Town, Contacts.County, Contacts.[Postal Code], Opportunities.Event, Opportunities.EventDate, Opportunities.Performer, Now() AS InvDate
FROM Contacts INNER JOIN Opportunities ON Contacts.ContactID = Opportunities.ContactID
WHERE Year([Opportunities].[EventDate])=Year(Now()) 
 AND Month([Opportunities].[Eventdate])=Month(Now())
 AND Opportunities.OppStatus Like "Inv*" 
 AND Opportunities.Invoiced=False;

 

[arnelgp]

how about if you re-arrange them:

INSERT INTO Invoices ( ContactID, Status, Company, Address, Town, County, PostalCode, Event, EventDate, Performer, InvoiceDate )
SELECT Contacts.ContactID, "Invoiced" AS Status, Contacts.School, Contacts.Address, Contacts.Town, Contacts.County, Contacts.[Postal Code], Opportunities.Event, Opportunities.EventDate, Opportunities.Performer, Now() AS InvDate
FROM Opportunities LEFT JOIN Contacts ON Opportunities.ContactID = Contacts.ContactID
WHERE Format([Opportunities].[EventDate], "yyyymm")= Format(Date(), "yyyymm")
 AND Opportunities.OppStatus Like "Inv*" 
 AND Opportunities.Invoiced=False;

 

[CoffeeGuru]

how about if you re-arrange them:

Well that appeared to work...can you shed any light on why!!

 

[arnelgp]

on the part "FROM Opportunities" since this is the table where you want to get the update from. also changed INNER JOIN to Left Join.