Results 1 to 6 of 6
  1. #1
    Lounger
    Join Date
    Mar 2011
    Posts
    26
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Access97 - no data in report

    Hello, I'm sure this is a very simple problem but can't figure it out. I have a report (rptByDateRange) which works fine. I now want a new report (rptByDateRange2011) constructed by copying everything from rptByDateRange and changing the names. Start date and End date are set from a form with two instances of the calendar control (ActiveXCtl7 and ActiveXCtl14) and then a Do Report button calls a query and the report. The report fails and the message says "There are no records for the date range selected" and the query comes up empty. The query will deliver the records if I run it and enter the start and end parameters manually.
    I'll put the SQL statements and form code below and hopefully someone can spot something.

    2009 version that works:

    qrydailyRecords is Record Source for frmByDateRange and frmByDateRange2011

    SELECT DISTINCTROW tblDailyRecords.DailyRecordID, tblDailyRecords.Date, tblDailyRecords.EmployeeID, tblDailyRecords.ExtraPay, tblDailyRecords.ExtraPayFor, tblDailyRecords.EIHours, tblDailyRecords.RWS, tblDailyRecords.CampCost, tblDailyRecords.CashAdvance, tblDailyRecords.ChequeAdvance, tblDailyRecords.OtherCosts, tblDailyRecords.Notes, Sum([NoUnits]*[Rate]) AS DayPay, tblDailyRecords.TypeOfWork, tblDailyRecords.ProjectID, tblProjects.ProjectName, tblEmployees.EmployeeName
    FROM tblEmployees INNER JOIN ((tblProjects INNER JOIN tblDailyRecords ON tblProjects.ProjectID = tblDailyRecords.ProjectID) INNER JOIN tblDailyRecordDetails ON tblDailyRecords.DailyRecordID = tblDailyRecordDetails.DailyRecordID) ON tblEmployees.EmployeID = tblDailyRecords.EmployeeID
    GROUP BY tblDailyRecords.DailyRecordID, tblDailyRecords.Date, tblDailyRecords.EmployeeID, tblDailyRecords.ExtraPay, tblDailyRecords.ExtraPayFor, tblDailyRecords.EIHours, tblDailyRecords.RWS, tblDailyRecords.CampCost, tblDailyRecords.CashAdvance, tblDailyRecords.ChequeAdvance, tblDailyRecords.OtherCosts, tblDailyRecords.Notes, tblDailyRecords.TypeOfWork, tblDailyRecords.ProjectID, tblProjects.ProjectName, tblEmployees.EmployeeName
    ORDER BY tblDailyRecords.Date, tblDailyRecords.EmployeeID;

    qryByDateRange:

    SELECT DISTINCTROW tblDailyRecords.DailyRecordID, tblDailyRecords.Date, tblEmployees.EmployeeName, tblDailyRecords.ExtraPay, tblDailyRecords.ExtraPayFor, tblDailyRecords.EIHours, tblDailyRecords.RWS, tblDailyRecords.CampCost, tblDailyRecords.CashAdvance, tblDailyRecords.ChequeAdvance, tblDailyRecords.OtherCosts, tblDailyRecords.Notes, tblDailyRecords.EmployeeID, Sum([NoUnits]*[Rate]) AS DayPay, tblDailyRecords.TypeOfWork, tblProjects.ProjectName, tblDailyRecords.ProjectID, tblDailyRecords.EmployeeID, tblEmployees.EmployeeRookie, tblEmployees.EmployeeStaff
    FROM tblEmployees INNER JOIN ((tblProjects INNER JOIN tblDailyRecords ON tblProjects.ProjectID = tblDailyRecords.ProjectID) INNER JOIN tblDailyRecordDetails ON tblDailyRecords.DailyRecordID = tblDailyRecordDetails.DailyRecordID) ON tblEmployees.EmployeID = tblDailyRecords.EmployeeID
    GROUP BY tblDailyRecords.DailyRecordID, tblDailyRecords.Date, tblEmployees.EmployeeName, tblDailyRecords.ExtraPay, tblDailyRecords.ExtraPayFor, tblDailyRecords.EIHours, tblDailyRecords.RWS, tblDailyRecords.CampCost, tblDailyRecords.CashAdvance, tblDailyRecords.ChequeAdvance, tblDailyRecords.OtherCosts, tblDailyRecords.Notes, tblDailyRecords.EmployeeID, tblDailyRecords.TypeOfWork, tblProjects.ProjectName, tblDailyRecords.ProjectID, tblDailyRecords.EmployeeID, tblEmployees.EmployeeRookie, tblEmployees.EmployeeStaff
    HAVING (((tblDailyRecords.Date) Between [Forms]![frmByDateRange]![StartDate] And [Forms]![frmByDateRange]![EndDate]))
    ORDER BY tblEmployees.EmployeeName;

    Code on frmByDateRange
    Code:
    Option Compare Database
    Option Explicit
    
    Private Sub CloseForm_Click()
    On Error GoTo Err_CloseForm_Click
    
        DoCmd.Close
    
    Exit_CloseForm_Click:
        Exit Sub
    
    Err_CloseForm_Click:
        MsgBox Err.Description
        Resume Exit_CloseForm_Click
        
    End Sub
    
    Private Sub DoReport_Click()
    
    On Error GoTo Err_DoReport_Click
    
        Dim stDocName As String
        Dim rptDocName As String
    
        stDocName = "qryByDateRange"
        rptDocName = "rptByDateRange"
        DoCmd.OpenQuery stDocName, acNormal, acEdit
        DoCmd.OpenReport rptDocName, acViewPreview
        DoCmd.Maximize
        
    Exit_DoReport_Click:
        Exit Sub
    
    Err_DoReport_Click:
        MsgBox Err.Description
        Resume Exit_DoReport_Click
    
    End Sub
    
    'Private Sub DoReport_Enter()
    
    'End Function
    
    Private Sub EndDate_Click()
     
    EndDate.Value = ActiveXCtl14.Value
    
    End Sub
    
    Private Sub Form_Close()
    DoCmd.CloseQuery
    End Sub
    
    Private Sub StartDate_Click()
    
    StartDate.Value = ActiveXCtl7.Value
    
    End Sub
    Code from rptByDateRange
    Code:
    Option Compare Database
    Option Explicit
    
    Private Sub Report_Close()
    DoCmd.Close acQuery, "qryByDateRange"
    End Sub
    
    Private Sub Report_NoData(Cancel As Integer)
    
    ' Display a message if user enters a date for which there are no records,
    ' and don't preview or print report.
    
        Dim strMsg As String, strTitle As String
        Dim intStyle As Integer
        
        strMsg = "There are no records for the date range selected."
        intStyle = vbOKOnly
        strTitle = "No Data for Date Range"
        
        MsgBox strMsg, intStyle, strTitle
        Cancel = True
    
    End Sub
    2011 version that doesn't work: uses same qryDailyRecords

    qryByDateRange2011:

    SELECT DISTINCTROW tblDailyRecords.DailyRecordID, tblDailyRecords.Date, tblEmployees.EmployeeName, tblDailyRecords.ExtraPay, tblDailyRecords.ExtraPayFor, tblDailyRecords.EIHours, tblDailyRecords.RWS, tblDailyRecords.CampCost, tblDailyRecords.CashAdvance, tblDailyRecords.ChequeAdvance, tblDailyRecords.OtherCosts, tblDailyRecords.Notes, tblDailyRecords.EmployeeID, Sum([NoUnits]*[Rate]) AS DayPay, tblDailyRecords.TypeOfWork, tblProjects.ProjectName, tblDailyRecords.ProjectID, tblDailyRecords.EmployeeID, tblEmployees.EmployeeRookie, tblEmployees.EmployeeStaff
    FROM tblEmployees INNER JOIN ((tblProjects INNER JOIN tblDailyRecords ON tblProjects.ProjectID = tblDailyRecords.ProjectID) INNER JOIN tblDailyRecordDetails ON tblDailyRecords.DailyRecordID = tblDailyRecordDetails.DailyRecordID) ON tblEmployees.EmployeID = tblDailyRecords.EmployeeID
    GROUP BY tblDailyRecords.DailyRecordID, tblDailyRecords.Date, tblEmployees.EmployeeName, tblDailyRecords.ExtraPay, tblDailyRecords.ExtraPayFor, tblDailyRecords.EIHours, tblDailyRecords.RWS, tblDailyRecords.CampCost, tblDailyRecords.CashAdvance, tblDailyRecords.ChequeAdvance, tblDailyRecords.OtherCosts, tblDailyRecords.Notes, tblDailyRecords.EmployeeID, tblDailyRecords.TypeOfWork, tblProjects.ProjectName, tblDailyRecords.ProjectID, tblDailyRecords.EmployeeID, tblEmployees.EmployeeRookie, tblEmployees.EmployeeStaff
    HAVING (((tblDailyRecords.Date) Between [Forms]![frmByDateRange2011]![StartDate] And [Forms]![frmByDateRange2011]![EndDate]))
    ORDER BY tblEmployees.EmployeeName;

    Code on frmByDateRange2011
    Code:
    Private Sub StartDate2011_Click()
    
     StartDate2011.Value = ActiveXCtl7.Value
     
    End Sub
    
    Private Sub DoReport2011_Click()
    
    On Error GoTo Err_DoReport2011_Click
    
        Dim stDocName As String
        Dim rptDocName As String
    
        stDocName = "qryByDateRange2011"
        rptDocName = "rptByDateRange2011"
        DoCmd.OpenQuery stDocName, acNormal, acEdit
        DoCmd.OpenReport rptDocName, acViewPreview
        DoCmd.Maximize
        
    Exit_DoReport2011_Click:
        Exit Sub
    
    Err_DoReport2011_Click:
        MsgBox Err.Description
        Resume Exit_DoReport2011_Click
    
    End Sub
    Code from rptByDateRange2011
    Code:
    Option Compare Database
    Option Explicit
    
    Private Sub Report_Close()
    DoCmd.Close acQuery, "qryByDateRange2011"
    End Sub
    
    Private Sub Report_NoData(Cancel As Integer)
    
    ' Display a message if user enters a date for which there are no records,
    ' and don't preview or print report.
    
        Dim strMsg As String, strTitle As String
        Dim intStyle As Integer
        
        strMsg = "There are no records for the date range selected."
        intStyle = vbOKOnly
        strTitle = "No Data for Date Range"
        
        MsgBox strMsg, intStyle, strTitle
        Cancel = True
    
    End Sub
    Hope this is not too wordy and thanks in advance for any help.
    Last edited by WendellB; 2011-11-15 at 11:47. Reason: Reduce white space and use code boxes

  2. #2
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts
    The first question is, did you change the recordsource for the report to "qryByDateRange2011"?

    Other little things, which don't really matter in your problem, I just don't understand why you are doing it. Like why do you run the query before running the Report? And why are you still using Access97?
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  3. #3
    Lounger
    Join Date
    Mar 2011
    Posts
    26
    Thanks
    5
    Thanked 0 Times in 0 Posts
    Thanks Mark, yes the recordsource for the report is "qryByDateRange2011". As to why the query runs before the report - lost in the mists of time - I'm very low skilled in Access and if something works I leave it at that. The number of records seems low enough that inefficiencies don't seem to matter. As to still using Access97, I have 2 legal copies of Office97 and 2 of Office2003 and thought a 97 database would be better across both versions.

  4. #4
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts
    Quote Originally Posted by nattres View Post
    Thanks Mark, yes the recordsource for the report is "qryByDateRange2011". As to why the query runs before the report - lost in the mists of time - I'm very low skilled in Access and if something works I leave it at that. The number of records seems low enough that inefficiencies don't seem to matter. As to still using Access97, I have 2 legal copies of Office97 and 2 of Office2003 and thought a 97 database would be better across both versions.
    If you properly fill-out the date fields in frmByDate2011 but don't click the Print button, and instead go to the database window and run query "qryByDateRange2011", what do you get?

    I'd consider changing this line of your SQL:

    HAVING (((tblDailyRecords.Date) Between [Forms]![frmByDateRange2011]![StartDate] And [Forms]![frmByDateRange2011]![EndDate]))

    To this:

    HAVING tblDailyRecords.Date Between Eval("[Forms]![frmByDateRange2011]![StartDate]") And eval("[Forms]![frmByDateRange2011]![EndDate]")
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  5. #5
    Lounger
    Join Date
    Mar 2011
    Posts
    26
    Thanks
    5
    Thanked 0 Times in 0 Posts
    "If you properly fill-out the date fields in frmByDate2011 but don't click the Print button, and instead go to the database window and run query "qryByDateRange2011", what do you get?"

    When I do this the query returns the same empty result but without the message "There are no records for the date range selected"

    I'll have to return to the Design Master to try the SQL change you suggest. I'm in a spot with no Internet or phone and have to drive to a truck stop for connection.

    Appreciate the help.

    Just to mention - everything is replicated if that makes a difference

    Further edit - tried your SQL statement but no joy - error message changed to "you canceled the previous operation" after using the "make report" button on frmByDateRange2011
    Last edited by nattres; 2011-11-16 at 00:00. Reason: more info

  6. #6
    Lounger
    Join Date
    Mar 2011
    Posts
    26
    Thanks
    5
    Thanked 0 Times in 0 Posts
    As has happened before, asking for help seemed to give a new perspective. I found the problem - it was a mixup on the start date and end date references. Found it by creating a 2012 set of objects and then looking back at the 2011 ones. Again, thanks for your help.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •