MS Access error: Cant Add All of the Records in the Update or Append Query

[Dilbert99]

0
i researched the knowledge base for this MS Access error I'm getting, but didn't find the answer i needed. I am using a query to Make a table. When it runs, I get the following error: MS Access error: Cant Add All of the Records in the Updte or Append Query: It set 8 Fields to Null due to a type conversion error. I can understand getting this error if i am trying to Update or append a table, but this query is MAKING a table, so I don't understand why there would be a conversion error.
Thank you anyone who can help me



I have attached a snapshot of the query and the error message. I have also attached a copy of the Access file.


here is the Query - in SQL format:

SELECT CrossWalk.Customer_Seq_Num AS Seq, CrossWalk.THG_Description AS [THG Description], CrossWalk.Merit_Item_Num AS [Merit ID#], Merit.Description AS [Description-Merit], Merit.[Unit Meas#] AS [U/M-Merit], Merit.[Pack Size] AS [Pkg Size-Merit], Merit.[Unit Price] AS [Price-Merit], CrossWalk.Factor_Merit AS [Merit Factor], IIf([CrossWalk]![Factor_Merit]>0,Round(([Merit]![Unit Price]/[CrossWalk]![Factor_Merit]),2),0) AS [PricePer-Merit], Merit.Weight AS Weight, CrossWalk.USFood_Item AS [USFood ID#], USFoods.[Product Desc] AS [Description-USF], USFoods.[Product UOM] AS [U/M-USF], CrossWalk.Factor_USFoods AS [Factor-USF], USFoods.[Product Package Size] AS [Pkg Size-USF], USFoods.[Product Price] AS [Price-USF], IIf([CrossWalk]![Factor_USFoods]>0,Round([USFoods]![Product Price]/[CrossWalk]![Factor_USFoods],2),0) AS [Price-Per USF], CrossWalk.ShamRock_Item_Num, Shamrock.Description AS [Description-Shamrock], Shamrock.Unit AS [U/M-Sham], CrossWalk.Factor_Shamrock AS [Factor-Sham], Shamrock.[Pack Size] AS [Pkg Size-Sham], Shamrock.Price AS [Price-Sham], IIf([CrossWalk]![Factor_Shamrock]>0,Round([Shamrock]![Price]/[CrossWalk]![Factor_Shamrock],2),0) AS [Price-Per Sham], CrossWalk.SysCo_Item_Num AS SUPC, Sysco.Desc AS [Description-Sysco], Sysco.Unit AS [U/M-Sysco], CrossWalk.Factor_SysCo AS [Factor-Sysco], Sysco.Pack AS [Qty-Sysco], Sysco.Size AS [Sysco -Size], Sysco.[Case $] AS [Sysco-Case $], Sysco.[Split $] AS [Sysco-Split$], IIf([CrossWalk]![Factor_SysCo]>0,Round([Sysco]![Case $]/[CrossWalk]![Factor_SysCo],2),0) AS [Price-Per Sysco], RestDepot.Item AS [RestDepot Item], RestDepot.Description AS [RestDepot-Desc], RestDepot.[U/M] AS [RestDepot-UnitMeas], RestDepot.[Est#Price] AS [RestDepot- Est Price], CrossWalk.Factor_RestDepot AS [RestDepot-Factor], IIf([CrossWalk]![Factor_RestDepot]>0,Round([RestDepot]![Est#Price]/[CrossWalk]![Factor_RestDepot],2),0) AS [RestDepot-Price-Per] INTO [COMPARE SPREADSHEET]
FROM RestDepot RIGHT JOIN (Merit RIGHT JOIN (USFoods RIGHT JOIN (Sysco RIGHT JOIN (Shamrock RIGHT JOIN CrossWalk ON Shamrock.[Product #] = CrossWalk.ShamRock_Item_Num) ON Sysco.SUPC = CrossWalk.SysCo_Item_Num) ON USFoods.[Product Number] = CrossWalk.USFood_Item) ON Merit.[Item Number] = CrossWalk.Merit_Item_Num) ON RestDepot.[Item] = CrossWalk.RestDepot_Item_Num
WHERE (((CrossWalk.THG_Description) Is Not Null))
ORDER BY CrossWalk.Customer_Seq_Num;

 

[theDBguy]

Hi. Just looking at your image, I see you have some calculated columns in your query. Maybe the type conversion error is happening there? Does it work okay if your change it to a SELECT query just for testing?

 

[Minty]

I would suspect it is in the IIf statements or the calculated fields and nulls.

Edit - DBGuy beat me too it...

 

[pbaldy]

I wondered if the make table was making an assumption about the data type of a column based on the first x records, and later records violated that assumption. That can happen with imports, not sure about make table queries.

 

[Isaac]

As Paul said remember that even though it's a make table query, Access needs to make some 'decisions' as it goes. One decision will be the data type based on what it 'sees'. Then it will exclude some records that violate that assumption 'later'.

Go through your query in the query design view column by column. Identify any cases where your various expressions and calculations might leave room for more than one data type. Resolve those by adding more logic that considers things like nulls, numbers stored as strings, or even errors in your expression result

 

[isladogs]

I agree with previous comments about the likely issues.
I did try running the make table query with this error message

Now that's odd for a number of reasons.
1. First of all that it wrongly refers to an update or append query
2. Also that it refers to 299 fields (not records) when the table actually has 'only' 23 fields.

The SELECT query version does run correctly though with a lot of null values in several fields.

I suggest you instead do the following
1. Add a primary key field to the table. Check that each field has the correct datatype
2. Scrap the make table query and replace with two queries
A) a DELETE query to empty the table (remove all existing table records
B) an APPEND query to insert new records.
If that still errors, look into the suggestions given in the replies above

 

[smtazulislam]

Excuse me for jumping.
can you check it.