Date filter on subreport

[darren via AccessMonster.com]

I need to apply the following type of filter to a subreport. However, this is
proving a lot harder than I anticipated. reading through the forum it appears
that setting criteria on a subreport is not possible and most suggest
applying critieria in the underlying query. That said how can I apply the
following via the query:

If Me.txtFromDate = "*" And Me.txtToDate = "*" Then
stLinkCriteria = stLinkCriteria & ""

ElseIf Me.txtFromDate = "*" And Me.txtToDate <> "*" Then
stLinkCriteria = stLinkCriteria & strField & " <= " & Format(Me.txtToDate,
"\#mm\/dd\/yyyy\#")

ElseIf Me.txtFromDate <> "*" And Me.txtToDate = "*" Then
stLinkCriteria = stLinkCriteria & strField & " >= " & Format(Me.
txtFromDate, "\#mm\/dd\/yyyy\#")

Else:
stLinkCriteria = stLinkCriteria & strField & " Between " & Format(Me.
txtFromDate, "\#mm\/dd\/yyyy\#") & " And " & Format(Me.txtToDate, "\#mm\/dd\
/yyyy\#")

End If

I did try writing a sub to change the sql where statement in the querydef,
but the report has a number of subreports that I need to apply this filter to
and changing the querydef for each seemed to be getting longwinded. Before I
pursue this further any better ideas?

Thanks

 

[Allen Browne]

Don't try to build the criteria for the subreport. Instead, have the
subreport's query read the values from the form.

In the query that feeds your subreport, in the Criteria row under your date
field, enter:
Between [Forms].[Form1].[txtStartDate] And [Forms].[Form1].[txtEndDate]

Assuming the form is left open, the subreport will repeatedly read these
values from the form, each time the subreport is generated in your report.

If you are trying to handle the cases where the text boxes may be left null,
switch the query to SQL View (View menu), and change the WHERE clause so it
looks like this:
WHERE (([Forms].[Form1].[txtStartDate] Is Null)
OR ([Date1] >= [Forms].[Form1].[txtStartDate]))
AND (([Forms].[Form1].[txtEndDate] Is Null)
OR ([Date1] <= [Forms].[Form1].[txtEndDate]))
Note: Replace Date1 with the name of your date field.

If txtStartDate and txtEndDate are unbound text boxes, set the Format
property to Short Date so Access will accept dates only. You don't want
string data such as "*" entered in these date boxes.

 

[darren via AccessMonster.com]

Hi Allen, you got my intentions in one.

I hadn't considered putting the forms date box values into the query and then
placing criteria upon them. As a result I was struggling with handling null
values.

Many Thanks