The VBA code for filtering query doesn't work (1 Viewer)

tihmir

Registered User.
Local time
Yesterday, 21:57
Joined
May 1, 2018
Messages
216
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:
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
 

Attachments

  • Database1.zip
    290.1 KB · Views: 260

Gasman

Enthusiastic Amateur
Local time
Today, 05:57
Joined
Sep 21, 2011
Messages
10,531
Debug.Print your StrWhere.

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

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 21:57
Joined
Oct 29, 2018
Messages
18,962
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

Enthusiastic Amateur
Local time
Today, 05:57
Joined
Sep 21, 2011
Messages
10,531
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. :(
 
Last edited:

tihmir

Registered User.
Local time
Yesterday, 21:57
Joined
May 1, 2018
Messages
216
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

Registered User.
Local time
Yesterday, 21:57
Joined
May 1, 2018
Messages
216
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

Enthusiastic Amateur
Local time
Today, 05:57
Joined
Sep 21, 2011
Messages
10,531
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

I’m here to help
Staff member
Local time
Yesterday, 21:57
Joined
Oct 29, 2018
Messages
18,962
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

Registered User.
Local time
Yesterday, 21:57
Joined
May 1, 2018
Messages
216
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

Enthusiastic Amateur
Local time
Today, 05:57
Joined
Sep 21, 2011
Messages
10,531
Adverts on
On the open report line, use intellisense to insert the where clause.
 

tihmir

Registered User.
Local time
Yesterday, 21:57
Joined
May 1, 2018
Messages
216
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:
Code:
DoCmd.OpenReport reportName, acViewReport, , strWhere
it shows me "Enter Parameter value DateTask and Worker because in Query3 there is no Date and Worker
 

Gasman

Enthusiastic Amateur
Local time
Today, 05:57
Joined
Sep 21, 2011
Messages
10,531
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

Enthusiastic Amateur
Local time
Today, 05:57
Joined
Sep 21, 2011
Messages
10,531
If query1 is just for that report, put the criteria as the form controls in the query itself
 

tihmir

Registered User.
Local time
Yesterday, 21:57
Joined
May 1, 2018
Messages
216
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:
Code:
[tbl_Inspections.Worker]=[forms]![fm_Report2]![cbo_Worker] Or [forms]![fm_Report2]![cbo_Worker] Is Null
Code:
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

Enthusiastic Amateur
Local time
Today, 05:57
Joined
Sep 21, 2011
Messages
10,531
Yes, query1 is only for that report and when I put criteria in the query it works perfect:
Code:
[tbl_Inspections.Worker]=[forms]![fm_Report2]![cbo_Worker] Or [forms]![fm_Report2]![cbo_Worker] Is Null
Code:
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

Registered User.
Local time
Yesterday, 21:57
Joined
May 1, 2018
Messages
216
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

Enthusiastic Amateur
Local time
Today, 05:57
Joined
Sep 21, 2011
Messages
10,531
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
 

Users who are viewing this thread

Top Bottom