• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 476
  • Last Modified:

MS Access type Mismatch in criteria expression

I'm receiving a run-time error "3464" Data type mismatch in criteria expression for line:  
strSQL = strSQL & "[datDate] = '" & Me.datDate & "'"

me.datDate is a date value.  

Dim DB As Database
Dim rs As Recordset
Dim strSQL As String

    Set DB = CurrentDb
   
If IsNull(Me.datDate) Then
MsgBox "Please a date value"
Exit Sub
End If

    strSQL = "Select * from tblNewReceipts Where"
    strSQL = strSQL & "[datDate] = '" & Me.datDate & "'"
 
    Set rs = DB.OpenRecordset(strSQL, dbOpenDynaset, dbSeeChanges)

   Me.Dept62BreakdownCost = rs!Dept62BreakdownCost
   Me.Dept62BreakdownRetail = rs!Dept62BreakdownRetail
   Me.Dept62NeedtoSortCost = rs!Dept62NeedtoSortCost
   Me.Dept62NeedtoSortRetail = rs!Dept62NeedtoSortRetail
   Me.Dept71BreakdownCost = rs!Dept71BreakdownCost
   Me.Dept71BreakdownRetail = rs!Dept71BreakdownRetail
   Me.Dept71NeedtoSortCost = rs!Dept71NeedtoSortCost
   Me.Dept71NeedtoSortRetail = rs!Dept71NeedtoSortRetail
   Me.Dept84BreakdownCost = rs!Dept84BreakdownCost
   Me.Dept84BreakdownRetail = rs!Dept84BreakdownRetail
   Me.Dept84NeedtoSortCost = rs!Dept84NeedtoSortCost
   Me.Dept84NeedtoSortRetail = rs!Dept84NeedtoSortRetail
   Me.Dept62Cost = rs!Dept62PalletsonFloorybyVendorCost
   Me.Dept62Retail = rs!Dept62PalletsonFloorybyVendorRetail
   Me.Dept71Cost = rs!Dept71PalletsonFloorybyVendorCost
   Me.Dept71Retail = rs!Dept71PalletsonFloorybyVendorRetail
   Me.Dept84Cost = rs!Dept84PalletsonFloorybyVendorCost
   Me.Dept84Retail = rs!Dept84PalletsonFloorybyVendorRetail
   Me.datDate = rs!datDate

   
    rs.Close
    DB.Close
    Set rs = Nothing
    Set DB = Nothing
0
jumpy262000
Asked:
jumpy262000
  • 3
  • 2
  • 2
2 Solutions
 
mbizupCommented:
If this is an Access Database, use #'s for date delimiters:

strSQL = strSQL & "[datDate] = #" & Me.datDate & "#"
0
 
jumpy262000Author Commented:
I received a Run time error 13, type mismatch for Line:
Set rs = DB.OpenRecordset(strSQL, dbOpenDynaset, dbSeeChanges)
0
 
Rey Obrero (Capricorn1)Commented:
your strSQL is also missing a space


change
    strSQL = "Select * from tblNewReceipts Where"
    strSQL = strSQL & "[datDate] = '" & Me.datDate & "'"
 
to



    strSQL = "Select * from tblNewReceipts Where  "
    strSQL = strSQL & "[datDate] = #" & Me.datDate & "#"
 
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
Rey Obrero (Capricorn1)Commented:
or if  [datDate] is text

     strSQL = "Select * from tblNewReceipts Where "
     strSQL = strSQL & "[datDate] = '" & Me.datDate & "'"

if date

    strSQL = "Select * from tblNewReceipts Where  "
    strSQL = strSQL & "[datDate] = #" & Me.datDate & "#"
 

0
 
mbizupCommented:
>I received a Run time error 13, type mismatch for Line:

You also need to explicitly define rs as DAO:

Dim rs As DAO.Recordset  '<-----*** Change this code to DAO
0
 
jumpy262000Author Commented:
Thanks to you both, hope the rest of you're morning goes well.
0
 
mbizupCommented:
Glad to help :-)
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 3
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now