Microsoft Access:运行时错误3464数据类型不匹配

Question

我有一个数据库以前运行得很好，但今天由于某种原因出现了这个错误：

运行时错误3464数据类型不匹配

当我点击调试时，它指出了有错误的行：

Set rst = CurrentDb().OpenRecordset(strSQL)

我该怎么修？非常感谢。

以下是我的代码：

Function DmdQtyThruDate(ItemVar As String, DatePeg As Date) As Double
Dim rst As DAO.Recordset
Dim strSQL As String

strSQL = "SELECT Sum(TotQty) AS TotTotQty "
strSQL = strSQL & "FROM ("
strSQL = strSQL & "SELECT DISTINCTROW Sum([dbo_apsplan].[qty]) AS TotQty "
strSQL = strSQL & "FROM [dbo_apsplan] LEFT JOIN [dbo_job_sch] ON (([dbo_apsplan].[ref_line_suf] = [dbo_job_sch].[suffix]) AND ([dbo_apsplan].[ref_num] = [dbo_job_sch].[job])) "
strSQL = strSQL & "WHERE ([dbo_apsplan].[item]='" & ItemVar & "' AND [dbo_apsplan].[is_demand]=1 AND DateValue(IIf([dbo_apsplan].[ref_type]='j',[dbo_job_sch].[start_date],[dbo_apsplan].[due_date]))<=DateValue(#" & DatePeg & "#)) "
strSQL = strSQL & "UNION ALL "
strSQL = strSQL & "SELECT Sum([qty_ordered]-[qty_shipped]) AS TotQty "
strSQL = strSQL & "FROM [dbo_coitem] "
strSQL = strSQL & "WHERE (([item]='" & ItemVar & "') AND ([ref_num]is null) AND ([stat]='o' Or [stat]='p') AND (DateValue([due_date])<=DateValue(#" & DatePeg & "#)))); "

Set rst = CurrentDb().OpenRecordset(strSQL)
If rst.EOF = False Then
DmdQtyThruDate = Nz(rst("TotTotQty"), 0)
Else
DmdQtyThruDate = 0
End If

rst.Close
Set rst = Nothing
End Function

Answer 1

考虑使用参数化存储查询，它比VBA运行查询更高效，尤其是使用JOIN和UNION，因为Access引擎可以保存最佳执行计划。这也有助于避免SQL和VBA的串联，增强可读性和可维护性，并更好地对齐数据类型。

SQL (另存为存储查询；Access SQL中支持PARAMETERS子句(

PARAMETERS prmItemVar Text, prmDatePeg Date;
SELECT SUM(TotQty) AS TotTotQty
FROM (
SELECT SUM(a.[qty]) AS TotQty
FROM [dbo_apsplan] a 
LEFT JOIN [dbo_job_sch] j 
ON ((a.[ref_line_suf] = j.[suffix]) 
AND (a.[ref_num] = j.[job]))
WHERE a.[item] = prmItemVar
AND a.[is_demand]=1 
AND DateValue(IIf(a.[ref_type] = 'j', j.[start_date], a.[due_date])
) <= prmDatePeg

UNION ALL
SELECT SUM([qty_ordered] - [qty_shipped]) AS TotQty
FROM [dbo_coitem]
WHERE [item] = prmItemVar
AND [ref_num] IS NULL
AND ([stat]='o' OR [stat]='p')
AND DateValue([due_date]) <= prmDatePeg
);

VBA

Function DmdQtyThruDate(ItemVar As String, DatePeg As Date) As Double
Dim qDef   As DAO.QueryDef
Dim rst    As DAO.Recordset
Dim strSQL As String

' INITIALIZE QUERYDEF
Set qDef = CurrentDb.QueryDefs("mySavedQuery")    
' BIND PARAMETERS
qDef!prmItemVar = ItemVar
qDef!prmDatePeg = DatePeg
' OPEN RECORDSET
Set rst = qDef.OpenRecordset()
If rst.EOF = False Then
DmdQtyThruDate = Nz(rst("TotTotQty"), 0)
Else
DmdQtyThruDate = 0
End If

rst.Close
Set rst = Nothing: Set qDef = Nothing
End Function