Help Please! Error: "The decimal field's percussion is too small to accept the numeric you attempted to add." displaying when running make table query.

Question

Hi all:

I am running a make table query that is supposed to copy the data from our accounting system (store in a SQL database) and create a table in a MS Access 2013 database.  When I run the query I get the following error:

The decimal field's percussion is too small to accept the numeric you attempted to add.

I have tried the following and nothing worked:

1. As mentioned above used the "make table" query to create the duplicate table.
2. Manually created table and ensured all field data types, fields size, precision and scale are all exactly the same as the source table and then tried to append the data from the source table to the manually created table.
3. Have adjusted the scale and precision in the manually created table to larger values...as much as precision = 28 and scale =5
4. Have also tried changing all numeric fields in my destination table to size "double" and append data to the destination table from the query after applying the Cdbl([fieldname]) function to each field.

I have included a small sample of the data below

Can anyone please HELP me with this.  All help is greatly appreciated.

JnlYear	JnlMonth	Journal	EntryNumber	TrnType	Warehouse	StockCode	StockDescription	TrnQty	EnteredCost	EnteredBasis	Reference	AddReference	TrnUnitCost	TrnValue	PrevUnitCost	NewUnitCost	TransferToWh	Job	Bin	StockedFlag	PurchaseOrder	PurchaseOrderLin	BinXferIn	PrevQtyOnHand	Notation	TransferFlag	GrnSource	Uom	BackOrdFlag	ProductClass	InterfaceFlag	Supplier	Decimals	GlCode	GlYear	GlPeriod	RejectCode	SubcontractFlag	GlUpdated	AbcElements	ReceiptValue	PriceVarUpdGl	GitExpIssue	OrigPoQty	OutstPoQty	PoDueDate	LineDueDate	PoDebitGlCode	CostVariance	FifoBucket	GrnVarGlCode	UserField1	Version	Release	CommitmentFlag	AnalysisEntry	DrAnalysisEntry	PriceAnalysisEnt	TimeStamp	LotSerial
2017	5	6249	1	R	01	08-WW-11123	Boot 1	2	55	U	000000000		55	110	58.3	56.65			01	Y	000000	1		2	INV480451		1	EA		1130	P	V1453	3	22110-08	2017	5				0	0			2	0	12-Jan-17	12-Jan-17		0	0	51364-15					0	0	0
2017	5	6249	2	R	01	08-WW-11124	Boot 2	2	55	U	000000000		55	110	55	55			01	Y	000000	2		-1	INV480451		1	EA		1130	P	V1453	3	22110-08	2017	5				0	0			2	0	12-Jan-17	12-Jan-17		0	0	51364-15					0	0	0
2017	5	6249	3	R	01	08-WW-12112	Boot 3	1	66.5	U	000000000		66.5	66.5	66.5	66.5			01	Y	000000	6		-1	INV480451		1	EA		1130	P	V1453	3	22110-08	2017	5				0	0			3	2	12-Jan-17	12-Jan-17		0	0	51364-15					0	0	0
2017	5	6249	4	R	01	08-WW-31207	Boot 4	2	92	U	000000000		92	184	95.695	93.23167			01	Y	000000	9		1	INV480451		1	EA		1130	P	V1453	3	22110-08	2017	5				0	0			2	0	12-Jan-17	12-Jan-17		0	0	51364-15					0	0	0
2017	5	6249	5	R	01	08-WW-31209	Boot 5	2	92	U	000000000		92	184	92.24973	92.12487			01	Y	000000	11		2	INV480451		1	EA		1130	P	V1453	3	22110-08	2017	5				0	0			2	0	12-Jan-17	12-Jan-17		0	0	51364-15					0	0	0
2017	5	6249	6	R	01	08-WW-31213	Boot 6	2	92	U	000000000		92	184	92	92			01	Y	000000	12		1	INV480451		1	EA		1130	P	V1453	3	22110-08	2017	5				0	0			2	0	12-Jan-17	12-Jan-17		0	0	51364-15					0	0	0
2017	5	6249	7	R	01	08-WW-31214	Boot 7	2	92	U	000000000		92	184	92	92			01	Y	000000	13		1	INV480451		1	EA		1130	P	V1453	3	22110-08	2017	5				0	0			2	0	12-Jan-17	12-Jan-17		0	0	51364-15					0	0	0
2017	5	6249	8	R	01	08-WW-31217	Boot 8	2	92	U	000000000		92	184	92	92			01	Y	000000	14		1	INV480451		1	EA		1130	P	V1453	3	22110-08	2017	5				0	0			2	0	12-Jan-17	12-Jan-17		0	0	51364-15					0	0	0
2017	5	6249	9	R	01	08-WW-11133	Boot 9	1	55	U	000000000		55	55	52	55			01	Y	000000	15		-1	INV480451		1	EA		1130	P	V1453	3	22110-08	2017	5				0	0			1	0	12-Jan-17	12-Jan-17		0	0	51364-15					0	0	0

Answer 1

Well, that data sample is all but impossible to read. Can you post it as delimited rows?

CDbl() should take just about anything you throw at it. What is in a field that won't convert? Run your query with just the primary key and one field at a time to test. Then take a look in the SQL database at the data in that SQL column and its data type.

Answer 2

As Bill says, the sample is unreadable; and it looks like you have a whole LOT of fields. You describe some of the things you've tried but it's hard to follow. What ARE the datatypes some (not all dozens!) relevant fields in the SQL data? What datatypes and sizes have you tried in Access? What is the nature of the data (currency data I'd guess, give that it's Accounting - did you try the Currency datatype?)