see post #11
but when run on the form it does not
see post #11
but when run on the form it does not
Hi Ajax
I have commented out the parameters
Code:' this is the SQL that the record set uses to create the Excel Sheet strSql = "SELECT Count(AssetNotes.AssetNotesID) AS [Number Of Tickets]," strSql = strSql & " AssetNotesType.NoteType," strSql = strSql & " Nz(Sum(DateDiff('n',[issueOpenedTime],[NoteEndtime])),0) AS [Actual Time]," strSql = strSql & " Minutes2Duration([actual time]) As [Formatted Time]" strSql = strSql & " FROM AssetNotesType RIGHT JOIN AssetNotes ON AssetNotesType.ID = AssetNotes.NotesType" strSql = strSql & " WHERE AssetNotes.IssueClosed" ' strSql = strSql & " Between " & [Forms].[frmITHelpDeskResponceTimeDates].[StartDate] & " And" ' strSql = strSql & " " & [Forms].[frmITHelpDeskResponceTimeDates].[EndDate] strSql = strSql & " GROUP BY AssetNotesType.NoteType, AssetNotesType.ID" strSql = strSql & " HAVING (((AssetNotesType.NoteType) Not Like 'General Note') AND" strSql = strSql & " ((Nz(Sum(DateDiff('n',[issueOpenedTime],[NoteEndtime])),0)) Is Not Null));"
and still get the same issue,
I have changed the code on my form, to remove the if statements that ran to check if Excel is open / installed
the code I have now is this
I did this out of desperation really to see if I had an error in my logic, when this now runs from the form I getCode:Dim objXlApp As Object Dim objXlBook As Object Dim objXlSheet As Object Dim strSql As String Dim objRs1 As DAO.Recordset 'This Is A Object Dim iRowStart As Integer Const XLCENTER = -4108 Const XLLEFT = -4131 'xlRight is -4152 Const XLCELLVALUE = 1 Const XLLESSEQUAL = 8 Const XLEQUAL = 3 ' Const xlGreater = 5 ' Const xlExpression = 2 ' this is the SQL that the record set uses to create the Excel Sheet strSql = "SELECT Count(AssetNotes.AssetNotesID) AS [Number Of Tickets]," strSql = strSql & " AssetNotesType.NoteType," strSql = strSql & " Nz(Sum(DateDiff('n',[issueOpenedTime],[NoteEndtime])),0) AS [Actual Time]," strSql = strSql & " Minutes2Duration([actual time]) As [Formatted Time]" strSql = strSql & " FROM AssetNotesType RIGHT JOIN AssetNotes ON AssetNotesType.ID = AssetNotes.NotesType" strSql = strSql & " WHERE AssetNotes.IssueClosed" ' strSql = strSql & " Between " & [Forms].[frmITHelpDeskResponceTimeDates].[StartDate] & " And" ' strSql = strSql & " " & [Forms].[frmITHelpDeskResponceTimeDates].[EndDate] strSql = strSql & " GROUP BY AssetNotesType.NoteType, AssetNotesType.ID" strSql = strSql & " HAVING (((AssetNotesType.NoteType) Not Like 'General Note') AND" strSql = strSql & " ((Nz(Sum(DateDiff('n',[issueOpenedTime],[NoteEndtime])),0)) Is Not Null));" 'Show The Output in the imediate window Debug.Print strSql Set objRs1 = CurrentDb.OpenRecordset("strSql", dbOpenSnapshot) 'If The Query Holds Now Data within the Dat rang, don't bother opening Excel, just quit 'If no data, don't bother opening Excel, just quit If objRs1.EOF Then MsgBox "NO DATA SELECTED FOR EXPORT", vbInformation + vbOKOnly, "NO DATA TO EXPORT" 'DoCmd.Close acForm, "frmITHelpDeskIssuesByAverageTime", acSavePrompt Else 'Now we Can Create the Excel Sheet On The Fly, We Shall Turn On The Hour Glass, So That Users Know That Something Is Happening DoCmd.Hourglass (True) 'Create an instance of Excel and start building a spreadsheet Late Binding Used So No Refrences Required Set objXlApp = CreateObject("Excel.Application") objXlApp.Visible = False Set objXlBook = objXlApp.Workbooks.Add() 'start a new workbook Set objXlSheet = objXlBook.Worksheets(1) With objXlSheet .Name = "Ticket Responce Time Report" .Cells.Font.Name = "Franklin Gothic Book" .Cells.Font.Size = 10 ' 'Format range as required To Bold a Cell use this for example .Range("A1").Cells.Font.Bold = True .Range("A1", "H1").Merge .Range("A1").Columns.AutoFit .Range("A2", "H2").Merge .Range("A2").Columns.AutoFit .Range("A1").HorizontalAlignment = XLLEFT .Range("A2").HorizontalAlignment = XLLEFT .Range("A1").Cells.Font.Name = "Franklin Gothic Book" .Range("A2").Cells.Font.Name = "Franklin Gothic Book" .Range("A1").Cells.Font.Size = 12 .Range("A2").Cells.Font.Size = 10 .Range("A1").Value = "Report Required" .Range("A2").Value = "Exported ON" & " - " & Date ' 'Now We Shall Build The Colum Headings.value is the text required .Range("A4").Value = "Note Type" .Range("B4").Value = "Acutal Time" .Range("C4").Value = "Hold Time" .Range("D4").Value = "Total Minutes" .Range("E4").Value = "Number Of Tickets" .Range("F4").Value = "Avg Minutes" .Range("G4").Value = "Average Hours" .Range("H4").Value = "Formatted Hours" .Range("i4").Value = "Formatted Average Hours" 'Format Column Headings set the text to the left on A4 so it looks nice, then center the values in the range B7:I3000 so all values are centered .Range("A4:l4").Cells.Font.Bold = True .Range("A4:l4").HorizontalAlignment = XLCENTER .Range("A4:l4").Columns.AutoFit 'iRowStart is the row that the starting row that the recordset will enter data into iRowStart = 7 'Then we Loop through recordset above and copy data from recordset until we get to the end of file Do While Not objRs1.EOF ' 'start importing the data from the record set above into the required columns A,B,C,D,E,F,G in this example .Range("A" & iRowStart).Value = Nz(objRs1![NoteType], "") .Range("A" & iRowStart).Columns.AutoFit .Range("A" & iRowStart).HorizontalAlignment = XLCENTER .Range("A" & iRowStart).ColumnWidth = 15 .Range("B" & iRowStart).Value = Nz(objRs1![Actual Time], "") .Range("B" & iRowStart).Columns.AutoFit .Range("B" & iRowStart).HorizontalAlignment = XLCENTER .Range("B" & iRowStart).ColumnWidth = 18 .Range("C" & iRowStart).Value = Nz(objRs1![Hold Time], 0) .Range("C" & iRowStart).Columns.AutoFit .Range("C" & iRowStart).HorizontalAlignment = XLCENTER .Range("C" & iRowStart).ColumnWidth = 15 .Range("D" & iRowStart).Value = Nz(objRs1![Total Minutes], 0) .Range("D" & iRowStart).Columns.AutoFit .Range("D" & iRowStart).HorizontalAlignment = XLCENTER .Range("D" & iRowStart).ColumnWidth = 20 ' .Range("E" & iRowStart).Value = Nz(objRs1![Number Of Tickets], 0) .Range("E" & iRowStart).Columns.AutoFit .Range("E" & iRowStart).HorizontalAlignment = XLCENTER .Range("E" & iRowStart).ColumnWidth = 15 .Range("F" & iRowStart).Value = Nz(objRs1![Avg Minutes], 0) .Range("F" & iRowStart).Columns.AutoFit .Range("F" & iRowStart).HorizontalAlignment = XLCENTER .Range("F" & iRowStart).ColumnWidth = 20 ' .Range("G" & iRowStart).Value = Nz(objRs1![Average Hours], 0) .Range("G" & iRowStart).Columns.AutoFit .Range("G" & iRowStart).HorizontalAlignment = XLCENTER .Range("G" & iRowStart).ColumnWidth = 20 ' .Range("H" & iRowStart).Value = Nz(objRs1![Formatted Hour], 0) .Range("H" & iRowStart).Columns.AutoFit .Range("H" & iRowStart).HorizontalAlignment = XLCENTER .Range("H" & iRowStart).ColumnWidth = 28 ' .Range("I" & iRowStart).Value = Nz(objRs1![Formatted Average Hours], 0) .Range("I" & iRowStart).Columns.AutoFit .Range("I" & iRowStart).HorizontalAlignment = XLCENTER .Range("I" & iRowStart).ColumnWidth = 20 iRowStart = iRowStart + 1 objRs1.MoveNext Loop 'then we set a footer, we start this two rows below the last entry and leave a gap of 3 rows and format the footer nicely iRowStart = iRowStart + 2 .Range("A" & iRowStart).Value = "All Data Exported All Times Shown Have been Formatted to the following D:H:M" .Range("A" & iRowStart).Font.Color = vbRed .Range("A" & iRowStart).HorizontalAlignment = XLLEFT End With 'now we close the recordset and reclaim the memory used DoCmd.Hourglass False objXlApp.Visible = True objRs1.Close Set objRs1 = Nothing Set objXlApp = Nothing Set objXlBook = Nothing Set objXlSheet = Nothing On Error GoTo 0 Exit Sub End If
I'm using linked tables, could this be the reason?Code:Error 3078 (The Microsoft access database engine cannot find the input table or query 'strSQL'
I'm really sorry to be a pain, the output of the SQL from the immediate window runs when put into a new query
Steve
why have you put quotes around strsql?Set objRs1 = CurrentDb.OpenRecordset("strSql", dbOpenSnapshot)
Hi Ajax
because I am really silly, never noticed this aahhhhh
I removed the quotes and the output now fails with
Code:Error 3061 (too few Parameters.Expected 2)
so I'm guessing I have an issue with the table names?
Steve
suggest copy the code from the immediate window and run it. But it may be your referenced form is not open or the fields not populated. You also seem to have dropped the format function which you will need to convert the dates to US format
Hi
yeah just noticed that after I posted, I have an old copy that I'm playing with and posted the wrong one,
the output of the sql from the immediate window runs correctly when posted in a new query, and the refrence form is open and the fields populated
would I need to add the parameters at the to of the SQL string some thing like
Code:strSql = "PARAMETERS [Forms].[frmITHelpDeskResponceTimeDates].[StartDate] DATE, [Forms].[frmITHelpDeskResponceTimeDates].[EndDate] DATE; " strSql = "SELECT Count(AssetNotes.AssetNotesID) AS CountOfAssetNotesID," strSql = strSql & " AssetNotesType.NoteType, AssetNotesType.ID," strSql = strSql & " Nz(Sum(DateDiff('n',[issueOpenedTime],[NoteEndtime])),0) AS [Actual Time]," strSql = strSql & " Minutes2Duration([actual time]) As [Formatted Time]" strSql = strSql & " FROM AssetNotesType RIGHT JOIN AssetNotes ON AssetNotesType.ID = AssetNotes.NotesType" strSql = strSql & " WHERE AssetNotes.IssueClosed" strSql = strSql & " Between format([forms].[frmITHelpDeskResponceTimeDates].[startdate], ""mm/dd/yyyy"") And" strSql = strSql & " format([forms].[frmITHelpDeskResponceTimeDates].[enddate], ""mm/dd/yyyy"")" strSql = strSql & " GROUP BY AssetNotesType.NoteType, AssetNotesType.ID" strSql = strSql & " HAVING (((AssetNotesType.NoteType) Not Like 'General Note') AND" strSql = strSql & " ((Nz(Sum(DateDiff('n',[issueOpenedTime],[NoteEndtime])),0)) Is Not Null));"
or is it not required as the "Between" part of the code is the criteria
hope this makes sense, and thanks for all the help
Steve
you are creating a string with specific values in the criteria, so you don't need the parameters. The reason is you cannot open a recordset with parameters - hence 'hard coding' the values when you create the string.
for general query design, your notetype criteria should be a where, not a having (which is evaluated after all the data is gathered, then eliminated with 'not like' - so makes for an inefficient query). Your other part re open and end times will also never be null because you are using nz to substitute a 0 if it is null i.e. 0=null - suggest this needs to be <>0 instead.
Also just realised I have not advised the treatment of a string date - you need to surround them with # to tell SQL that whatever is between them should be treated as a date
Code:.... strSql = strSql & " Between #" & format([forms].[frmITHelpDeskResponceTimeDates].[startdate], "mm/dd/yyyy") & "# And" strSql = strSql & " #" & format([forms].[frmITHelpDeskResponceTimeDates].[enddate], "mm/dd/yyyy") & "#" ....
Hi Ajax
Many thanks for getting back,
I'm not in the office for the next few days I have holiday to take before out xmas break so I'm replying from home.
as soon as I'm back in, I will take a look and update you
many thanks for all your help
Steve
Hi Ajax
I have been looking at the query I have, and have amended it which produces the output that I'm looking for
I have been able to successfully create my excel sheet with the formatting I require using it, but without filtering the dates
Code:On Error GoTo ExportAllDataButton_Click_Error Dim objXlApp As Object Dim objXlBook As Object Dim objXlSheet As Object Dim strSql As String Dim objRs1 As DAO.Recordset 'This Is A Object Dim iRowStart As Integer Const XLCENTER = -4108 Const XLLEFT = -4131 'xlRight is -4152 Const XLCELLVALUE = 1 Const XLLESSEQUAL = 8 Const XLEQUAL = 3 ' Const xlGreater = 5 ' Const xlExpression = 2 'This Is The SQL That Creates The Excel Sheet strSql = "SELECT Count(qryITHelpDeskHoldTimeByIssue.CountOfAssetNotesID) AS [Number Of Tickets], AssetNotesType.NoteType, AssetNotesType.ID, Nz(Sum(DateDiff('n',[issueOpenedTime],[NoteEndtime])),0) AS [Actual Time], Minutes2Duration([actual time]) AS [Formatted Time], qryITHelpDeskHoldTimeByIssue.[Hold Time], ([Actual Time]-[Hold Time]) AS [Total Minutes], ([Total Minutes]/[Number Of Tickets]) AS [Avg Minutes], [AVG Minutes] AS [Average Hours], Minutes2Duration([Total Minutes]) AS [Formatted Hour], Minutes2Duration([Average Hours]) AS [Formatted Average Hours]" strSql = strSql & " FROM (AssetNotesType RIGHT JOIN AssetNotes ON AssetNotesType.ID = AssetNotes.NotesType) LEFT JOIN qryITHelpDeskHoldTimeByIssue ON AssetNotesType.ID = qryITHelpDeskHoldTimeByIssue.ID" strSql = strSql & " WHERE (((AssetNotes.[On Hold]) = False))" strSql = strSql & " GROUP BY AssetNotesType.NoteType, AssetNotesType.ID, qryITHelpDeskHoldTimeByIssue.[Hold Time]" strSql = strSql & " HAVING ((AssetNotesType.NoteType) Not Like 'General Note') AND Nz(Sum(DateDiff('n',[issueOpenedTime],[NoteEndtime])),0)" strSql = strSql & " ORDER BY Count(qryITHelpDeskHoldTimeByIssue.CountOfAssetNotesID) DESC;" 'Show The Output in the imediate window Debug.Print strSql 'Execute query and populate recordset Set objRs1 = CurrentDb.OpenRecordset(strSql, dbOpenSnapshot) 'If no data, don't bother opening Excel, just quit If objRs1.RecordCount = 0 Then MsgBox "NO DATA SELECTED FOR EXPORT", vbInformation + vbOKOnly, "NO DATA TO EXPORT" 'DoCmd.Close acForm, "frmITHelpDeskIssuesByAverageTime", acSavePrompt Else 'We Shall Turn On The Hour Glass, So That Users Know That Something Is Happening DoCmd.Hourglass (True) 'Create an instance of Excel and start building a spreadsheet Late Binding Used So No Refrences Required Set objXlApp = CreateObject("Excel.Application") objXlApp.Visible = False Set objXlBook = objXlApp.Workbooks.Add() 'start a new workbook Set objXlSheet = objXlBook.Worksheets(1) With objXlSheet .Name = "IT Support Ticket Closure Times" .Cells.Font.Name = "Franklin Gothic Book" .Cells.Font.Size = 10 ' 'Format range as required To Bold a Cell use this for example .Range("A1").Cells.Font.Bold = True .Range("A1", "H1").Merge .Range("A1").Columns.AutoFit .Range("A2", "H2").Merge .Range("A2").Columns.AutoFit .Range("A1").HorizontalAlignment = XLLEFT .Range("A2").HorizontalAlignment = XLLEFT .Range("A1").Cells.Font.Name = "Franklin Gothic Book" .Range("A2").Cells.Font.Name = "Franklin Gothic Book" .Range("A1").Cells.Font.Size = 12 .Range("A2").Cells.Font.Size = 10 .Range("A1").Value = "IT Support Ticket Closure Times By Ticket Type" .Range("A1").Font.Color = vbBlue .Range("A2").Value = "Exported ON" & " - " & Date .Range("A2").Font.Color = vbBlue 'add header iforming of possible extended resolution times .Range("A4", "H4").Merge .Range("A4").Columns.AutoFit .Range("A4").Cells.Font.Name = "Franklin Gothic Book" .Range("A4").Cells.Font.Size = 10 .Range("A4").Value = "The Total Hours And Avaerage Hours Include Tickets That Were Created Out Of Hours, Support Times Fall Within 8AM - 4PM Monday To Friday" .Range("A4").Font.Color = vbRed 'Now We Shall Build The Colum Headings.value is the text required .Range("A6").Value = "Number Of Tickets" .Range("B6").Value = "Note Type" .Range("C6").Value = "Total Hours" .Range("C7").Value = "Formatted As DD:HH:MM" .Range("D6").Value = "Average Hours" .Range("D7").Value = "Formatted As DD:HH:MM" 'Format Column Headings set the text to the left on A4 so it looks nice, then center the values in the range B7:I3000 so all values are centered .Range("A6:i6").Cells.Font.Bold = True .Range("A6:i6").HorizontalAlignment = XLCENTER .Range("A6:i6").Columns.AutoFit .Range("C7:D7").HorizontalAlignment = XLCENTER .Range("C7:D7").Columns.AutoFit .Range("C7:D7").Font.Color = vbBlue 'iRowStart is the row that the starting row that the recordset will enter data into iRowStart = 9 'Then we Loop through recordset above and copy data from recordset until we get to the end of file Do While Not objRs1.EOF ' 'start importing the data from the record set above into the required columns A,B,C,D,E,F,G in this example .Range("A" & iRowStart).Value = Nz(objRs1![Number Of Tickets], "") .Range("A" & iRowStart).Columns.AutoFit .Range("A" & iRowStart).HorizontalAlignment = XLCENTER .Range("A" & iRowStart).ColumnWidth = 20 .Range("B" & iRowStart).Value = Nz(objRs1![NoteType], "") .Range("B" & iRowStart).Columns.AutoFit .Range("B" & iRowStart).HorizontalAlignment = XLCENTER .Range("B" & iRowStart).ColumnWidth = 30 .Range("C" & iRowStart).Value = Nz(objRs1![Formatted Hour], 0) .Range("C" & iRowStart).Columns.AutoFit .Range("C" & iRowStart).HorizontalAlignment = XLCENTER .Range("C" & iRowStart).ColumnWidth = 30 .Range("D" & iRowStart).Value = Nz(objRs1![Formatted Average Hours], 0) .Range("D" & iRowStart).Columns.AutoFit .Range("D" & iRowStart).HorizontalAlignment = XLCENTER .Range("D" & iRowStart).ColumnWidth = 30 iRowStart = iRowStart + 1 objRs1.MoveNext Loop 'then we set a footer, we start this two rows below the last entry and leave a gap of 3 rows and format the footer nicely iRowStart = iRowStart + 2 .Range("A" & iRowStart).Value = "All Times Shown Have been Formatted to the following D:H:M" .Range("A" & iRowStart).Font.Color = vbRed .Range("A" & iRowStart).HorizontalAlignment = XLLEFT 'place the cursor at the required cell .Range("A3").select 'Now we Save The newly Created ExcelFile To The Newwork objXlSheet.Application.DisplayAlerts = False objXlSheet.SaveAs DLookup("FilePath", "Settings", "ID = 1") & "\IT" & "\Reports" & "\Issues By Average Time" & "\All Ticket Resolution Times.xls" objXlSheet.Application.DisplayAlerts = True 'Now We Open The File We Have Just Saved objXlApp.Visible = True objXlApp.Workbooks.Open DLookup("FilePath", "Settings", "ID = 1") & "\IT" & "\Reports" & "\Issues By Average Time" & "\All Ticket Resolution Times.xls" Set objXlApp = Nothing End With 'now we close the record sets and reclaim any memory held DoCmd.Hourglass False objRs1.Close Set objRs1 = Nothing Set objXlApp = Nothing Set objXlBook = Nothing Set objXlSheet = Nothing 'On Error GoTo 0 Exit Sub End If On Error GoTo 0 Exit Sub ExportAllDataButton_Click_Error: MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure ExportAllDataButton_Click of Sub Form_frmITHelpDeskDashboard" End Sub
when amending my SQL String to
I getCode:'This Is The SQL That Creates The Excel Sheet strSql = "SELECT Count([qryITHelpDeskHoldTimeByIssue].[CountOfAssetNotesID]) AS [Number Of Tickets], [AssetNotesType].[NoteType], [AssetNotesType].[ID], Nz(Sum(DateDiff('n',[issueOpenedTime],[NoteEndtime])),0) AS [Actual Time], Minutes2Duration([actual time]) AS [Formatted Time], [qryITHelpDeskHoldTimeByIssue].[Hold Time], ([Actual Time]-[Hold Time]) AS [Total Minutes], ([Total Minutes]/[Number Of Tickets]) AS [Avg Minutes], [AVG Minutes] AS [Average Hours], Minutes2Duration([Total Minutes]) AS [Formatted Hour], Minutes2Duration([Average Hours]) AS [Formatted Average Hours]" strSql = strSql & " FROM (AssetNotesType RIGHT JOIN AssetNotes ON AssetNotesType.ID = AssetNotes.NotesType) LEFT JOIN qryITHelpDeskHoldTimeByIssue ON AssetNotesType.ID = qryITHelpDeskHoldTimeByIssue.ID" strSql = strSql & " WHERE (((AssetNotes.[On Hold]) = False))" strSql = strSql & " Between #" & Format([Forms].[frmITHelpDeskResponceTimeDates].[StartDate], "mm/dd/yyyy") & "# And" strSql = strSql & " #" & Format([Forms].[frmITHelpDeskResponceTimeDates].[EndDate], "mm/dd/yyyy") & "#" strSql = strSql & " GROUP BY [AssetNotesType].[NoteType], [AssetNotesType].[ID], [qryITHelpDeskHoldTimeByIssue].[Hold Time]" strSql = strSql & " HAVING (([AssetNotesType].[NoteType]) Not Like 'General Note') AND Nz(Sum(DateDiff('n',[issueOpenedTime],[NoteEndtime])),0)" strSql = strSql & " ORDER BY Count([qryITHelpDeskHoldTimeByIssue].[CountOfAssetNotesID]) DESC;"
Error438 (Object doesn't support this property or method)
sorry to be a real pain
Steve
perhaps because excel does not support the nz function?
I Ajax
Thanks for the reply
I have removed the NZ function, tested the query and it works, but now getting
Error 3061 (too few Parameters.Expected 2)
when running the code.
I think I'm going to have to do a lot more research on this, I think I'm trying to punch well above my weight to be honest
many thanks for the help
Steve
perhaps it is something to do with your other query - qryITHelpDeskHoldTimeByIssue
HI Ajax
Yeap your right, it had a NZ function.
will work on that
many thanks for all your help
Happy Xmas and new year
Steve