MS Access error: Cant Add All of the Records in the Update or Append Query (1 Viewer)

Dilbert99

New member
Local time
Today, 19:02
Joined
Jan 9, 2020
Messages
27
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;
 

Attachments

  • z88ZT.jpg
    z88ZT.jpg
    200.5 KB · Views: 524
  • CROSSWALK MASTER-AccessProgramv23-E_FORUM.accdb
    4.4 MB · Views: 384

theDBguy

I’m here to help
Staff member
Local time
Today, 16:02
Joined
Oct 29, 2018
Messages
21,358
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

AWF VIP
Local time
Today, 23:02
Joined
Jul 26, 2013
Messages
10,355
I would suspect it is in the IIf statements or the calculated fields and nulls.

Edit - DBGuy beat me too it...
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 16:02
Joined
Aug 30, 2003
Messages
36,118
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

Lifelong Learner
Local time
Today, 16:02
Joined
Mar 14, 2017
Messages
8,738
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

MVP / VIP
Local time
Today, 23:02
Joined
Jan 14, 2017
Messages
18,186
I agree with previous comments about the likely issues.
I did try running the make table query with this error message
1616608283176.png


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

Member
Local time
Tomorrow, 02:02
Joined
Mar 27, 2020
Messages
806
Excuse me for jumping.
can you check it.
 

Attachments

  • CROSSWALK MASTER-AccessProgramv23-E_FORUM.accdb
    2 MB · Views: 543

Users who are viewing this thread

Top Bottom