CODEWITHSUNDEEP
sqlms-access
Trung Tran
Trung Tran

Reputation: 11

Microsoft Access: run time error 3464 data type mismatch

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

Upvotes: 0

Views: 497

Answers (1)

Parfait
Parfait

Reputation: 107567

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

Upvotes: 1

Related Questions