Feed parameters from a form to run report

[THWatson]

Using Access 2003

On the Main Menu, one option in an Option Group is to "Get member attendance for the current Fiscal Year." The fiscal year is from October 1 through September 30.

When I click on the option in question, the code that runs is

Case Is = 6
        DoCmd.Close
    DoCmd.OpenForm "frmDateSelector", acNormal
        [Forms]![frmDateSelector]![Text7] = "MEMBERS ATTENDANCE TOTALS for CURRENT Fiscal Year"
        [Forms]![frmDateSelector]![txtEndDate] = DateSerial(Year(Date), 9, 30)
        [Forms]![frmDateSelector]![txtStartDate] = DateSerial(Year(Date) - 1, 10, 1)

As you can see, frmDateSelector is opened and 3 things happen:
1. The start date for the period is plugged into txtStartDate
2. The end date for the period is plugged into txtEndDate
3. The report is identified in [Text7] Select Case statements in code behind the form determine which report to run.

The user then clicks either a Preview command button or a Print command button. When the PREVIEW button is pressed, nothing happens. This is the code behind the Preview button

Case Is = "MEMBERS ATTENDANCE TOTALS for CURRENT Fiscal Year"
    DoCmd.OpenReport "rptTotalAttendanceForPeriodSelected", acViewPreview, , "[FirstOfMeetingDate] Between Forms!frmDateSelector!txtStartDate AND Forms!frmDateSelector!txtEndDate"

Curiously, pressing the PRINT button works fine. The code behind the Print button is

Case Is = "MEMBERS ATTENDANCE TOTALS for CURRENT Fiscal Year"
    DoCmd.OpenReport "rptTotalAttendanceForPeriodSelected", acViewNormal, , "[FirstOfMeetingDate] Between Forms!frmDateSelector!txtStartDate AND Forms!frmDateSelector!txtEndDate"

The SQL behind the query that populates the report is

SELECT qryAttendance.MemberID, qryAttendance.FullName, qryAttendance.TypeOfMeeting, Count(qryAttendance.MemberID) AS CountOfMemberID, First(qryAttendance.MeetingDate) AS FirstOfMeetingDate, IIf(Weekday(DateSerial(Year([MeetingDate]),12,25))=5 And Weekday(DateSerial(Year([MeetingDate]),1,1))=5,50,IIf(Weekday(DateSerial(Year([MeetingDate]),12,25))=5 Or Weekday(DateSerial(Year([MeetingDate]),1,1))=5,51,52)) AS WeekCount, RetThur([Forms]![frmDateSelector]![txtStartDate],[Forms]![frmDateSelector]![txtEndDate]) AS Thursdays, qryAttendance.LastName, qryAttendance.PreferredName
FROM qryAttendance
GROUP BY qryAttendance.MemberID, qryAttendance.FullName, qryAttendance.TypeOfMeeting, IIf(Weekday(DateSerial(Year([MeetingDate]),12,25))=5 And Weekday(DateSerial(Year([MeetingDate]),1,1))=5,50,IIf(Weekday(DateSerial(Year([MeetingDate]),12,25))=5 Or Weekday(DateSerial(Year([MeetingDate]),1,1))=5,51,52)), qryAttendance.LastName, qryAttendance.PreferredName
ORDER BY qryAttendance.TypeOfMeeting DESC;

But this same SQL runs irrespective of whether the PREVIEW or PRINT button is pressed.

Any clues as to what is going on here?

Thanks.

Tom

 

[THWatson]

Well, this is kind of weird.

I decided to make a new frmDateSelector...and everything runs fine from there.

So 'twould appear that somehow, for whatever unknown reason, the Preview button became corrupted.

Tom

 

[TheAceMan1]

How are ya THWatson . . .

I can't directly say why the print button works, but it appears the dates are being recognized and [blue]somehow coerced into date data types.[/blue] In any case try the following:

[blue]   Dim [purple][b]Cri[/b][/purple] As String
   
   '
   'your select case
   '
   
   Case Is = "MEMBERS ATTENDANCE TOTALS for CURRENT Fiscal Year"
    
   [purple][b]Cri[/b][/purple] = "[FirstOfMeetingDate] Between #" & Format(Me!txtStartDate, "yyyy,mm,dd") & "# AND " & _
                                      "#" & Format(Me!txtEndDate, "yyyy,mm,dd") & "#"
   DoCmd.OpenReport "rptTotalAttendanceForPeriodSelected", acViewPreview, , [purple][b]Cri[/b][/purple][/blue]

[blue]Your Thoughts? . . .[/blue]


See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[THWatson]

Hi TheAceMan1
Nice to hear from you!!

The two controls on frmDateSelector, txtStartDate and txtEndDate are populated from the Option button in the Option Group on frmMainMenu, by

Forms!frmDateSelector!txtStartDate = DateSerial(Year(Date) + (Month(Date) <= 9), 10, 1)
        Forms!frmDateSelector!txtEndDate = DateSerial(Year(Date) - (Month(Date) > 10), 9, 30)

On frmDateSelector, the Format for the two controls, txtStartDate and txtEndDate, have the following in their Format property setting
mmmm dd", "yyyy

The PREVIEW button is working fine now, once that I rebuilt the form. The curious thing is that it worked fine all along until yesterday evening...and then, all of a sudden, she went south on me.

Do you think that it's okay the way I have it, or do you still think it preferable to do it the way you suggest?

Best regards.

Tom