Greetings,
Firstly I should point out that I have very limited access experience, as
such I try my best with help files, tutorials and forums.
I have put together a excel export from access which seems to work fine when I have a date criteria in the query with a defined date Between #20/07/09# and #26/07/09# but when I have the following criteria Between [forms]![criteria - payroll report]![txtdate] And [forms]![criteria - payroll report]![txtDate]-6 I keep getting Error 3464 Data type mismatch in criteria expression. In the line Set rst = qdf.OpenRecordset(dbOpenDynaset)
Could someone please advise where I've gone astray ... many thanks in advance ... Jack
Private Sub butExcel_Click()
Dim db As Database
Dim rst As Recordset
Dim qdf As QueryDef
Dim prm As Parameter
Dim objApp As Excel.Application
Dim objBook As Excel.Workbook
Dim objPivotTable As Excel.PivotTable
Dim lngRows As Long
Dim iWeekday As Integer
Set db = CurrentDb()
Set qdf = db.QueryDefs("qryTimesheet - Excel Payroll Dump")
iWeekday = Weekday(txtDate)
If IsNull(txtDate) Then
MsgBox "You must enter the week ending date!", vbOKOnly, "CHLOE"
txtDate.SetFocus
Exit Sub
ElseIf IsNull(txtInitial) Then
MsgBox "You must enter the Supervisors Initial!", vbOKOnly, "CHLOE"
txtInitial.SetFocus
Exit Sub
End If
If iWeekday > vbSunday Then
MsgBox "Enter a WEEK ENDING DATE (Sunday)", vbOKOnly, "CHLOE"
txtDate.SetFocus
txtDate = Empty
Exit Sub
End If
For Each prm In qdf.Parameters
prm.Value = Eval(prm.Name)
Next prm
Set rst = qdf.OpenRecordset(dbOpenDynaset)
Set objBook = Workbooks.Add(Template:="c:\chloe\documents\Payroll Excel.xls ")
Set objApp = objBook.Parent
objBook.Windows(1).Visible = True
Set objPivotTable = objBook.Worksheets("Timesheet").Range("A3").PivotTable
lngRows = rst.RecordCount
With objBook.Worksheets("Raw")
.Range("a2").CopyFromRecordset rst
.Visible = xlSheetHidden
End With
objPivotTable.RefreshTable
With objBook.Worksheets("Timesheet")
.Range("E2:M2").Font.Bold = True
.Range("E2:M2").HorizontalAlignment = xlCenter
.Range("E:M").ColumnWidth = 10
End With
objApp.Visible = True
objBook.SaveAs Filename:="c:\Chloe\Payroll\WE " & Format(txtDate, ("dd-MMM-yy ")) & txtInitial & ".xls"
objApp.Quit
Set db = Nothing
Set rst = Nothing
Set qdf = Nothing
Set prm = Nothing
Set objApp = Nothing
Set objBook = Nothing
rst.Close
DoCmd.Close acForm, "Criteria - Payroll Report", acSaveNo
Errortrap:
'MsgBox "Error!!", vbOKOnly, "CHLOE"On Error GoTo Errortrap
End Sub
Firstly I should point out that I have very limited access experience, as
such I try my best with help files, tutorials and forums.
I have put together a excel export from access which seems to work fine when I have a date criteria in the query with a defined date Between #20/07/09# and #26/07/09# but when I have the following criteria Between [forms]![criteria - payroll report]![txtdate] And [forms]![criteria - payroll report]![txtDate]-6 I keep getting Error 3464 Data type mismatch in criteria expression. In the line Set rst = qdf.OpenRecordset(dbOpenDynaset)
Could someone please advise where I've gone astray ... many thanks in advance ... Jack
Private Sub butExcel_Click()
Dim db As Database
Dim rst As Recordset
Dim qdf As QueryDef
Dim prm As Parameter
Dim objApp As Excel.Application
Dim objBook As Excel.Workbook
Dim objPivotTable As Excel.PivotTable
Dim lngRows As Long
Dim iWeekday As Integer
Set db = CurrentDb()
Set qdf = db.QueryDefs("qryTimesheet - Excel Payroll Dump")
iWeekday = Weekday(txtDate)
If IsNull(txtDate) Then
MsgBox "You must enter the week ending date!", vbOKOnly, "CHLOE"
txtDate.SetFocus
Exit Sub
ElseIf IsNull(txtInitial) Then
MsgBox "You must enter the Supervisors Initial!", vbOKOnly, "CHLOE"
txtInitial.SetFocus
Exit Sub
End If
If iWeekday > vbSunday Then
MsgBox "Enter a WEEK ENDING DATE (Sunday)", vbOKOnly, "CHLOE"
txtDate.SetFocus
txtDate = Empty
Exit Sub
End If
For Each prm In qdf.Parameters
prm.Value = Eval(prm.Name)
Next prm
Set rst = qdf.OpenRecordset(dbOpenDynaset)
Set objBook = Workbooks.Add(Template:="c:\chloe\documents\Payroll Excel.xls ")
Set objApp = objBook.Parent
objBook.Windows(1).Visible = True
Set objPivotTable = objBook.Worksheets("Timesheet").Range("A3").PivotTable
lngRows = rst.RecordCount
With objBook.Worksheets("Raw")
.Range("a2").CopyFromRecordset rst
.Visible = xlSheetHidden
End With
objPivotTable.RefreshTable
With objBook.Worksheets("Timesheet")
.Range("E2:M2").Font.Bold = True
.Range("E2:M2").HorizontalAlignment = xlCenter
.Range("E:M").ColumnWidth = 10
End With
objApp.Visible = True
objBook.SaveAs Filename:="c:\Chloe\Payroll\WE " & Format(txtDate, ("dd-MMM-yy ")) & txtInitial & ".xls"
objApp.Quit
Set db = Nothing
Set rst = Nothing
Set qdf = Nothing
Set prm = Nothing
Set objApp = Nothing
Set objBook = Nothing
rst.Close
DoCmd.Close acForm, "Criteria - Payroll Report", acSaveNo
Errortrap:
'MsgBox "Error!!", vbOKOnly, "CHLOE"On Error GoTo Errortrap
End Sub