Filter report (1 Viewer)

Ravi Kumar

Registered User.
Local time
Today, 17:40
Joined
Aug 22, 2019
Messages
162
dear all ,
I have report called OT details & I have a combo box in a form "filter report",
but the code to filter the area is not working .here is my code.
Code:
Private Sub cmdfilter_Click()
Dim StrWhere As String
StrWhere = "[Area]" = Me.cbolocationbrief & "'"
End Sub
 

June7

AWF VIP
Local time
Today, 04:10
Joined
Mar 9, 2014
Messages
5,423
It doesn't work because syntax is wrong and have not used the StrWhere variable anywhere.
Code:
Private Sub cmdfilter_Click()
DoCmd.OpenReport "OT Details", acViewPreview, , "[Area]='" & Me.cbolocationbrief & "'"
End Sub

Advise not to use spaces nor punctuation/special characters in naming convention.
 

Ravi Kumar

Registered User.
Local time
Today, 17:40
Joined
Aug 22, 2019
Messages
162
hi june7,
thank you it worked ,as I wanted something more I created two more text box which I can use to filter the date of my report. but now date is not getting filtered but area is filtering ,could u pls check the fault in my code??
Code:
Private Sub OK_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 = "OT details"
strDateField = "[Month of Entry]"
lngView = acViewReport
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
DoCmd.OpenReport "OT Details", acViewPreview, , "[Area]='" & Me.cbolocationbrief & "'"
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
 

June7

AWF VIP
Local time
Today, 04:10
Joined
Mar 9, 2014
Messages
5,423
Suggest you use indentation formatting of your code so it is easier to read.

Are you using code from http://allenbrowne.com/ser-62.html ?

I am in the U.S. and use U.S. date style so don't have to resort to the "\#mm\/dd\/yyyy\#" formatting.

Your field is "Month Of Entry" so does this have a full date value?

Advise not to use spaces in naming convention.
 
Last edited:

Ravi Kumar

Registered User.
Local time
Today, 17:40
Joined
Aug 22, 2019
Messages
162
sorry sir I now double checked & I found out date is getting filtered but the area is not getting filtered ,it was a typo earlier .
very sorry for the inconvenience, kindly help me on this regard.
 

Gasman

Enthusiastic Amateur
Local time
Today, 12:10
Joined
Sep 21, 2011
Messages
14,038
Ravi,
If you cannot be bothered to indent your code, then this would help you.
However I suggest you start as it makes debugging code so, so much easier.

HTH

https://www.add-ins.com/macro-products-for-Microsoft-Excel/how-to-indent-vba-code/how-to-indent-vba-code.htm

Code:
Private Sub OK_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 = "OT details"
    strDateField = "[Month of Entry]"
    lngView = acViewReport
    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
    DoCmd.OpenReport "OT Details", acViewPreview, , "[Area]='" & Me.cbolocationbrief & "'"
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
 

June7

AWF VIP
Local time
Today, 04:10
Joined
Mar 9, 2014
Messages
5,423
Is Area a text or number field?

If it is number type, remove apostrophe delimiters.
 

Gasman

Enthusiastic Amateur
Local time
Today, 12:10
Joined
Sep 21, 2011
Messages
14,038
sorry sir I now double checked & I found out date is getting filtered but the area is not getting filtered ,it was a typo earlier .
very sorry for the inconvenience, kindly help me on this regard.

You are opening the report twice?
Once with date criteria and once with location criteria.?

Take a step back, pause and look at what you are doing with the indented version of your code I posted.
 

Ravi Kumar

Registered User.
Local time
Today, 17:40
Joined
Aug 22, 2019
Messages
162
You are opening the report twice?
Once with date criteria and once with location criteria.?

Take a step back, pause and look at what you are doing with the indented version of your code I posted.

no sir , my intention is to open the report only once ,but it should open the report with two filtered fields , that is area (text field) & month of entry(date filed),at the same time.
so can you please tell me how to achieve this ??
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 20:10
Joined
May 7, 2009
Messages
19,169
maybe something like:
Code:
Private Sub OK_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 = "OT details"
    strDateField = "[Month of Entry]"
    lngView = acViewReport
    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
    If Trim(Me.cbolocationbrief & "") <> "" Then
        StrWhere = StrWhere & " And " & _
                 Application.BuildCriteria("[Area]", VarType(Me.cbolocationbrief.Column(0)), Me.cbolocationbrief.Column(0))
    End If
    Debug.Print StrWhere
[COLOR="Navy"]    'DoCmd.OpenReport strReport, lngView, , StrWhere
    If Trim(Me.cbolocationbrief & "") <> "" Then
        StrWhere = StrWhere & " And " & _
                 Application.BuildCriteria("[Area]", VarType(Me.cbolocationbrief.Column(0)), Me.cbolocationbrief.Column(0))
    End If
[/COLOR]    'DoCmd.OpenReport "OT Details", acViewPreview, , "[Area]='" & Me.cbolocationbrief & "'"
    DoCmd.OpenReport "OT Details", acViewPreview, , 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
 

Ravi Kumar

Registered User.
Local time
Today, 17:40
Joined
Aug 22, 2019
Messages
162
maybe something like:
Code:
Private Sub OK_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 = "OT details"
    strDateField = "[Month of Entry]"
    lngView = acViewReport
    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
    If Trim(Me.cbolocationbrief & "") <> "" Then
        StrWhere = StrWhere & " And " & _
                 Application.BuildCriteria("[Area]", VarType(Me.cbolocationbrief.Column(0)), Me.cbolocationbrief.Column(0))
    End If
    Debug.Print StrWhere
[COLOR="Navy"]    'DoCmd.OpenReport strReport, lngView, , StrWhere
    If Trim(Me.cbolocationbrief & "") <> "" Then
        StrWhere = StrWhere & " And " & _
                 Application.BuildCriteria("[Area]", VarType(Me.cbolocationbrief.Column(0)), Me.cbolocationbrief.Column(0))
    End If
[/COLOR]    'DoCmd.OpenReport "OT Details", acViewPreview, , "[Area]='" & Me.cbolocationbrief & "'"
    DoCmd.OpenReport "OT Details", acViewPreview, , 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

hello sir ,
now it is giving error 3075 , I have attached the error message for your reference.
 

Attachments

  • Capture.jpg
    Capture.jpg
    87.5 KB · Views: 97

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 20:10
Joined
May 7, 2009
Messages
19,169
sorry the criteria is doubling:
Code:
Private Sub OK_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 = "OT details"
    strDateField = "[Month of Entry]"
    lngView = acViewReport
    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
    If Trim(Me.cbolocationbrief & "") <> "" Then
        StrWhere = StrWhere & " And " & _
                 Application.BuildCriteria("[Area]", vbString, Me.cbolocationbrief.Column(0))
    End If
    Debug.Print StrWhere
    'DoCmd.OpenReport strReport, lngView, , StrWhere
    'DoCmd.OpenReport "OT Details", acViewPreview, , "[Area]='" & Me.cbolocationbrief & "'"
    DoCmd.OpenReport "OT Details", acViewPreview, , 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
 

Gasman

Enthusiastic Amateur
Local time
Today, 12:10
Joined
Sep 21, 2011
Messages
14,038
Wow,:cool:
That Application.BuildCriteria /method function could help a lot of people who get the syntax incorrect.
 

Ravi Kumar

Registered User.
Local time
Today, 17:40
Joined
Aug 22, 2019
Messages
162
sorry the criteria is doubling:
Code:
Private Sub OK_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 = "OT details"
    strDateField = "[Month of Entry]"
    lngView = acViewReport
    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
    If Trim(Me.cbolocationbrief & "") <> "" Then
        StrWhere = StrWhere & " And " & _
                 Application.BuildCriteria("[Area]", vbString, Me.cbolocationbrief.Column(0))
    End If
    Debug.Print StrWhere
    'DoCmd.OpenReport strReport, lngView, , StrWhere
    'DoCmd.OpenReport "OT Details", acViewPreview, , "[Area]='" & Me.cbolocationbrief & "'"
    DoCmd.OpenReport "OT Details", acViewPreview, , 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

hello sir , now it is working only for two specific areas that is "dies" & "fa" ,
but not for "Blk wire Drawing(fine)" for this particular area again error 3075 is coming, maybe the brackets I am using causing this problem??
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 20:10
Joined
May 7, 2009
Messages
19,169
open the table where Blk wire drawing("fine"), can be found.
remove the double quote (") from all records.

should only be:

Blk wire drawing(fine)
 

Ravi Kumar

Registered User.
Local time
Today, 17:40
Joined
Aug 22, 2019
Messages
162
open the table where Blk wire drawing("fine"), can be found.
remove the double quote (") from all records.

should only be:

Blk wire drawing(fine)

already it is like this only , I have not added any quotation marks, pls refer the attached file .
 

Attachments

  • Capture.JPG
    Capture.JPG
    87.4 KB · Views: 100
  • Capture2.JPG
    Capture2.JPG
    60.8 KB · Views: 94

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 20:10
Joined
May 7, 2009
Messages
19,169
change to:
Code:
Application.BuildCriteria("[Area]", vbString, Chr(34) & Me.cbolocationbrief.Column(0) & Chr(34))
 

Ravi Kumar

Registered User.
Local time
Today, 17:40
Joined
Aug 22, 2019
Messages
162
change to:
Code:
Application.BuildCriteria("[Area]", vbString, Chr(34) & Me.cbolocationbrief.Column(0) & Chr(34))

Thank you so much sir , you really is making my work so much easier!!
 

Gasman

Enthusiastic Amateur
Local time
Today, 12:10
Joined
Sep 21, 2011
Messages
14,038
Should not vbString be dbText in the BuildCriteria method?
 

Users who are viewing this thread

Top Bottom