Page 1 of 2 12 LastLast
Results 1 to 15 of 29
  1. #1
    sdel_nevo is offline Competent Performer
    Windows 10 Access 2010 32bit
    Join Date
    Apr 2013
    Location
    Gloucester, UK
    Posts
    402

    Export To Excel with formatting issue

    Hi Guys

    I have this code that runs on a button click that exports a query to excel with formatting

    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
        
       
        Dim objExcel As Object
        On Error Resume Next
        Set objExcel = GetObject(, "Excel.Application")
        If Err.Number <> 0 Then
            ' No Outlook is not open, try and create object
            Err.Clear
            Set objExcel = CreateObject("Excel.Application")
            If Err.Number <> 0 Then
                MsgBox "Excel Is Either Not Installed or Is Unavailable You Can Not Export to Excel"
                Err.Clear
                Exit Sub
            End If
        End If
      
        strSql = "SELECT AssetNotesType.NoteType, qryITHelpDeskResponceTimeByIssue.[Actual Time], qryITHelpDeskHoldTimeByIssue.[Hold Time] AS HoldTime, [Actual Time]-[HoldTime]) AS [Total Minutes], " & _
            "qryITHelpDeskResponceTimeByIssue.CountOfAssetNotesID AS [Number Of Tickets], ([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], " & _
            "FROM (AssetNotesType LEFT JOIN qryITHelpDeskHoldTimeByIssue ON AssetNotesType.ID = qryITHelpDeskHoldTimeByIssue.ID) , " & _
            "RIGHT JOIN qryITHelpDeskResponceTimeByIssue ON AssetNotesType.ID = qryITHelpDeskResponceTimeByIssue.ID, " & _
            "ORDER BY qryITHelpDeskResponceTimeByIssue.CountOfAssetNotesID DESC"
    
        '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 exported"
            Exit Sub
        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 References 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 = "Asset 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 Hours], 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
            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
    when I run the code, it fires this code

    Code:
     '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 exported"
            Exit Sub
        Else

    if I export the data to exel with this command

    Code:
    DoCmd.OutputTo acOutputQuery, "qryITHelpDeskTicketResolutionTimes", acFormatXLS, DLookup("FilePath", "Settings", "ID = 1") & "\IT" & "\Reports" & "\Issues By Average Time" & "\" & Me.DateRangetxtBox & ".xls"
        '    Application.FollowHyperlink (DLookup("FilePath", "Settings", "ID = 1")) & "\IT" & "\Reports" & "\Issues By Average Time" & "\" & Me.DateRangetxtBox & ".xls"
    it works correctly



    both query's are the same I'm a bit confused as to what I have missed

    any help will be great

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    Dont loop thru the recordset (too slow)
    either:
    export using TRANSFERSPREADSHEET, then open it and format the data
    or
    use your code to paste the data in using COPYFROMRECORDSET rst

  3. #3
    sdel_nevo is offline Competent Performer
    Windows 10 Access 2010 32bit
    Join Date
    Apr 2013
    Location
    Gloucester, UK
    Posts
    402
    Hi Ranman256

    That's Great, will have a go at this

    many thanks

    Steve

  4. #4
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    In the code, you have errors in the SQL. I don't like to use the line continuation character, so I reformatted it and found some errors.
    Code:
              strSql = "SELECT AssetNotesType.NoteType, qryITHelpDeskResponceTimeByIssue.[Actual Time],"
    strSql  & strSql = " qryITHelpDeskHoldTimeByIssue.[Hold Time] AS HoldTime, [Actual Time]-[HoldTime]) AS [Total Minutes]," 
    strSql  & strSql = " qryITHelpDeskResponceTimeByIssue.CountOfAssetNotesID AS [Number Of Tickets],"
    strSql  & strSql = " ([Total Minutes]/[Number Of Tickets]) AS [Avg Minutes], [AVG Minutes] AS [Average Hours],"
    strSql  & strSql = " Minutes2Duration([Total Minutes]) AS [Formatted Hour], Minutes2Duration([Average Hours]) AS ,"  '<< missing the alias - looks like it is on the next line
    strSql  & strSql = " [Formatted Average Hours],"   '<< shouldn't have a comma
    strSql  & strSql = " FROM (AssetNotesType LEFT JOIN qryITHelpDeskHoldTimeByIssue ON AssetNotesType.ID = qryITHelpDeskHoldTimeByIssue.ID),"   '<< shouldn't have ending comma
    strSql  & strSql = " RIGHT JOIN qryITHelpDeskResponceTimeByIssue ON AssetNotesType.ID = qryITHelpDeskResponceTimeByIssue.ID,"   '<< shouldn't have ending comma
    strSql  & strSql = " ORDER BY qryITHelpDeskResponceTimeByIssue.CountOfAssetNotesID DESC"
    Debug.Print strSql
    
    Set objRs1 = CurrentDb.OpenRecordset(strSql, dbOpenSnapshot)
    I would suggest executing the code, setting a break on the "Set objRs1...." line, copying the SQL from the immediate window, creating a new query and pasting in the copied SQL. Execute the query to see if it returns any records.

  5. #5
    sdel_nevo is offline Competent Performer
    Windows 10 Access 2010 32bit
    Join Date
    Apr 2013
    Location
    Gloucester, UK
    Posts
    402
    Hi ssanfu

    sorry for the late reply, I have followed your advice. placed a break point in the code then copied the SQL from the immediate window into a new query and I get

    "The Select Statement Includes a reserved word or argument name that is misspelled or missing, or the punctuation is in correct"


    the query is the same as the query called "qryITHelpDeskTicketResolutionTimes" which works when running the code below

    Code:
    DoCmd.OutputTo acOutputQuery, "qryITHelpDeskTicketResolutionTimes", acFormatXLS, DLookup("FilePath", "Settings", "ID = 1") & "\IT" & "\Reports" & "\Issues By Average Time" & "\" & Me.DateRangetxtBox & ".xls"
    Application.FollowHyperlink (DLookup("FilePath", "Settings", "ID = 1")) & "\IT" & "\Reports" & "\Issues By Average Time" & "\" & Me.DateRangetxtBox & ".xls"''    DoCmd.OutputTo acOutputQuery, "qryITHelpDeskTicketResolutionTimes", acFormatXLS, DLookup

    This is the SQL view of "qryITHelpDeskTicketResolutionTimes"

    Code:
    SELECT AssetNotesType.NoteType, qryITHelpDeskResponceTimeByIssue.[Actual Time], qryITHelpDeskHoldTimeByIssue.[Hold Time] AS HoldTime, ([Actual Time]-[HoldTime]) AS [Total Minutes], qryITHelpDeskResponceTimeByIssue.CountOfAssetNotesID AS [Number Of Tickets], ([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]
    FROM (AssetNotesType LEFT JOIN qryITHelpDeskHoldTimeByIssue ON AssetNotesType.ID = qryITHelpDeskHoldTimeByIssue.ID) RIGHT JOIN qryITHelpDeskResponceTimeByIssue ON AssetNotesType.ID = qryITHelpDeskResponceTimeByIssue.ID
    ORDER BY qryITHelpDeskResponceTimeByIssue.CountOfAssetNotesID DESC;
    qryITHelpDeskTicketResolutionTimes includes a query called "qryITHelpDeskResponceTimeByIssue" this query filters the results between dates

    the SQL for this query is

    Code:
    SELECT Count(AssetNotes.AssetNotesID) AS CountOfAssetNotesID, AssetNotesType.NoteType, AssetNotesType.ID, Nz(Sum(DateDiff("n",[issueOpenedTime],[NoteEndtime])),0) AS [Actual Time], Minutes2Duration([actual time]) AS [Formatted Time]
    FROM AssetNotesType RIGHT JOIN AssetNotes ON AssetNotesType.ID = AssetNotes.NotesType
    WHERE (((AssetNotes.IssueClosed) Between [forms].[frmITHelpDeskResponceTimeDates].[startdate] And [forms].[frmITHelpDeskResponceTimeDates].[enddate]))
    GROUP BY AssetNotesType.NoteType, AssetNotesType.ID
    HAVING (((AssetNotesType.NoteType) Not Like "General Note") AND ((Nz(Sum(DateDiff("n",[issueOpenedTime],[NoteEndtime])),0)) Is Not Null));

    when I run either query from the query designer they run correctly after entering dates in the parameters window

    but get the error above when copying the output of the immediate window into a new query

    I'm guessing the issue is with the parameters for the dates, but I'm alittle stuck to be honest as to weather this is a red hearing or not

    many thanks for your help

    Steve

  6. #6
    sdel_nevo is offline Competent Performer
    Windows 10 Access 2010 32bit
    Join Date
    Apr 2013
    Location
    Gloucester, UK
    Posts
    402
    Hi

    I have managed to resolve the issue I had with the error
    "The Select Statement Includes a reserved word or argument name that is misspelled or missing, or the punctuation is in correct"


    this is the code that I now have in my form

    Code:
    strSql = "SELECT AssetNotesType.NoteType, qryITHelpDeskResponceTimeByIssue.[Actual Time], qryITHelpDeskHoldTimeByIssue.[Hold Time] AS HoldTime, ([Actual Time]-[HoldTime]) AS [Total Minutes], qryITHelpDeskResponceTimeByIssue.CountOfAssetNotesID AS [Number Of Tickets], ([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]" & _
    "FROM (AssetNotesType LEFT JOIN qryITHelpDeskHoldTimeByIssue ON AssetNotesType.ID = qryITHelpDeskHoldTimeByIssue.ID) RIGHT JOIN qryITHelpDeskResponceTimeByIssue ON AssetNotesType.ID = qryITHelpDeskResponceTimeByIssue.ID " & _
    "ORDER BY qryITHelpDeskResponceTimeByIssue.CountOfAssetNotesID DESC;"
    when I copy the output from the immediate window into a new query and I get the query below


    Code:
    SELECT AssetNotesType.NoteType, qryITHelpDeskResponceTimeByIssue.[Actual Time], qryITHelpDeskHoldTimeByIssue.[Hold Time] AS HoldTime, ([Actual Time]-[HoldTime]) AS [Total Minutes], qryITHelpDeskResponceTimeByIssue.CountOfAssetNotesID AS [Number Of Tickets], ([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]FROM (AssetNotesType LEFT JOIN qryITHelpDeskHoldTimeByIssue ON AssetNotesType.ID = qryITHelpDeskHoldTimeByIssue.ID) RIGHT JOIN qryITHelpDeskResponceTimeByIssue ON AssetNotesType.ID = qryITHelpDeskResponceTimeByIssue.ID ORDER BY qryITHelpDeskResponceTimeByIssue.CountOfAssetNotesID DESC;
    when I run this I get no errors and am asked to enter the dates, when I do this the correct data is returned

    when I remove the breakpoint from the code, the code always returns this

    Code:
    '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 exported"
            Exit Sub
        Else
    I am sure the issue is with the dates, but I'm do not know / understand how to get around this

    many thanks

    Steve

  7. #7
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    when I run this I get no errors and am asked to enter the dates, when I do this the correct data is returned
    Great!
    So now the dates. What queries are dates needed? From what you have provided, it appears only the query "qryITHelpDeskTicketResolutionTimes" gets dates from a form named "frmITHelpDeskResponceTimeDates".

    Try this:
    Open the form "frmITHelpDeskResponceTimeDates" and enter dates into the two text box controls "startdate" and "enddate". (these are the names of the controls?)
    Then, in a standard module, create a sub to test the query and dates.
    Code:
    Option Compare Database
    Option Explicit
    
    Private Sub TestDates()
        Dim strSQL As String
    
        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 [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));"
    Debug.Print strSQL
    
    End Sub
    Execute the code. Look at the SQL in the immediate window. What does the WHERE clause look like?
    Post the SQL from the immediate window.


    I question the HAVING clause.
    Code:
    HAVING (((AssetNotesType.NoteType) Not Like "General Note") AND ((Nz(Sum(DateDiff("n",[issueOpenedTime],[NoteEndtime])),0)) Is Not Null));
    Because of the NZ function, you should never see a null value, so the IS NOT NULL will never tbe true.

  8. #8
    sdel_nevo is offline Competent Performer
    Windows 10 Access 2010 32bit
    Join Date
    Apr 2013
    Location
    Gloucester, UK
    Posts
    402
    Hi ssanfu

    Many thanks for the reply, and I'm sorry its been a while in getting back in touch.

    yes you are correct regarding this

    So now the dates. What queries are dates needed? From what you have provided, it appears only the query "qryITHelpDeskTicketResolutionTimes" gets dates from a form named "frmITHelpDeskResponceTimeDates".
    I have amemded the code to use this as advised

    Code:
     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 [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 entered the dates into the start and end date on the form "frmITHelpDeskResponceTimeDates"


    and then copied the SQL in the immediate window into a new query and it runs correctly and shows the results as expected

    this is the SQL from the immediate window

    Code:
    SELECT Count(AssetNotes.AssetNotesID) AS CountOfAssetNotesID, AssetNotesType.NoteType, AssetNotesType.ID, Nz(Sum(DateDiff('n',[issueOpenedTime],[NoteEndtime])),0) AS [Actual Time], Minutes2Duration([actual time]) As [Formatted Time] FROM AssetNotesType RIGHT JOIN AssetNotes ON AssetNotesType.ID = AssetNotes.NotesType WHERE (((AssetNotes.IssueClosed) Between [forms].[frmITHelpDeskResponceTimeDates].[startdate] And [forms].[frmITHelpDeskResponceTimeDates].[enddate])) GROUP BY AssetNotesType.NoteType, AssetNotesType.ID HAVING (((AssetNotesType.NoteType) Not Like 'General Note') AND ((Nz(Sum(DateDiff('n',[issueOpenedTime],[NoteEndtime])),0)) Is Not Null));
    but when run from the form it still fails at

    Code:
    '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 exported"
            Exit Sub
        Else
    I have amended this line,
    Code:
    HAVING (((AssetNotesType.NoteType) Not Like "General Note") AND ((Nz(Sum(DateDiff("n",[issueOpenedTime],[NoteEndtime])),0)) Is Not Null));
    as your correct "is not null will" never be true thanks for that pointer

    Steve

    Many thanks

    Steve

  9. #9
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    I think your problem is here

    Code:
    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 exported"
            Exit Sub
        Else
    recordcount will return 0 until the recordset has been fully loaded. Usual way round it is to use movelast to force the full load before testing the recordcount but instead try


    if objRs1.eof then


    EDIT: see note 4 on this link http://www.allenbrowne.com/ser-29.html, also note 3 re using movelast.

  10. #10
    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 the reply
    I get the same issue when changing
    Code:
    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 exported"
            Exit Sub
        Else
    to

    Code:
    Set objRs1 = CurrentDb.OpenRecordset(strSql, dbOpenSnapshot)
        'If no data, don't bother opening Excel, just quit
        If objRs1.eof Then
            MsgBox "No data selected for export", vbInformation + vbOKOnly, "No data exported"
            Exit Sub
        Else
    Steve

  11. #11
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    in that case it is because your query is using parameters.

    modify your code

    Code:
    strSQL = strSQL & " WHERE (((AssetNotes.IssueClosed)"
    strSQL = strSQL & " Between [forms].[frmITHelpDeskResponceTimeDates].[startdate] And"
        strSQL = strSQL & " [forms].[frmITHelpDeskResponceTimeDates].[enddate]))"
    to

    Code:
    strSQL = strSQL & " WHERE AssetNotes.IssueClosed"
    strSQL = strSQL & " Between  " & [forms].[frmITHelpDeskResponceTimeDates].[startdate] & " And"
        strSQL = strSQL & " " & [forms].[frmITHelpDeskResponceTimeDates].[enddate]

  12. #12
    sdel_nevo is offline Competent Performer
    Windows 10 Access 2010 32bit
    Join Date
    Apr 2013
    Location
    Gloucester, UK
    Posts
    402
    I Ajax

    I have modified the code to this

    Code:
     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  " & [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));"
    but msgbox fires off

    Code:
    Set objRs1 = CurrentDb.OpenRecordset(strSql, dbOpenSnapshot)
        'If no data, don't bother opening Excel, just quit
        If objRs1.eof Then
            MsgBox "No data selected for export", vbInformation + vbOKOnly, "No data exported"
            Exit Sub
        Else


    Steve

  13. #13
    sdel_nevo is offline Competent Performer
    Windows 10 Access 2010 32bit
    Join Date
    Apr 2013
    Location
    Gloucester, UK
    Posts
    402
    Hi

    I have added this code to try to count the records

    Code:
        Set objRs1 = CurrentDb.OpenRecordset("strSql", dbOpenSnapshot)
    
        Dim RecCount As Long
        objRs1.MoveLast 'required to do a MoveLast before getting the RecordCount?
        RecCount = objRs1.RecordCount
        MsgBox RecCount
    
    
        'If no data, don't bother opening Excel, just quit
        If objRs1.eof ThenThen
             MsgBox "NO DATA SELECTED FOR EXPORT", vbInformation + vbOKOnly, "NO DATA TO EXPORT"
            'DoCmd.Close acForm, "frmITHelpDeskIssuesByAverageTime", acSavePrompt
        Else
    but this is always returning 0

    Hmm now I'm confused

    many thanks

    steve

  14. #14
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    one other thing - you are in the UK but date comparisons need to be the US format of mm/dd/yyyy.

    so add the format function to both dates

    format([forms].[frmITHelpDeskResponceTimeDates].[startdate],"mm/dd/yyyy")

    If that still does not work, put the line

    debug.print sqlstr

    just before your openrecordset line.

    It will put the string you have created in the immediate window. Copy and paste this to a new query and run it

  15. #15
    sdel_nevo is offline Competent Performer
    Windows 10 Access 2010 32bit
    Join Date
    Apr 2013
    Location
    Gloucester, UK
    Posts
    402
    Hi Ajax


    I Have amended the SQL string to this
    Code:
        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));"
    when I copy the output of Debug.Print strSql into a new query I get this

    Code:
    SELECT Count(AssetNotes.AssetNotesID) AS CountOfAssetNotesID, AssetNotesType.NoteType, AssetNotesType.ID, Nz(Sum(DateDiff('n',[issueOpenedTime],[NoteEndtime])),0) AS [Actual Time], Minutes2Duration([actual time]) As [Formatted Time] FROM AssetNotesType RIGHT JOIN AssetNotes ON AssetNotesType.ID = AssetNotes.NotesType WHERE AssetNotes.IssueClosed Between format([forms].[frmITHelpDeskResponceTimeDates].[startdate],  "mm/dd/yyyy") And format([forms].[frmITHelpDeskResponceTimeDates].[enddate],  "mm/dd/yyyy") GROUP BY AssetNotesType.NoteType, AssetNotesType.ID HAVING (((AssetNotesType.NoteType) Not Like 'General Note') AND ((Nz(Sum(DateDiff('n',[issueOpenedTime],[NoteEndtime])),0)) Is Not Null));
    which when run works

    but when run on the form it does not

    Many thanks

    Steve

Page 1 of 2 12 LastLast
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