Open report with all records that have date within values - date in subform

[Ruegen]

I have a report with subforms displaying bookings. The main report is the school, the subreport/suform is the bookings.



However I only want to see the Schools that have a booking within a date range.

Private Sub btnEndYear_Click()If Not IsNull(Me.txtFromDate) And Not IsNull(Me.txtUntilDate) Then
DoCmd.OpenReport "rptYearLetter", acViewPreview, , "BookingDate >= #" & Me.txtFromDate & "# and BookingDate <= #" & Me.txtUntilDate & "#"
Else
MsgBox "Please enter the between dates"
End If
End Sub

I have this to show BookingDate on the main report but because I can't have that on the main report and it is on the subform - how do I get it to filter/where. Is this possible?

[Ruegen]

Nevermind I used fixed date fields in the query of the report and subreport to the same text boxes and it gives me the result I want (although having it unfixed and dependent on the code would be better because then I can reuse the report for other purposes)

[ItsMe]

Looks like you may have data type variants. Try getting your text box values assigned to a date value outside of your DoCmd statement and then assign it to a string value. Then place your string into the DoCmd. At a glance it looks good. All I can suggest is stepping through the process before you place your string in the DoCmd.

Declare a couple date data types and then have them equal your text boxes. Place those date variables into a declared String. Place the string into the DoCmd.

Now you can step through line by line to see if something is amiss.

[Ruegen]

Only problem is I find now that all the schools are showing within the dates rather than just the ones that have the booking. I tried a where in the query where the school would only show within the dates if it were tied to a bookingid but that still brings up the same result. I don't know how to filter the schools that don't have a booking within those dates...

nuuuvermind, I didn't account for cancelled bookings and hiding those...