VB.NET执行Access SELECT查询报错：Decimal字段精度过小求助

有一个SELECT查询在MS Access中运行正常，但在VB.NET通过OleDb执行时抛出精度错误，最终需求是将查询结果转为字符串导出至Excel，原本以为只有插入表操作才会出现这类问题。

错误信息 #

System.Data.OleDb.OleDbException: 'The decimal field's precision is too small to accept the numeric you attempted to add.'

相关代码 #

'data
Dim Q1 As String = "SELECT [WARR INV1].invdate, [WARR INV1].yr, [WARR INV1].wk, [WARR INV1].Customer, [WARR INV1].InvoiceCustomerName AS Customer_Name, [warr Invoice 2].Charge, [warr Invoice 2].InvoiceLineTypeDes, [warr Invoice 2].InvoiceLineBasisDes, [warr Invoice 2].InvoiceLineQty, [warr Invoice 2].InvoiceLineRate, [warr Invoice 2].InvoiceLineTotal, [warr Invoice 2].InvoiceLineVATTot, [WARR INV1].InvoiceRef AS [Invoice No], [WARR INV1].InvoiceType, [dels grouped by week].SumOfloads AS receipts, [recpts grouped by week].SumOfplts AS [recpt plts], [recpts grouped by week].SumOfcases AS cases, [dels grouped by week].SumOfloads AS Deliveries, [dels grouped by week].SumOfplts AS [Del plts], [dels grouped by week].SumOfcases AS [Del Cases]" &
" FROM [Determine week] LEFT JOIN ((([WARR INV1] LEFT JOIN [warr Invoice 2] On [WARR INV1].InvoiceRef = [warr Invoice 2].InvoiceRef) LEFT JOIN [recpts grouped by week] On ([WARR INV1].yr = [recpts grouped by week].yr) And ([WARR INV1].wk = [recpts grouped by week].s) And ([WARR INV1].Customer = [recpts grouped by week].Customer)) LEFT JOIN [dels grouped by week] On ([WARR INV1].yr = [dels grouped by week].yr) And ([WARR INV1].wk = [dels grouped by week].WK) And ([WARR INV1].Customer = [dels grouped by week].Customer)) On [Determine week].wk = [WARR INV1].wk" &
" WHERE ((([WARR INV1].yr)='2024') AND (([WARR INV1].wk)='33') AND (([warr Invoice 2].InvoiceLineTypeDes) Is Not Null));"

osheet = oWB.Worksheets("Sheet1")

'CLEAR DATA
Dim rg = osheet.Range("a1:t1500")
rg.Clear()

y = 2

Dim cmd As New OleDbCommand(Q1, cnn)
Dim TheDataReader As OleDbDataReader = cmd.ExecuteReader() ' errors here

osheet.Cells(1, 1) = "Invoice Date"
osheet.Cells(1, 2) = "Year"
osheet.Cells(1, 3) = "Customer"
osheet.Cells(1, 4) = "Customer Name"
osheet.Cells(1, 5) = "Charge"
osheet.Cells(1, 6) = "Invoice Sub-Type"
osheet.Cells(1, 7) = "Line Description"
osheet.Cells(1, 8) = "Invoice Ln Qty"
osheet.Cells(1, 9) = "Invoice Ln Rate"
osheet.Cells(1, 10) = "Invoice Ln Total"
osheet.Cells(1, 11) = "Invoice Ln VAT"
osheet.Cells(1, 12) = "Invoice Number"
osheet.Cells(1, 13) = "Invoice Type"
osheet.Cells(1, 14) = "Week"
osheet.Cells(1, 15) = "Receipts"
osheet.Cells(1, 16) = "Receipt Plts"
osheet.Cells(1, 17) = "Receipt Cases"
osheet.Cells(1, 18) = "Deliveries"
osheet.Cells(1, 19) = "Delivery Plts"
osheet.Cells(1, 20) = "Delivery Cases"

While TheDataReader.Read()
    osheet.Cells(y, 1) = TheDataReader("Invdate").ToString()
    osheet.Cells(y, 2) = TheDataReader("yr").ToString()
    osheet.Cells(y, 3) = TheDataReader("customer").ToString()
' 后续代码省略

解决指引 #

• 修正分组查询的计算精度：查询中用到的[recpts grouped by week]和[dels grouped by week]是分组查询，其Sum计算结果可能超出OleDb驱动默认的精度限制。修改分组查询里的Sum表达式，显式转换类型，比如用CDbl(Sum(loads)) AS SumOfloads将结果转为Double，或者用CStr(Sum(loads)) AS SumOfloads直接转为字符串（适配最终导出需求）。
• 显式转换查询中的数值字段：将SELECT语句中所有数值类型字段（如InvoiceLineTotal、InvoiceLineVATTot）转为字符串或更高精度类型，例如CStr([warr Invoice 2].InvoiceLineTotal) AS InvoiceLineTotal，避免OleDb在类型映射时触发精度错误。
• 检查连接字段类型匹配：确认连接条件中的字段类型一致，比如[WARR INV1].wk和[recpts grouped by week].s，若一个是文本、一个是数值，Access内部会自动转换，但OleDb驱动可能因类型不匹配引发精度问题。
• 分步定位问题来源：将复杂查询拆分为多个子查询分步执行，先验证[WARR INV1] LEFT JOIN [warr Invoice 2]的部分，再逐步加入其他连接，定位具体是哪个子查询或字段导致的精度错误。

内容的提问来源于stack exchange，提问作者Robert Bee