UtterAccess Discussion Forums > Setting a report filter?

UtterAccess Discussion Forums > Microsoft® Access > Access Reports

houghty1402

Apr 27 2006, 11:38 AM

Could someone please have a look at this code and tell me why it doesn't work?

What the function does is get the ListIndex of a combo box (cboKeyDate), assign a value to a variable (strDateSort and strFilter) depending on the ListIndex, which in turn opens a report (rptProcessReport) with a set filter.

The report opens fine but no records are showing, even though I know that there are records which meet the filter criteria.

Any pointers would be much appreciated!

Cheers

Chris

CODE

Dim stDocName As String
Dim strFilter As String
Dim strDateSort As String

Select Case Me.cboKeyDate.ListIndex
Case 1
strDateSort = "[DateInfoReceived]"
Case 2
strDateSort = "[DateAccsReviewCompleted]"
Case 3
strDateSort = "[DateAccsReturnedToBrookson]"
Case 4
strDateSort = "[DateBrooksonResponseReceived]"
Case 5
strDateSort = "[DateReviewSignedOff]"
Case 6
strDateSort = "[DateAccsSent]"
End Select

If Me.txtDateStart = Me.txtDateEnd Then
strFilter = strDateSort & "=#" & Me.txtDateStart & "#"
Else
strFilter = strDateSort & " BETWEEN #" & Me.txtDateStart & "# AND #" & Me.txtDateEnd & "#"
End If

stDocName = "rptProcessReport"
DoCmd.OpenReport stDocName, acViewPreview

With Reports!rptProcessReport
.Filter = strFilter
.FilterOn = True
End With

fkegley

Apr 27 2006, 11:53 AM

You may want to try opening the report AFTER you set the filter.

houghty1402

Apr 27 2006, 01:21 PM

I need to have the report open to code the filter, so I don't think it's the order of the coding that's causing the problem, rather the filter that is being applied when the code runs.

When I open the report in design view (after setting dates 01/04/06 and 02/04/06 as StartDate and EndDate, and setting the field to filter on as [DateInfoReceived]) the filter being applied reads:

[DateInfoReceived] BETWEEN #01/04/2006# AND #02/04/2006#

This therefore shows that a filter is being applied to the report, although it is not working as planned!

I can't see why this filter is excluding all records - this statement works as SQL code in query design, so I can't see why it won't work as an applied filter

Any other ideas?

Cheers

Chris

fkegley

Apr 27 2006, 04:09 PM

Chris, I goofed. I didn't read your code closely enough. The fact is that the filter is not being applied even after the report is open.

What I have done in the past is open the report in Design View, Hidden window, then set the filter and filter on properties, closed and saved the report, then opened in Print Preview.

houghty1402

Apr 28 2006, 04:08 AM

Hi Frank

I did a bit of research on the net and found a good tutorial here that offered a slightly different approach.

I think the problem lay in the format of the dates that were being put into the filter - these now appear to be working ok.

Cheers for your help

Chris

fkegley

Apr 28 2006, 09:04 AM

Chris, yes, there are a variety of ways to do it. That looks as good or better than the one I was advising you to use. I am glad you found one that works for you.

This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.