Report, multiple filters problem (1 Viewer)

niloc77

New member
Local time
Today, 15:33
Joined
Feb 28, 2012
Messages
5
I have a report that uses a date filter (vba) to generate a report. The report also contains a separate filter within the report property sheet and the Filter On Load is set to "Yes". my problem is the vba filter is overriding the Filter On Load.

Any idea or help on how to fix this.
Thanks in advance
 

boblarson

Smeghead
Local time
Today, 07:33
Joined
Jan 12, 2001
Messages
32,059
Any idea or help on how to fix this.
Thanks in advance
Get rid of the saved filter. Set the filter completely when opening the report by using the WHERE clause instead.
 

niloc77

New member
Local time
Today, 15:33
Joined
Feb 28, 2012
Messages
5
Thanks Bob,

I'm a bit of a novice i'm afraid, I need to add the following filter to the code below.

Filter...

[Attendance Type]="FU" And [Clinical Diagnosis]>"" Or [Attendance Type]="FU+PROC" And [Clinical Diagnosis]>""

Date filter vba.....

Private Sub DateReportDiagFU_Click()
On Error GoTo Err_Handler
'Purpose: Filter a report to a date range.
'Note: Filter uses "less than the next day" in case the field has a time component.
Dim strReport As String
Dim strDateField As String
Dim strWhere As String
Dim lngView As Long
Const strcJetDate = "\#mm\/dd\/yyyy\#" 'Do NOT change this format.

'DO set the values in the next 3 lines.
strReport = "Clinical Diagnosis FU" 'Put your report name in these quotes.
strDateField = "[Date of GP Referral]" 'Put your field name in the square brackets in these quotes.
lngView = acViewPreview 'Use acViewNormal to print instead of preview.

'Build the filter string.
If IsDate(Me.txtStartDate) Then
strWhere = "(" & strDateField & " >= " & Format(Me.txtStartDate, strcJetDate) & ")"
End If
If IsDate(Me.txtEndDate) Then
If strWhere <> vbNullString Then
strWhere = strWhere & " AND "
End If
strWhere = strWhere & "(" & strDateField & " < " & Format(Me.txtEndDate + 1, strcJetDate) & ")"
End If

'Close the report if already open: otherwise it won't filter properly.
If CurrentProject.AllReports(strReport).IsLoaded Then
DoCmd.Close acReport, strReport
End If

'Open the report.
'Debug.Print strWhere 'Remove the single quote from the start of this line for debugging purposes.
DoCmd.OpenReport strReport, lngView, , strWhere
Exit_Handler:
Exit Sub
Err_Handler:
If Err.Number <> 2501 Then
MsgBox "Error " & Err.Number & ": " & Err.Description, vbExclamation, "Cannot open report"
End If
Resume Exit_Handler
End Sub

Any suggestions would be appreciated.
Thanks
Colin
 

boblarson

Smeghead
Local time
Today, 07:33
Joined
Jan 12, 2001
Messages
32,059
Change your code to this:

Code:
Private Sub DateReportDiagFU_Click()
    On Error GoTo Err_Handler
    'Purpose: Filter a report to a date range.
    'Note: Filter uses "less than the next day" in case the field has a time component.
    Dim strReport As String
    Dim strDateField As String
    Dim strWhere As String
    Dim lngView As Long
    Const strcJetDate = "\#mm\/dd\/yyyy\#"    'Do NOT change this format.

    'DO set the values in the next 3 lines.
    strReport = "Clinical Diagnosis FU"    'Put your report name in these quotes.
    strDateField = "[Date of GP Referral]"    'Put your field name in the square brackets in these quotes.
    lngView = acViewPreview    'Use acViewNormal to print instead of preview.

    'Build the filter string.
    If IsDate(Me.txtStartDate) Then
        strWhere = "(" & strDateField & " >= " & Format(Me.txtStartDate, strcJetDate) & ")"
    End If
    If IsDate(Me.txtEndDate) Then
        If strWhere <> vbNullString Then
            strWhere = strWhere & " AND "
        End If
        strWhere = strWhere & "(" & strDateField & " < " & Format(Me.txtEndDate + 1, strcJetDate) & ")"
    End If
    
[B][COLOR="Red"]    

If strWhere <> vbNullString then 
   strWhere = strWhere & " AND " 
End If

strWhere = strWhere & " ([Attendance Type]='FU' And " & _
    "[Clinical Diagnosis] <> " & vbNullString & ") Or ([Attendance Type]='FU+PROC' " & _
    "And [Clinical Diagnosis] <>" & vbNullString & ")"
[/COLOR][/B]
    'Close the report if already open: otherwise it won't filter properly.
    If CurrentProject.AllReports(strReport).IsLoaded Then
        DoCmd.Close acReport, strReport
    End If

    'Open the report.
    'Debug.Print strWhere 'Remove the single quote from the start of this line for debugging purposes.
    DoCmd.OpenReport strReport, lngView, , strWhere
Exit_Handler:
    Exit Sub
Err_Handler:
    If Err.Number <> 2501 Then
        MsgBox "Error " & Err.Number & ": " & Err.Description, vbExclamation, "Cannot open report"
    End If
    Resume Exit_Handler
End Sub
 
Last edited:

niloc77

New member
Local time
Today, 15:33
Joined
Feb 28, 2012
Messages
5
Hi Bob,

Tried that and get the following...

Error 3075: Extra ) in query expression '(Date of GP Referral]>=#01/01/2012#) AND ([Date of GP Referral]<#03/02/2012#) AND ([Attendance Type]='FU' And [Clinical Diagnosis]<> ) Or ([Attendance Type]='FU+PROC' And [Clinical Diagnosis]<>)'.

Thanks
Colin
 

boblarson

Smeghead
Local time
Today, 07:33
Joined
Jan 12, 2001
Messages
32,059
Try changing to this:
Code:
If strWhere <> vbNullString Then
   strWhere = strWhere & " AND "
End If

strWhere = strWhere & " ([Attendance Type]='FU' And " & _
    "[Clinical Diagnosis] <> '') Or ([Attendance Type]='FU+PROC' " & _
    "And [Clinical Diagnosis] <> '')"
 

niloc77

New member
Local time
Today, 15:33
Joined
Feb 28, 2012
Messages
5
Hi Bob,

That seems to have done the trick, I will give it a good run through tommorow also.

Once again, thank you very much and thanks for sparing me the time with my issue.

Kind regards
Colin
 

niloc77

New member
Local time
Today, 15:33
Joined
Feb 28, 2012
Messages
5
Hi Bob,

The filter seems ok but I seem to have an issue with the code for the date.

I have modified to the following, previous was dd\/mm (wrong order)
Const strcJetDate = "\#mm\/dd\/yyyy\#"

But still gives me a false result.
Do you think the date code is ok
Thanks
Colin
 

boblarson

Smeghead
Local time
Today, 07:33
Joined
Jan 12, 2001
Messages
32,059
I don't see anything wrong with what you have for the date field. Are you sure it is a date/time field and not saved as text?
 

Mikki

Mikki
Local time
Today, 10:33
Joined
Jun 11, 2008
Messages
25
Good Day Bob~
Can this code be used with a parent report and a child report if you form has one start date for parent and one for child?
Please advise.
Thanks in advance~mjc
 

Users who are viewing this thread

Top Bottom