Results 1 to 4 of 4
  1. #1
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts
    The sql for the record source for the report is
    [codebox]SELECT qryAllDatesAndMembers.MemberID, qryAllDatesAndMembers.MonthYear, qryAllDatesAndMembers.Required, Nz([MeetingsAttended],0) AS MeetingsAttendedCount, nz([Makeups],0) AS MakeupCount, IIf(nz([Makeups],0)>4,4,nz([MakeUps],0)) AS MakeUpCredit, IIf([MakeUpCredit]+nz([MeetingsAttended])>=[Required],"Credit","NoCredit") AS CreditedMonth, qryAllDatesAndMembers.FullName, qryAllDatesAndMembers.LastName, qryAllDatesAndMembers.PreferredName
    FROM (qryAllDatesAndMembers LEFT JOIN qryCountMakeUps ON (qryAllDatesAndMembers.MonthYear = qryCountMakeUps.MeetingMonth) AND (qryAllDatesAndMembers.MemberID = qryCountMakeUps.MemberID)) LEFT JOIN qryCountMeetingsAttended ON (qryAllDatesAndMembers.MonthYear = qryCountMeetingsAttended.MeetingMonth) AND (qryAllDatesAndMembers.MemberID = qryCountMeetingsAttended.MemberID)
    WHERE (((qryAllDatesAndMembers.MonthYear)>=[StartDate] And (qryAllDatesAndMembers.MonthYear)<=[EndDate]))
    ORDER BY qryAllDatesAndMembers.MemberID, qryAllDatesAndMembers.MonthYear DESC , qryAllDatesAndMembers.LastName, qryAllDatesAndMembers.PreferredName;
    [/codebox]

    The StartDate and EndDate are fed from a form, frmPerfectAttendance, "txtStartDate" and "txtEndDate" text box controls.

    On a command button I put the following
    [codebox]
    Dim stDocName As String
    stDocName = "rptFullStatsSelectedDates"
    DoCmd.OpenReport stDocName, acPreview, , "([StartDate] = #" & Forms!frmPerfectAttendance!txtStartDate & "#) AND ([EndDate] = #" & Forms!frmPerfectAttendance!txtEndDate & "#) "[/codebox]

    I also tried
    [codebox]
    DoCmd.OpenReport stDocName, acViewPreview, , "([StartDate] = Format(Forms!frmPerfectAttendance!txtStartDate,'\# mm\\/yyyy\#') AND ([EndDate] = Format(Forms!frmPerfectAttendance!txtEndDate,'\#mm \/yyyy\#')"[/codebox]

    I still get prompted for StartDate and EndDate paremeters.

    Any thoughts?

    Thanks.

    Tom

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='THWatson' post='770638' date='15-Apr-2009 17:54']I still get prompted for StartDate and EndDate paremeters.[/quote]
    Remove the where part of the query.
    Then use this code to open the report :
    Code:
    Dim stDocName As String
    stDocName = "rptFullStatsSelectedDates"
    DoCmd.OpenReport stDocName, acPreview, , "qryAllDatesAndMembers.MonthYear >= #" & _
       Forms!frmPerfectAttendance!txtStartDate & "# AND qryAllDatesAndMembers.MonthYear<=  #" & _ 
       Forms!frmPerfectAttendance!txtEndDate & "#"
    Francois

  3. #3
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='Francois' post='770650' date='15-Apr-2009 17:48']Remove the where part of the query.
    Then use this code to open the report :
    [codebox]SELECT qryAllDatesAndMembers.MemberID, qryAllDatesAndMembers.MonthYear,
    qryAllDatesAndMembers.Required, Nz([MeetingsAttended],0) AS MeetingsAttendedCount,
    nz([Makeups],0) AS MakeupCount, IIf(nz([Makeups],0)>4,4,nz([MakeUps],0)) AS MakeUpCredit,
    IIf([MakeUpCredit]+nz([MeetingsAttended])>=[Required],"Credit","NoCredit") AS CreditedMonth,
    qryAllDatesAndMembers.FullName, qryAllDatesAndMembers.LastName,
    qryAllDatesAndMembers.PreferredName
    FROM (qryAllDatesAndMembers LEFT JOIN qryCountMakeUps ON
    (qryAllDatesAndMembers.MemberID = qryCountMakeUps.MemberID) AND
    (qryAllDatesAndMembers.MonthYear = qryCountMakeUps.MeetingMonth))
    LEFT JOIN qryCountMeetingsAttended ON
    (qryAllDatesAndMembers.MemberID = qryCountMeetingsAttended.MemberID) AND
    (qryAllDatesAndMembers.MonthYear = qryCountMeetingsAttended.MeetingMonth)
    WHERE (((qryAllDatesAndMembers.MonthYear)>=[Forms]![frmPerfectAttendance]![txtStartDate] And
    (qryAllDatesAndMembers.MonthYear)<=[Forms]![frmPerfectAttendance]![txtEndDate]))
    ORDER BY qryAllDatesAndMembers.MemberID, qryAllDatesAndMembers.MonthYear DESC ,
    qryAllDatesAndMembers.LastName, qryAllDatesAndMembers.PreferredName;
    [/codebox]

    And then use this code in the form behind the command button that calls the report:
    [codebox]Dim stDocName As String

    stDocName = "rptFullStatsSelectedDates"
    DoCmd.OpenReport stDocName, acViewPreview
    [/codebox]

    Tom

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    You can simplify

    WHERE (((qryAllDatesAndMembers.MonthYear)>=[Forms]![frmPerfectAttendance]![txtStartDate] And (qryAllDatesAndMembers.MonthYear)<=[Forms]![frmPerfectAttendance]![txtEndDate]))

    to

    WHERE qryAllDatesAndMembers.MonthYear Between [Forms]![frmPerfectAttendance]![txtStartDate] And [Forms]![frmPerfectAttendance]![txtEndDate]

Posting Permissions

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