Getting Date parameters properly satisfied in report 1

[THWatson]

The sql for the record source for the report is

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;

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

On a command button I put the following

Dim stDocName As String
stDocName = "rptFullStatsSelectedDates"
DoCmd.OpenReport stDocName, acPreview, , "([StartDate] = #" & Forms!frmPerfectAttendance!txtStartDate & "#) AND ([EndDate] =  #" & Forms!frmPerfectAttendance!txtEndDate & "#)  "

I also tried

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

I still get prompted for StartDate and EndDate paremeters.

Any thoughts?

Thanks.

Tom

 

[lameid]

Option 1

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)>=Forms!frmPerfectAttendance!txtStartDate  And (qryAllDatesAndMembers.MonthYear)<=Forms!frmPerfectAttendance!txtEndDate ))
ORDER BY qryAllDatesAndMembers.MemberID, qryAllDatesAndMembers.MonthYear DESC , qryAllDatesAndMembers.LastName, qryAllDatesAndMembers.PreferredName;

Dim stDocName As String
stDocName = "rptFullStatsSelectedDates"
DoCmd.OpenReport stDocName, acPreview

Option 2

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)
ORDER BY qryAllDatesAndMembers.MemberID, qryAllDatesAndMembers.MonthYear DESC , qryAllDatesAndMembers.LastName, qryAllDatesAndMembers.PreferredName;

And your code untouched...

Dim stDocName As String
stDocName = "rptFullStatsSelectedDates"
DoCmd.OpenReport stDocName, acPreview, , "([StartDate] = #" & Forms!frmPerfectAttendance!txtStartDate & "#) AND ([EndDate] =  #" & _ 
Forms!frmPerfectAttendance!txtEndDate & "#)  "

 

[THWatson]

Thanks lameid
The first option works.

The second one still asks for Start and End date parameters.

Tom

 

[lameid]

You didn't use my updated SQL statement in option 2 if it is asking for Start and End Date. I completely removed the where clause. The key is you put the criteria in the SQL OR you put the criteria in the where parameter of the docmd.openreport.

 

[THWatson]

lameid
Well, actually, I did. Unless we're referring to something different.

For the report's record source, I used your second option:

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)
ORDER BY qryAllDatesAndMembers.MemberID, qryAllDatesAndMembers.MonthYear DESC , qryAllDatesAndMembers.LastName, qryAllDatesAndMembers.PreferredName;

And in the form, I used:

Dim stDocName As String
stDocName = "rptFullStatsSelectedDates"
DoCmd.OpenReport stDocName, acPreview, , "([StartDate] = #" & Forms!frmPerfectAttendance!txtStartDate & "#) AND ([EndDate] =  #" & _
Forms!frmPerfectAttendance!txtEndDate & "#)  "

When I tried previously to run it from the form, using the same formulation "([StartDate] = #" & Forms!frmPerfectAttendance!txtStartDate & "#) AND ([EndDate] = #" & _
Forms!frmPerfectAttendance!txtEndDate & "#) "

If I leave this part out of the DoCmd line, I get a gazillion records.

Tom

 

[lameid]

I see... I didn't look closely enough at the code to notice the field name wasn't from the query...

Dim stDocName As String
stDocName = "rptFullStatsSelectedDates"
DoCmd.OpenReport stDocName, acPreview, , "(qryAllDatesAndMembers.MonthYear = #" & _
Forms!frmPerfectAttendance!txtStartDate & "#) AND (qryAllDatesAndMembers.MonthYear =  #" & _
Forms!frmPerfectAttendance!txtEndDate & "#)"

 

[THWatson]

That's an interesting wrinkle. Yields no records at all.

But if the first one works, then I am quite satisfied.

Tom

 

[lameid]

Sorry I need to wake up and smell the where clause in your original base query.... The where condition you pass has to be the right criteria altogether.

Dim stDocName As String
stDocName = "rptFullStatsSelectedDates"
DoCmd.OpenReport stDocName, acPreview, , "(qryAllDatesAndMembers.MonthYear [red]>[/red]= #" & _
Forms!frmPerfectAttendance!txtStartDate & "#) AND (qryAllDatesAndMembers.MonthYear [red]<[/red]= #" & _
Forms!frmPerfectAttendance!txtEndDate & "#)"

I can't believe I got it wrong the second time.

But that one is right.

 

[THWatson]

lameid
Now that you have spent this amount of work on Option 2, I feel duty bound to use it.

Anyway, I like it better because the query isn't hard-wired.

Tom

 

[lameid]

The advantage of option 2 is that you can build the where condition in code and therefore customize it programmatically or use the same report behind different buttons with different where condition's. The disadvantage is that it will be slower. Access cannot keep the query compiled with the criteria using this method. It may even run the query first and then filter it down.

Since I mentioned compiling a query this is how you do it.

Save the query in design view. Run the query. Save it in datasheet view. Compiled queries run faster

Generally, I don't worry about performance in filters this way unless it becomes an issue (the report takes a really long time to run).

 

[THWatson]

I agree with the query not being hard-wired.

Actually, using either approach, the report runs really fast, so not an issue.

I do have another issue. It has to do with something that's puzzling.

If you have time, and are so inclined, take a look at this link on 4Shared. It's a stripped down copy of the database.

http://www.4shared.com/file/98784628/e72dad15/AttendanceV20_-_Copymdb.html

In frmPerfectAttendance, when I press the command button "Preview Perfect In Range" I end up having to press it twice in order to get results to display. The first time I press it, I get no records; the second time is right.
a time period to test is Start Date 10/1/07, and End Date is 9/30/08. There has to be one record during that time frame.

The same thing happens when I press the "Perfect in Range" command button to populate the bottom subform.

Now, I do have code in there so that if the user single clicks the End Date box (which enters today's date) when the command button is pressed if the date in End Date is less than the last day of the month the date automatically updates to the last day of the previous month.

If Me.txtEndDate < (DateSerial(Year(txtEndDate), Month(txtEndDate) + 1, 0)) Then
Me.txtEndDate = DateSerial(Year(txtEndDate), Month(txtEndDate), 0)
Else
Me.txtEndDate = Date
End If

That code shouldn't get in the way of anything, and if I don't use that check no records will ever show when the "Preview Perfect In Range" is pressed.

Tom

 

[THWatson]

lameid
I found the problem. It's the piece of code

If Me.txtEndDate < (DateSerial(Year(txtEndDate), Month(txtEndDate) + 1, 0)) Then
Me.txtEndDate = DateSerial(Year(txtEndDate), Month(txtEndDate), 0)
Else
Me.txtEndDate = Date
End If

that's getting in the way. It's doing some nasty things in there. So if I remove that everything works fine.

I had it in there to keep the user from entering a current date that is less than the last day of the current month. But when you select a date that is several months back the code jumps in and puts it to the last day of the current month.

Tom

 

[lameid]

So you want the last day of the month if it is the current month and not the last day of the month?

If Me.txtEndDate < (DateSerial(Year(txtEndDate), Month(txtEndDate) + 1, 0)) And Me.txtEndDate >= cdate(format(date(),m/1/yyyy)) Then
     Me.txtEndDate = DateSerial(Year(txtEndDate), Month(txtEndDate), 0)
Else
     Me.txtEndDate = Date
End If