The VBA code for filtering query doesn't work

[tihmir]

Good evening all.
I have a problem with my query filtering code. I can't figure out where the problem is and why it doesn't work.
Here is the code:

Private Sub cmdReport_Click()

    Dim reportName As String
    Dim strQuery As String
    Dim strDateField As String
    Dim strWhere As String
    Const strcJetDate = "\#mm\/dd\/yyyy\#"  'Do NOT change it to match your local settings.
    
    reportName = "report"
    strQuery = "Query1"
    strDateField = "[DateTask]" 'Put your field name in the square brackets in these quotes.
    lngView = acViewReport     'Use acViewNormal to print instead of preview.
    
    'Build the filter string.
    If IsDate(Me.txt_DateFrom) Then
        strWhere = "(" & strDateField & " >= " & Format(Me.txt_DateFrom, strcJetDate) & ")"
        Else
        MsgBox "Please, inser date!", vbInformation, "Attention!"
        Exit Sub
    End If
    If IsDate(Me.txt_DateTo) Then
        If strWhere <> vbNullString Then
            strWhere = strWhere & " AND "
    End If
        strWhere = strWhere & "(" & strDateField & " < " & Format(Me.txt_DateTo + 1, strcJetDate) & ")"
        Else
        MsgBox "Please, inser date!", vbInformation, "Attention!"
        Exit Sub
    End If
        
  If Trim(Me.cbo_Worker & "") <> "" Then
        If strWhere <> vbNullString Then
            strWhere = strWhere & " AND "
        End If
        strWhere = strWhere & "[Worker] = '" & Me.cbo_Worker & "'"
        Else
        MsgBox "Please, inser 'Worker'!", vbInformation, "Attention!"
        Exit Sub
    End If
    
    ' check if the strWhere has some value
    If Trim(strWhere) = "" Then strWhere = "(1=1)"
        
    'Close the report if already open: otherwise it won't filter properly.
    If CurrentProject.AllReports(strReport).IsLoaded Then
        DoCmd.Close acReport, strReport
    End If
        DoCmd.OpenReport reportName, acViewReport

End Sub

I also add the database

 

[Gasman]

Debug.Print your StrWhere.

Plus you do not appear to use it when you open the report?????

 

[theDBguy]

Hi. Sorry, can't look at your file right now. What does "doesn't work" mean? Are you getting any error messages? Can you also provide a step-by-step instructions on how to duplicate the problem, using your sample database?

 

[Gasman]

I get prompts for DateTask and Worker ????

Do yourself a favour.

Put Option Explicit at the top of every module, object then Compile and fix all those issues.

 

[tihmir]

Debug.Print your StrWhere.

Plus you do not appear to use it when you open the report?????

Yes, i can't understand why the code doesn't filter query1 and then open the report based on query3. The code simply loads the report without performing the filtering

 

[tihmir]

I get prompts for DateTask and Worker ????

With the code I want to filter by date and worker it doesn't work.

 

[tihmir]

Hi. Sorry, can't look at your file right now. What does "doesn't work" mean? Are you getting any error messages? Can you also provide a step-by-step instructions on how to duplicate the problem, using your sample database?

no, just the code doesn't work and no filtering is done. I don't know where the problem might be

 

[Gasman]

Well as I mentioned you are not even using the strwhere to open the report with, so not sure how you mean to filter it.?

I'm off to watch the Portugal Grand Prix now.

 

[theDBguy]

no, just the code doesn't work and no filtering is done. I don't know where the problem might be

Did you step through it during execution? Just curious...

 

[tihmir]

Well as I mentioned you are not even using the strwhere to open the report with, so not sure how you mean to filter it.?

I'm off to watch the Portugal Grand Prix now.

enjoy watching the Portugal Grand Prix
When you have time would you tell me what i need to modify in the code to work properly please?

 

[Gasman]

Adverts on
On the open report line, use intellisense to insert the where clause.

 

[tihmir]

Adverts on
On the open report line, use intellisense to insert the where clause.

The Record Source of the report is Query3. If I add the where clause here:

DoCmd.OpenReport reportName, acViewReport, , strWhere

it shows me "Enter Parameter value DateTask and Worker because in Query3 there is no Date and Worker

 

[Gasman]

Yes, i can't understand why the code doesn't filter query1 and then open the report based on query3. The code simply loads the report without performing the filtering

You cannot expect that strWhere to filter query1, it is for the report, but if those fields are not supplied to the report it will not work?
Perhaps modify the query1 SQL with the qdf.sql ?

 

[Gasman]

If query1 is just for that report, put the criteria as the form controls in the query itself

 

[tihmir]

If query1 is just for that report, put the criteria as the form controls in the query itself

Yes, query1 is only for that report and when I put criteria in the query it works perfect:

[tbl_Inspections.Worker]=[forms]![fm_Report2]![cbo_Worker] Or [forms]![fm_Report2]![cbo_Worker] Is Null

IIf([Forms]![fm_Report2]![txt_DateFrom] Is Null And [forms]![fm_Report2]![txt_DateTo] Is Null;True;IIf([Forms]![fm_Report2]![txt_DateFrom] Is Not Null And [forms]![fm_Report2]![txt_DateTo] Is Not Null;[DateTask] Between [Forms]![fm_Report2]![txt_DateFrom] And [Forms]![fm_Report2]![txt_DateTo];[DateTask]>=[forms]![fm_Report2]![txt_DateFrom] Or [DateTask]<=[forms]![fm_Report2]![txt_DateTo]))

but I was wondering if I could replace the criteria in the query with the VBA code to filter query1?

 

[Gasman]

Yes, query1 is only for that report and when I put criteria in the query it works perfect:

[tbl_Inspections.Worker]=[forms]![fm_Report2]![cbo_Worker] Or [forms]![fm_Report2]![cbo_Worker] Is Null

IIf([Forms]![fm_Report2]![txt_DateFrom] Is Null And [forms]![fm_Report2]![txt_DateTo] Is Null;True;IIf([Forms]![fm_Report2]![txt_DateFrom] Is Not Null And [forms]![fm_Report2]![txt_DateTo] Is Not Null;[DateTask] Between [Forms]![fm_Report2]![txt_DateFrom] And [Forms]![fm_Report2]![txt_DateTo];[DateTask]>=[forms]![fm_Report2]![txt_DateFrom] Or [DateTask]<=[forms]![fm_Report2]![txt_DateTo]))

but I was wondering if I could replace the criteria in the query with the VBA code to filter query1?

I would only know how, by changing the qdf SQL, so I would suggest you stick with what works now. I'd still add option explicit and sort your variables out?

 

[tihmir]

I would only know how, by changing the qdf SQL, so I would suggest you stick with what works now. I'd still add option explicit and sort your variables out?

Good suggestion, I will add option explicit and will sort out the variables
And yet if there is any idea how I could do it with VBA please share it

 

[Gasman]

If I wanted to use Query1 from other forms, then I would likely use TempVars as the criteria and set in each form.

A more flexible way would be to amend the sql of the querydef for that query, but that is a little more complicated to achieve and continue to use, as once you add the WHERE clause you need to amend that each time.

https://www.google.com/search?q=ame...9i57j69i59.15567j0j7&sourceid=chrome&ie=UTF-8

I would likely just replace the whole WHERE clause each time. Use the Split() function to get the SQL without the WHERE clause.
Alternatively, you could use CreateQueryDef and write the query each time.

HTH