Microsoft Access: run time error 3464 data type mismatch

Question

I have a database that worked perfectly before, but today for some reason it has this error:

run time error 3464 data type mismatch

When I click debug, it pointed out the line that has the error:

Set rst = CurrentDb().OpenRecordset(strSQL)

How could I fix it? Thank you!!!!

Below is my code:

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

Can you run it again without the () after CurrentDB? It's been a while since I did VBA in ms-access, but that feels wrong. — JNevill
– JNevill, Commented Apr 16, 2021 at 19:47
Thank you JNevill, I did it but it still has that error. It wouldn't go away :( — Trung Tran
– Trung Tran, Commented Apr 16, 2021 at 20:18
Try adding a space in ([ref_num]is null) so ([ref_num] is null). — June7
– June7, Commented Apr 16, 2021 at 20:55

Parfait · Accepted Answer · 2021-04-16 20:34:06Z

Consider using parameterized stored queries which is more efficient than VBA run queries especially with JOIN and UNION since Access engine saves best execution plan. This also helps avoid concatenation of SQL and VBA, enhances readability and maintainability, and better aligns data types.

SQL (save as a stored query; PARAMETERS clause is supported in Access SQL)

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

Collectives™ on Stack Overflow

Microsoft Access: run time error 3464 data type mismatch

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related