Filtering query all months with combo box (1 Viewer)

theDBguy

I’m here to help
Staff member
Local time
Today, 03:48
Joined
Oct 29, 2018
Messages
13,163
Here is the query that I am using for the form
Code:
SELECT tblMA_workload.lPersonID, tblMA_workload.DMISID, tblMA_workload.ien1, tblMA_workload.result, tblMA_workload.Name, tblMA_workload.dtfixed, tblMA_Audit.AuditCompleted, tblMA_Audit.dtAuditDate, Month([dtfixed]) AS mth, Year([dtFixed]) AS Yr
FROM tblMA_workload LEFT JOIN tblMA_Audit ON tblMA_workload.lPersonID = tblMA_Audit.lPersonID
WHERE (((tblMA_workload.Name)<>[Forms]![Login_frm]![cboUser]) AND ((tblMA_Audit.AuditCompleted)=No Or (tblMA_Audit.AuditCompleted) Is Null));

Its a continuous form. I was trying to get just cboTechnician to filter the data and I couldn't get it to work even when I took out the other code for the other combo boxes.
Interesting. I do see [name] in the source.

Okay, let's go back to this. What happens?
Code:
If Me.cboTechnician > "" AND Me.cboTechnician <> "All Technicians" Then
    strCriteria = strCriteria & " AND [name]='" & Me.cboTechnician & "'"
End If

Debug.Print strCriteria
 

Db-why-not

Registered User.
Local time
Today, 05:48
Joined
Sep 17, 2019
Messages
127
Interesting. I do see [name] in the source.

Okay, let's go back to this. What happens?
Code:
If Me.cboTechnician > "" AND Me.cboTechnician <> "All Technicians" Then
    strCriteria = strCriteria & " AND [name]='" & Me.cboTechnician & "'"
End If

Debug.Print strCriteria
IT doesn't do anything. I set the initial criteria in each combo box form load and then it filters but it doesnt work for cbotechnician after I try to change the combobox and hit filter button.

Code:
Private Sub Form_Load()
'Sets the filter comboboxes
Me.cboMonth = 3
Me.cboYear = 2020
Me.cboTechnician = "Jane Doe"


Dim strCriteria As String
strCriteria = "1=1"

If Me.cboMonth > " " Then
  strCriteria = strCriteria & " AND [Mth] = " & Me.cboMonth
  End If
If Me.cboYear > " " Then
    strCriteria = strCriteria & " AND [Yr] = " & Me.cboYear
   End If
If Me.cboTechnician > "" And Me.cboTechnician <> "All Technicians" Then
    strCriteria = strCriteria & " AND [name]='" & Me.cboTechnician & "'"
End If

Debug.Print strCriteria
Me.Filter = strCriteria
Me.FilterOn = True
Me.Requery
End Sub
 

Db-why-not

Registered User.
Local time
Today, 05:48
Joined
Sep 17, 2019
Messages
127
I don't have to have the "all technicians" option for my combobox. I just want a way to be able to show all the technicians. If I can just leave the combo box blank for cboTechnician box and have it show all technicians that would be good also. Thats what I did for my reports I had a combobox stay blank to pull up all the months or all the years for reports. then click get report button. This page is different because Im not opening a report, Im just filtering a continuous form.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 03:48
Joined
Oct 29, 2018
Messages
13,163
I don't have to have the "all technicians" option for my combobox. I just want a way to be able to show all the technicians. If I can just leave the combo box blank for cboTechnician box and have it show all technicians that would be good also. Thats what I did for my reports I had a combobox stay blank to pull up all the months or all the years for reports. then click get report button. This page is different because Im not opening a report, Im just filtering a continuous form.
Hi. Using the code I gave you, what happens if you empty out the technician combo?
 

Db-why-not

Registered User.
Local time
Today, 05:48
Joined
Sep 17, 2019
Messages
127
Hi. Using the code I gave you, what happens if you empty out the technician combo?
When it first loads it works and it shows all the technicians. I can filter with the month and year and it works. As soon as I move the technician to any name other than blank selection it not longer pulls any data.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 03:48
Joined
Oct 29, 2018
Messages
13,163
When it first loads it works and it shows all the technicians. I can filter with the month and year and it works. As soon as I move the technician to any name other than blank selection it not longer pulls any data.
Okay. I think that confirms the problem is with the value returned by the combobox and the field you're trying to filter.

I think it's time for us to ask if it's possible for you to post a test db, so we can help you get to the bottom of this?
 

Db-why-not

Registered User.
Local time
Today, 05:48
Joined
Sep 17, 2019
Messages
127
I changed the data source for my dropdown box CboTechnician and now it works perfect. I'm not sure why the other data source wasn't working.

Code:
Private Sub cmdFilter_Click()

Dim strCriteria As String
strCriteria = "1=1"

If Me.cboMonth > " " Then
  strCriteria = strCriteria & " AND [Mth] = " & Me.cboMonth
  End If
If Me.cboYear > " " Then
    strCriteria = strCriteria & " AND [Yr] = " & Me.cboYear
   End If
If Me.cboTechnician > "" Then
    strCriteria = strCriteria & " AND [Name]='" & Me.cboTechnician & "'"
End If
Debug.Print strCriteria

'Turns on filter
Me.Filter = strCriteria
Me.FilterOn = True
Me.Requery

End Sub

Thanks For everyone help!
 

theDBguy

I’m here to help
Staff member
Local time
Today, 03:48
Joined
Oct 29, 2018
Messages
13,163
I changed the data source for my dropdown box CboTechnician and now it works perfect. I'm not sure why the other data source wasn't working.

Code:
Private Sub cmdFilter_Click()

Dim strCriteria As String
strCriteria = "1=1"

If Me.cboMonth > " " Then
  strCriteria = strCriteria & " AND [Mth] = " & Me.cboMonth
  End If
If Me.cboYear > " " Then
    strCriteria = strCriteria & " AND [Yr] = " & Me.cboYear
   End If
If Me.cboTechnician > "" Then
    strCriteria = strCriteria & " AND [Name]='" & Me.cboTechnician & "'"
End If
Debug.Print strCriteria

'Turns on filter
Me.Filter = strCriteria
Me.FilterOn = True
Me.Requery

End Sub

Thanks For everyone help!
Hi. Congratulations! Glad to hear you got it sorted out. Good luck with your project.
 

Users who are viewing this thread

Top Bottom