Filtering a Report using a Form (1 Viewer)

Zan1818 · Aug 21, 2017

Hi everyone,

I'm dealing with a little problem when filtering my report. You see, I had already created a form to filter a report according to a date range. It works perfectly! But today when I showed it to the staff, they asked me if I could add another criteria to it. They want me to add by name as well by date range. I have tried the Allen Browne example but I can't figure out how to make it work.
Please any help would be appreciated

plog · Aug 21, 2017

It would be helpful if you posted the code you use to do the filtering currently. Hopefully it is using DoCmd.OpenReport (https://msdn.microsoft.com/en-us/vba/access-vba/articles/docmd-openreport-method-access). If not, it will be now.

DoCmd.OpenReport takes a filter string to open a report to specific records. What you do is add the new criteria to that filter string.

Zan1818 · Aug 21, 2017

Hi,

Below it's the code I'm using to filter the report within a date range (using the Allen Browne example):

Private Sub cmdPreview_Click()
On Error GoTo Err_Handler

Dim strReport As String
Dim strDateField As String
Dim strWhere As String
Dim lngView As Long
Const strcJetDate = "\#mm\/dd\/yyyy\#"

strReport = "SafetyR"
strDateField = "[Month]"
lngView = acViewPreview .

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

If CurrentProject.AllReports(strReport).IsLoaded Then
DoCmd.Close acReport, strReport
End If

Debug.Print strWhere
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

Thanks for the help

plog · Aug 21, 2017

Looks like you are building the exact string I described--strWhere. You need to add more If statements to add your additional criteria to it.

Zan1818 · Aug 21, 2017

Would you mind walking me through that? I tried adding something like:

If Not IsNull(Me.cmbStaffID) Then
strWhere = strWhere & "([StaffID] = " & Me.cmbStaffID & ") AND "
End If

It always gives me an error 3075 and I don't really know how to fix it.

plog · Aug 21, 2017

Don't guess about what's in strWhere--spit it out to see what it contains. You can do that via Debug.print or put it in a message box:

MsgBox(strWhere)

Zan1818 · Aug 21, 2017

I don't think I understand. Do you mean that the If statement for the StaffID criteria is right? I just need to add the last line?
Sorry about the trouble, I'm just not good at coding.

jdraw · Aug 22, 2017

See this material from Martin Green re Dynamic Reports

plog · Aug 22, 2017

I mean the strWhere is incorrect. You need to see what's in it by spitting its contents out so that you can see it with your eyes, not discern it with your mind.

Zan1818 · Aug 22, 2017

At the moment I'm using this:

Private Sub cmdPreview_Click()
On Error GoTo Err_Handler

Dim strReport As String
Dim strDateField As String
Dim strWhere As String
Dim strFilter As String
Dim lngView As Long
Const strcJetDate = "\#mm\/dd\/yyyy\#"

strReport = "TechnicalAssistanceR"
strDateField = "[Month]"
lngView = acViewPreview

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

If Not IsNull(Me.cmbStaffID) Then
strStaffID = "Like '*'"
Else
strStaffID = "='" & Me.cmbStaffID.Value & "'"
End If

If CurrentProject.AllReports(strReport).IsLoaded Then
DoCmd.Close acReport, strReport
End If

Debug.Print strWhere
DoCmd.OpenReport strReport, lngView, , strWhere

MsgBox(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

When I run this the next message pops-up:
([Month]>=#07/01/2017#)AND([Month]<#10/02/2017#)
This is when I put 7/1/2017 to 10/1/2017 for the date range. Any ideas on what this means?

plog · Aug 22, 2017

That message is how your report is to be filtered. You need to add your new criteria to it.

Zan1818 · Aug 22, 2017

Does that mean I only need one if statement for the date range?

plog · Aug 22, 2017

If that parts working, that parts working. I thought you want to add additional criteria to it?

To do that, you need another block of code to add on to your strWhere variable.

Zan1818 · Aug 22, 2017

Yes, that part is working but I don't understand why the error is related to the dates when I'm trying to add a combo box to the filter relating to the names.
I think I'm not understanding to where in the code I have, it's where you say I need to add something to. And also what is exactly what I need to add.
Sorry about this but I'm really a newbie when it comes to coding.

Zan1818 · Aug 22, 2017

This has been my last update on the code:

Code:

Private Sub cmdPreview_Click()
On Error GoTo Err_Handler
    
    Dim strReport As String
    Dim strDateField As String
    Dim strStaffID As String
    Dim strWhere As String
    Dim lngView As Long
   
    strReport = "TechnicalAssistanceR"
    strDateField = "[Month]"
    strStaffID = "[StaffID]"
    lngView = acViewPreview
       
  
    If IsDate(Me.txtStartDate) And IsDate(Me.txtEndDate) Then
        strWhere = strDateField & " Between #" & Format(Me.txtStartDate, "mm/dd/yyyy") & "# AND #" & Format(Me.txtEndDate, "mm/dd/yyyy") & "#"
    Else
        MsgBox "Please enter a start date and an end date", vbExclamation, "Error"
    End If
    
    
    If IsNull(Me.cmbStaffID) Then
        strStaffID = "Like '*'"
    Else
        strStaffID = "='" & Me.cmbStaffID & "'"
    End If

    If CurrentProject.AllReports(strReport).IsLoaded Then
        DoCmd.Close acReport, strReport
    End If
    
    
    Debug.Print strWhere
    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

I have worked with it for two days now and I just can't figure out how to add another criteria. When I run the above code the date range works fine but the name field is completely ignored even though I do have an If statement for it. Please if someone could just let me know what to add to make it work I would really appreciate it.

plog · Aug 22, 2017

Every part of the working code was used somehow. Where and how are you using strStaffID?

plog · Aug 22, 2017

Perhaps some one else will come along and just give you the code you want, I have no interest in that. If you would like to learn how to code I suggest you go through your code and understand what each part does.

Ultimately you want to open a report. That is accomplished via DoCmd.OpenReport. Look into that function and then work towards calling it correctly.

Minty · Aug 22, 2017

As Plog has stated you need to add together (concatenate) the two parts of the where clause. At the moment you haven't done anything with the staff id string you have created.

Open the debug window (Press alt+f11) then open the immediate window (press ctrl + g) you will see the value of strWhere

Filtering a Report using a Form (1 Viewer)

Zan1818

Registered User.

plog

Banishment Pending

Zan1818

Registered User.

plog

Banishment Pending

Zan1818

Registered User.

plog

Banishment Pending

Zan1818

Registered User.

jdraw

Super Moderator

plog

Banishment Pending

Zan1818

Registered User.

plog

Banishment Pending

Zan1818

Registered User.

plog

Banishment Pending

Zan1818

Registered User.

Zan1818

Registered User.

plog

Banishment Pending

plog

Banishment Pending

Minty

AWF VIP

Similar threads

Users who are viewing this thread