Link to home
Start Free TrialLog in
Avatar of jumpy262000
jumpy262000

asked on

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
Avatar of mbizup
mbizup
Flag of Kazakhstan image

If this is an Access Database, use #'s for date delimiters:

strSQL = strSQL & "[datDate] = #" & Me.datDate & "#"
Avatar of jumpy262000
jumpy262000

ASKER

I received a Run time error 13, type mismatch for Line:
Set rs = DB.OpenRecordset(strSQL, dbOpenDynaset, dbSeeChanges)
ASKER CERTIFIED SOLUTION
Avatar of Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1)
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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 & "#"
 

SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thanks to you both, hope the rest of you're morning goes well.
Glad to help :-)