Filter of Report

[Guest]

I am using a "Martin Green" template and have tried to customize it for
myself. There is something I have changed with the dates or something that
has made it not filter properly. I was not sure on the format for sending
dates in the filter in a report. I would like the form to open the report
then apply the filter. I tried docmd.openreport (rptFindings,acPreview)
without luck. Thanks for any suggestions in advance!


Private Sub Cancel_Click()
On Error Resume Next
' Switch the filter off
Reports![rptFindings].FilterOn = False
End Sub
Private Sub OK_Click()
Dim beginnningdate As String
Dim EndingDate As String
Dim strfindtech As String
Dim strfindscribe As String
Dim strfindexamdate As String
Dim strfindauditmonth As String
Dim strfindpatname As String
Dim strfindenteredby As String

strbeginningdate = ">=#" & Me.beginningdate.Value & "#"
strEndingDate = "<=#" & Me.EndingDate.Value & "#"

' Check that the report is open
If SysCmd(acSysCmdGetObjectState, acReport, "rptFindings") <>
acObjStateOpen Then
MsgBox "You must open the report first."
Exit Sub
End If
' Build criteria string for Technician Name field
If IsNull(Me.findtech.Value) Then
strfindtech = "Like '*'"
Else
strfindtech = "='" & Me.findtech.Value & "'"
End If
' Build criteria string for Scribe Name field
If IsNull(Me.findscribe.Value) Then
strfindscribe = "Like '*'"
Else
strfindscribe = "='" & Me.findscribe.Value & "'"
End If
' Build criteria string for Examdate field
If IsNull(Me.findexamdate.Value) Then
strfindexamdate = "Like '*'"
Else
strfindexamdate = "=#" & Me.findexamdate.Value & "#"
End If
' Build criteria string for Auditmonth field
If IsNull(Me.findauditmonth.Value) Then
strfindauditmonth = "Like '*'"
Else
strfindauditmonth = "=#" & Me.findauditmonth.Value & "#"
End If
' Build criteria string for Patient Name field
If IsNull(Me.findpatname.Value) Then
strfindpatname = "Like '*'"
Else
strfindpatname = "='" & Me.findpatname.Value & "'"
End If
' Build criteria string for Enteredby field
If IsNull(Me.findenteredby.Value) Then
strfindenteredby = "Like '*'"
Else
strfindenteredby = "='" & Me.findenteredby.Value & "'"
End If
' Combine criteria strings into a WHERE clause for the filter
strFilter = "[DateofEntry] " & strbeginningdate & " AND [Dateofentry] " &
strEndingDate & " AND [Technician Name] " & strfindtech & " AND [Scribe Name]
" & strfindscribe & " AND [Examdate] " & strfindexamdate & " AND [Patient
Name] " & strfindpatname & " AND [Auditmonth] " & strfindauditmonth & " AND
[Enteredby] " & strfindenteredby
' Apply the filter and switch it on
With Reports![rptFindings]
.Filter = strFilter
.FilterOn = True
End With
End Sub

 

[Tim Ferguson]

There is something I have changed with the dates or something that
has made it not filter properly.

This is not an adequate description of the problem you are seeing. Wrong
selection of records? No records at all? Error message?

strbeginningdate = ">=#" & Me.beginningdate.Value & "#"
strEndingDate = "<=#" & Me.EndingDate.Value & "#"

I don't know where you got this code from, but this is a hopelessly
inadequate method of passing dates to the database and is bound to fail
sooner or later.

First: you need to ascertain that the textboxes are valid dates -- use
the internal conversion methods:

On Error GoTo Err_MessedUpDates
dtBeginning = CDate(Me.beginningdate.Value)
dtEnding = CDate(Me.EndingDate.Value)

Now, you need to translate these into db-legible strings. VB conversions
are regionally-sensitive, while the database is not, so you have to use
explicit formats:

strJetDates = "\#yyyy\-mm\-dd\#"

strBeginningDate = Format(dtBeginning, strJetDates)
strEndingDate = Format(dtEnding, strJetDates)


Finally, the boundary conditions are wrong. The DateTime values we have
used so far are actually midnight at the start of the quoted day:
therefore the first criterion is:

strCriterion = strCriterion & " AND DateOfEntry >= " & strBeginningDate

The second one is wrong: you want everything up to (but not including)
midnight at the start of the following day. You need to alter the line
above to

strEndingDate = Format(dtEnding + 1, strJetDates)

and then

strCriterion = strCriterion & " AND DateOfEntry < " & strEndingDate

Finally, always do something like

Debug.Assert vbYes = msgbox(strCriterion, vbYesNo, "Is This Okay?")

and really read the stuff you are passing to the database first.


Hope that helps


Tim F