Error:3464 Data Type mismatch in Criteria Expression

[aman]

Hi All

I have a problem in MS Access which is very urgent to resolve. I have developed a form which has two textboxes that store from Date and To Date and one button. Based on these two values I have to generate a report in Excel. I developed everything and when i run it then it gives the above error message.
KIndly find the code written below.

Please can anyone help me..
Regards
Aman


Option Compare Database

Private Sub Command16_Click()

Call CreateExcelSheet

End Sub

Private Sub CreateExcelSheet()
On Error GoTo Err_CreateExcelSheet

Dim Pos1, Pos2, Pos3, Pos4, Pos5 As Integer
Dim CurCol, CurRow As Integer
Dim TotalSent, PerOfDocSent As Long
CurCol = 1
CurRow = 1

'Setting/Getting Date Range
Dim DateRangeFrom, DateRangeTo As String

DateRangeFrom = DTPickerFrom.Value
DateRangeTo = DTPickerTo.Value

'Lets Create an Excel Sheet Using Class CreateWorkSheet
Dim ExcelSheet1 As CreateWorkSheet



'Getting Total
Dim db As Database
Dim rs As Recordset
Dim strSql As String
strSql = "SELECT count(P2PRequestID) as TotalRequests" & _
" FROM tblDocumentRequest" & _
" WHERE Datevalue(RequestStartDateTime)>= #" & Format(DateRangeFrom, "MM/DD/YY") & "# And" & _
" Datevalue(RequestStartDateTime)<= #" & Format(DateRangeTo, "MM/DD/YY") & "#"
Set db = CurrentDb
Set rs = db.OpenRecordset(strSql, dbOpenSnapshot)
If rs.EOF = True Then
'Not Found Then...
GoTo CloseEveryThing 'Close EveryThing , The very Next Lines
Else

'Found then...
If rs!TotalRequests = 0 Then
CloseEveryThing:
rs.Close 'Close RecordSet
Set rs = Nothing 'Kill RS
Set db = Nothing 'Kill DB

Me.SetFocus
MsgBox "No Records Found for this DateRange.", vbInformation, "Report Cancelled"

Set ExcelSheet1 = Nothing

Exit Sub 'Get Out of This Function

Else

'Create Title in Sheet
Set ExcelSheet1 = New CreateWorkSheet
Call ExcelSheet1.AddToExcelSheet(CurRow, 1, "DRS MI Report", 14, True)
CurRow = CurRow + 2
Call ExcelSheet1.AddToExcelSheet(CurRow, 1, "Date Range:", 10, True)
Call ExcelSheet1.AddToExcelSheet(CurRow, 2, "From " & Format(DTPickerFrom.Value, "DD/MM/YY") & " To " & Format(DTPickerTo.Value, "DD/MM/YY"), 10, False)
CurRow = CurRow + 2
'Change ColWidth of Excel File
Call ExcelSheet1.ChangeColumnWidth("A:A", 14)
Call ExcelSheet1.ChangeColumnWidth("B:B", 48)

Call ExcelSheet1.VisibleExcelSheet(True)
Call ExcelSheet1.AddToExcelSheet(CurRow, 2, "Total Requests for Report Period:", 10, True)
Call ExcelSheet1.AddToExcelSheet(CurRow, 3, (rs!TotalRequests), 10, True)
End If
End If
rs.Close
Set rs = Nothing
Set db = Nothing

'Move 2 Lines Further
CurRow = CurRow + 2
Call ExcelSheet1.AddToExcelSheet(CurRow, 1, "Requests by Dept", 10, True)
CurRow = CurRow + 1


'Getting Totals By Dept
strSql = "SELECT RequestedForDept AS Dept, Count(P2PRequestID) AS TotalRequests" & _
" FROM tblDocumentRequest" & _
" WHERE Datevalue(RequestStartDateTime)>= #" & Format(DateRangeFrom, "MM/DD/YY") & "# And" & _
" Datevalue(RequestStartDateTime)<= #" & Format(DateRangeTo, "MM/DD/YY") & "#" & _
" GROUP BY RequestedForDept"
Set db = CurrentDb
Set rs = db.OpenRecordset(strSql, dbOpenSnapshot)
If rs.EOF = True Then
'No matching records found
Call ExcelSheet1.AddToExcelSheet(CurRow, 2, "Error/No Value", 10, False)
Else
Do Until rs.EOF
Call ExcelSheet1.AddToExcelSheet(CurRow, 2, CStr(rs!Dept), 10, False)
Call ExcelSheet1.AddToExcelSheet(CurRow, 3, CStr(rs!TotalRequests), 10, False)
CurRow = CurRow + 1
rs.MoveNext
Loop

End If
rs.Close
Set rs = Nothing
Set db = Nothing
'Move 2 Lines Further
CurRow = CurRow + 2
Call ExcelSheet1.AddToExcelSheet(CurRow, 1, "Requests by Types", 10, True)
CurRow = CurRow + 1



'Getting Total Requests By Types
strSql = "SELECT DocumentDescription , Count(P2PRequestID) AS TotalRequests" & _
" FROM tblDocumentRequest" & _
" WHERE Datevalue(RequestStartDateTime)>= #" & Format(DateRangeFrom, "MM/DD/YY") & "# And" & _
" Datevalue(RequestStartDateTime)<= #" & Format(DateRangeTo, "MM/DD/YY") & "#" & _
" GROUP BY DocumentDescription"
Set db = CurrentDb
Set rs = db.OpenRecordset(strSql, dbOpenSnapshot)
If rs.EOF = True Then
'No matching records found
Call ExcelSheet1.AddToExcelSheet(CurRow, 2, "Error/No Value", 10, True)
Else
Do Until rs.EOF
Call ExcelSheet1.AddToExcelSheet(CurRow, 2, CStr(rs!DocumentDescription), 10, False)
Call ExcelSheet1.AddToExcelSheet(CurRow, 3, CStr(rs!TotalRequests), 10, False)
CurRow = CurRow + 1
rs.MoveNext
Loop

End If
rs.Close
Set rs = Nothing
Set db = Nothing
'Move 2 Lines Further
CurRow = CurRow + 2
Call ExcelSheet1.AddToExcelSheet(CurRow, 1, "Document Action Summary", 10, True)
CurRow = CurRow + 1

Call ExcelSheet1.AddToExcelSheet(CurRow, 2, "Sum of Document Sent & NotSent for Report Period:", 10, True)
Pos1 = CurRow

CurRow = CurRow + 1
Call ExcelSheet1.AddToExcelSheet(CurRow, 2, "Action", 10, False)
Call ExcelSheet1.AddToExcelSheet(CurRow, 3, " Total", 10, False)
Call ExcelSheet1.AddToExcelSheet(CurRow, 4, " % ", 10, False)


CurRow = CurRow + 1

'Getting Total Requests By Types
Dim TotalActions As Long
strSql = "SELECT ProcessName , Count(P2PRequestID) AS TotalRequests" & _
" FROM tblProcessDocuments" & _
" WHERE Datevalue(ProcessDateTime)>= #" & Format(DateRangeFrom, "MM/DD/YY") & "# And" & _
" Datevalue(ProcessDateTime)<= #" & Format(DateRangeTo, "MM/DD/YY") & "#" & _
" GROUP BY ProcessName Order BY ProcessName Desc"
Set db = CurrentDb
Set rs = db.OpenRecordset(strSql, dbOpenSnapshot)
If rs.EOF = True Then
'No matching records found
Call ExcelSheet1.AddToExcelSheet(CurRow, 2, "Error/No Value", 10, True)
Else
Do Until rs.EOF
Call ExcelSheet1.AddToExcelSheet(CurRow, 2, CStr(rs!ProcessName), 10, False)
Call ExcelSheet1.AddToExcelSheet(CurRow, 3, CStr(rs!TotalRequests), 10, False)

Pos2 = (Pos1 - CurRow)
Call ExcelSheet1.ApplyFormula(CurRow, 4, "=RC[-1]/R[" & Pos2 & "]C[-1]", "0%")


'Calculate only Sent/Not Sent ' Business Logic
If CStr(rs!ProcessName) = "WL - Document Not Sent" Or CStr(rs!ProcessName) = "WL - Document Sent" Then
TotalActions = TotalActions + (rs!TotalRequests)
End If

'Calculate Only Sent ' Business Logic
If CStr(rs!ProcessName) = "WL - Document Sent" Then
TotalSent = CLng(rs!TotalRequests)
Pos3 = CurRow
End If

'Find When is Document Received
If CStr(rs!ProcessName) = "AXA Receive Document" Then

Pos4 = CurRow - Pos3


Call ExcelSheet1.ApplyFormula(CurRow, 4, "=RC[-1]/R[" & -Pos4 & "]C[-1]", "0%")

'PerOfDocSent = (CLng(rs!TotalRequests) / TotalSent) '* 100

'MsgBox PerOfDocSent

'Call ExcelSheet1.AddToExcelSheet(CurRow, 4, CStr(PerOfDocSent), 10, False)
End If


CurRow = CurRow + 1

rs.MoveNext
Loop
'Save Total on a Per Pos
Call ExcelSheet1.AddToExcelSheet(CInt(Pos1), 3, CStr(TotalActions), 10, False)
End If
rs.Close
Set rs = Nothing
Set db = Nothing

Set ExcelSheet1 = Nothing
Exit_CreateExcelSheet:
Exit Sub
Err_CreateExcelSheet:
MsgBox Err.Description, vbCritical, "CreateExcelSheet"
Resume Exit_CreateExcelSheet

End Sub

 

[DCrake]

First of all when sending code please wrap it in code tags. And when mentioning errors please let the reader know which line is generating the error.