Hello! I'm having trouble getting my report to show all the records for the user-specified date range.

I make use of a report's Filter and OrderBy properties to create dynamic reports which the user can filter and sort using a simple dialog, and see the result appear immediately in the report's Print Preview window.

To see a different set of data all they have to do is specify a different filter, you click the 'Apply Filter' button, and the report will change to display the new data. This is a nice tip I stumbled upon while Googling. (http://www.fontstuff.com/access/acctut19.htm)

About the DB: It is a simple trouble ticket DB for my department.

My problem: When I click 'Apply Filter' (even if I select All Dates), it omits many records in the report. The only time it will actually show all records is if I press the 'Start Over' filter.

Screenshot of my Dynamic Report (http://img209.imageshack.us/img209/7905/dynamicreport10lt.jpg)

Here is the code for the 'Apply Filter' button:

Private Sub cmdApplyFilter_Click()

Dim strLocation As String
Dim strComputer As String
Dim strTech As String
Dim strPatron As String
Dim strStartDate, strEndDate As Date

DoCmd.OpenReport "rptServiceRecords2", acViewPreview

'If a combo box is empty its value is Null so I can use an If Statement to check whether or not the user made a choice and then construct the appropriate SQL:

If IsNull(Me.cboLocation.Value) Then
strLocation = "Like '*'"
Else
strLocation = "='" & Me.cboLocation.Value & "'"
End If

'There is a similar If Statement for each combo box:

If IsNull(Me.cboComputer.Value) Then
strComputer = "Like '*'"
Else
strComputer = "='" & Me.cboComputer.Value & "'"
End If

If IsNull(Me.cboTech.Value) Then
strTech = "Like '*'"
Else
strTech = "='" & Me.cboTech.Value & "'"
End If

If IsNull(Me.txtPatron.Value) Then
strPatron = "Like '*'"
Else
strPatron = "='" & Me.txtPatron.Value & "'"
End If

If IsNull(Me.txtStartDate.Value) Then
strStartDate = "1/1/1900"
Else
strStartDate = Me.txtStartDate.Value
End If

If IsNull(Me.txtEndDate.Value) Then
strEndDate = Date
Else
strEndDate = Me.txtEndDate.Value
End If

'Next comes a line which combines the criteria to form a WHERE clause for the filter:

strFilter = "[Location] " & strLocation & " AND [CompName] " & strComputer & " AND [TechName] " & strTech & " AND [CustomerName] " & strPatron & " AND [ServiceDate] BETWEEN #" & strStartDate & "# AND #" & strEndDate & "#"

'And finally the filter is applied to the report and switched on:

With Reports![rptServiceRecords2]
.Filter = strFilter
.FilterOn = True
End With

'test
If IsNull(Me.cboLocation.Value) And _
IsNull(Me.cboComputer.Value) And IsNull(Me.cboTech.Value) And _
IsNull(Me.txtPatron.Value) And _
IsNull(Me.txtStartDate.Value) And _
IsNull(Me.optDateRange.Value) Then Reports![rptServiceRecords2].FilterOn = False

End Sub

*** Thanks in advance! ***

"IsNull" It does not always work as anticipated. This is because it expects a Null value, and if there is anything else there, a space or a zero lengh string, then it is not Null and your code will not work as you expect. What I would suggest doing first is a test to see if Null is being detected, add a message box to the code after the test for null,

MsgBox " cboLocation.Value is Null"

once we've got that possibility out of the way, if there's still a problem we can look for something else.