Filter string error for subreport

[rhavlak]

Hi all,

I am building a report in Access 2003 that consists of several subreports that summarize data in a View I've created in SQL Server 2005 (note I am aware that some compatibility issues exist).

I need to filter the entire report based on input from a popup form that allows the user to specify a start date, end date, and / or Hospital. Any of these left blank are ignored.

I am pretty sure this is working properly for the main report, but am having trouble passing the filter expression to the subreports. Currently I am trying to set the Filter and FilterOn properties of a subreport from the onClick event handler of the view button on the popup form, before opening the main form:

Private Sub view_Click()
Dim strWhere As String
Dim strReportName As String
strReportName = "Donor Summary"
strWhere = "1=1"
Const strcJetDate = "mm\/dd\/yyyy"
If Not IsNull(Me.cboHospital) Then
   'EmpID is numeric
   strWhere = strWhere & " AND Hospital = '" & Me.cboHospital & "'"
End If
If Not IsNull(Me.txtStartDate) Then
   'DateField is a date
   strWhere = strWhere & " And Death_Date>= Convert(smalldatetime, '" & Me.txtStartDate & "')"
End If
If Not IsNull(Me.txtEndDate) Then
   'DateField is a date
   strWhere = strWhere & " And Death_Date<=Convert(smalldatetime, '" & Me.txtEndDate & "')"
End If
[Report_Donor_Summary_Hospital].FilterOn = True
[Report_Donor_Summary_Hospital].Filter = strWhere

DoCmd.OpenReport strReportName, acPreview, , strWhere
End Sub

So the filter expression is like this (without dates) "1=1 AND Hospital = 'Training Hospital 2'" or (with dates specified) "1=1 AND Hospital = 'Training Hospital 2' And Death_Date>= Convert(smalldatetime, '10/19/2008') And Death_Date<=Convert(smalldatetime, '11/20/2009')" or (with nothing specified) "1=1"

With any of these expressions, when I click the form's view button the main report opens and I get two identical error messages that read, "one or more operators in the filter expression is invalid, for a list of valid operators please refer to the help file".

OR sometimes I get a "Runtime error 2021: one or more operators in the filter expression is invalid" break during execution of the form button's event handler at the line where I set FilterOn=True.

Which error appears seems to vary with the following in a cryptic way that I can't seem to understand:
a)whether I have either report open in design view when I click the form button.
b)what filter expression / FilterOn status is hard-coded on the subreport.

Neither report has any associated VBA code.

Can anyone shed any light on this for me? Any suggestions much appreciated.

Thanks

Rick

 

[wiklendt]

here is a date segment from one of my build filters. looks like you're missing a couple of little things, and getting others the wrong way around. compare mine to yours and see if you can make some changes...

i've highlighted two main things that i noticed right away were different in yours:

    Const conDateFormat = "[COLOR=Red]\#[/COLOR]mm\/dd\/yyyy[COLOR=Red]\#[/COLOR]"

    strReport = "rptOrders"
    strField = "[DateOrdered]"
    varWhere = ""
    
    ' ================================================================================
    ' CHECK FOR DATE RANGE
    ' ================================================================================
    If IsNull(Me.cmbDateStart) Then
        If Not IsNull(Me.cmbDateEnd) Then  'End date, but no start.
            varWhere = "(" & strField & " <= " & [COLOR=Red]Format(Me.cmbDateEnd, conDateFormat)[/COLOR] & ") "
        Else
            'neither start nor end dates, do nothing to varWhere
        End If
    Else
        If IsNull(Me.cmbDateEnd) Then  'Start date, but no End.
            varWhere = "(" & strField & " >= " & [COLOR=Red]Format(Me.cmbDateStart, conDateFormat)[/COLOR] & ") "
        Else 'Both start and end dates present.
            varWhere = "(" & strField & " Between " & Format(Me.cmbDateStart, conDateFormat) _
            & " AND " & [COLOR=Red]Format(Me.cmbDateEnd, conDateFormat)[/COLOR] & ")"
        End If
    End If

 

[wiklendt]

why is 1=1 always? what's this do? i find it somewhat weird at a glance...

 

[wiklendt]

also, you can add

Debug.Print strWhere

to your code. when you are developing your filter, you can rem out the report calling and have just the srtWhere thrown up at you (Debug.Print "prints" the string to the "immediate window" in the VBA editor).

and i never recall having to edit/change or even look at the filter settings of a report. in terms of filtering your subreport - if you have your parent/child reports setup correctly, any filters in the main records will 'trickle' to the sub records, assuming you don't need to further filtering on the subreport records...

 

[rhavlak]

The 1=1 at the beginning of the filter is kind of a hack, it just makes sure that the filter strings being appended to strWhere can always start with "AND" regardless of whether they come from the first non-null control. It simplifies the If.. Then structure a bit, at the cost of a more obfuscated filter expression. I think maybe I will switch to doing it your way.

Thanks for the helpful replies. I'm going to work on this for a bit and see if I can make it work with what you've suggested.

 

[wiklendt]

The 1=1 at the beginning of the filter is kind of a hack, it just makes sure that the filter strings being appended to strWhere can always start with "AND" regardless of whether they come from the first non-null control. It simplifies the If.. Then structure a bit, at the cost of a more obfuscated filter expression. I think maybe I will switch to doing it your way.

Thanks for the helpful replies. I'm going to work on this for a bit and see if I can make it work with what you've suggested.

this is something that will test your patience. it took me forever to get my head around how to build a complex filter. you're definitely better off learning how to do it properly anyway

i'll attach my most recent database with this feature - feel free to pick it apart. in particular you may want to look at the code behind the form that opens when you click on the "review orders" button.

there's a front and backend, but you don't have to worry about cnnecting them, there's code that runs to do it automatically when you open the front end (has suffix "_fe" in file name). also, log in as whomever you like - it's not a security thing, it's an audit thing. and there's no sensitive data in there, it's all just dummy data.

have fun!