The VBA code for filtering query doesn't work

T

tihmir

Registered User.
Local time
Today, 06:48
Joined
May 1, 2018
Messages
257
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

Debug.Print your StrWhere.

Plus you do not appear to use it when you open the report?????
 
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?
 
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:
Gasman said:
Debug.Print your StrWhere.

Plus you do not appear to use it when you open the report?????
Click to expand...
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
 
theDBguy said:
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?
Click to expand...
no, just the code doesn't work and no filtering is done. I don't know where the problem might be
 
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. :)
 
tihmir said:
no, just the code doesn't work and no filtering is done. I don't know where the problem might be
Click to expand...
Did you step through it during execution? Just curious...
 
Gasman said:
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. :)
Click to expand...
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?
 
Adverts on
On the open report line, use intellisense to insert the where clause.
 
Gasman said:
Adverts on
On the open report line, use intellisense to insert the where clause.
Click to expand...
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
 
tihmir said:
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
Click to expand...
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 ?
 
If query1 is just for that report, put the criteria as the form controls in the query itself
 
Gasman said:
If query1 is just for that report, put the criteria as the form controls in the query itself
Click to expand...
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? :)
 
tihmir said:
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? :)
Click to expand...
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?
 
Gasman said:
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?
Click to expand...
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 :)
 
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
 
You must log in or register to reply here.

Similar threads

T
Can someone please assist with the VBA code Below - Error Message 3075
Replies
16
Views
389
CJ_London
C
C
Solved getting filtered records
Replies
11
Views
908
ClaraBarton
C
F
VBA Webpage search
Replies
1
Views
80
theDBguy
theDBguy
I
Filter between two data
Replies
3
Views
247
sonic8
S
nimeshaanethmini
Need Help with VBA Code for Access Form Filtering
Replies
5
Views
373
Uncle Gizmo
Uncle Gizmo

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom