Page 2 of 2 FirstFirst 12
Results 16 to 29 of 29
  1. #16
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    • but when run on the form it does not




    see post #11

  2. #17
    sdel_nevo is offline Competent Performer
    Windows 10 Access 2010 32bit
    Join Date
    Apr 2013
    Location
    Gloucester, UK
    Posts
    402
    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

    Code:
    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 did this out of desperation really to see if I had an error in my logic, when this now runs from the form I get

    Code:
    Error 3078 (The Microsoft access database engine cannot find the input table or query 'strSQL'
    I'm using linked tables, could this be the reason?

    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

  3. #18
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    Set objRs1 = CurrentDb.OpenRecordset("strSql", dbOpenSnapshot)
    why have you put quotes around strsql?

  4. #19
    sdel_nevo is offline Competent Performer
    Windows 10 Access 2010 32bit
    Join Date
    Apr 2013
    Location
    Gloucester, UK
    Posts
    402
    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

  5. #20
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    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

  6. #21
    sdel_nevo is offline Competent Performer
    Windows 10 Access 2010 32bit
    Join Date
    Apr 2013
    Location
    Gloucester, UK
    Posts
    402
    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

  7. #22
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    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.

  8. #23
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    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") & "#"
    ....

  9. #24
    sdel_nevo is offline Competent Performer
    Windows 10 Access 2010 32bit
    Join Date
    Apr 2013
    Location
    Gloucester, UK
    Posts
    402
    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

  10. #25
    sdel_nevo is offline Competent Performer
    Windows 10 Access 2010 32bit
    Join Date
    Apr 2013
    Location
    Gloucester, UK
    Posts
    402
    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

    Code:
    '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;"
    I get

    Error438 (Object doesn't support this property or method)

    sorry to be a real pain

    Steve

  11. #26
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    perhaps because excel does not support the nz function?

  12. #27
    sdel_nevo is offline Competent Performer
    Windows 10 Access 2010 32bit
    Join Date
    Apr 2013
    Location
    Gloucester, UK
    Posts
    402
    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

  13. #28
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    perhaps it is something to do with your other query - qryITHelpDeskHoldTimeByIssue

  14. #29
    sdel_nevo is offline Competent Performer
    Windows 10 Access 2010 32bit
    Join Date
    Apr 2013
    Location
    Gloucester, UK
    Posts
    402
    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

Page 2 of 2 FirstFirst 12
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Export query to Excel and apply conditional formatting
    By mcpearce in forum Import/Export Data
    Replies: 4
    Last Post: 04-27-2014, 05:26 PM
  2. Formatting Access Export to Excel
    By derekben in forum Access
    Replies: 1
    Last Post: 07-09-2013, 02:30 PM
  3. Formatting Access Export to Excel - VBA
    By derekben in forum Import/Export Data
    Replies: 2
    Last Post: 07-01-2013, 02:19 PM
  4. Marco: Export to Excel w/o Formatting
    By AKoval in forum Import/Export Data
    Replies: 1
    Last Post: 03-20-2013, 09:14 AM
  5. Excel export formatting
    By tariq nawaz in forum Import/Export Data
    Replies: 0
    Last Post: 09-24-2012, 05:30 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums