• ** There has been a recent site upgrade. Please clear your browser cache to avoid issues. **
  • New forum feature - post voting and best solution

    Check out this thread for the details: https://www.access-programmers.co.uk/forums/threads/new-forum-feature-post-voting-and-best-answer.314134/

    This new feature looks great to me! :)

  • We now have 3 forum themes

    Go for the default (light) theme, Shades of Grey or Shades of Blue. I just added the Blue one.

    The thread about it is here: https://www.access-programmers.co.uk/forums/threads/new-forum-theme-shades-of-blue.314136/

Filtering query all months with combo box (1 Viewer)

theDBguy

I’m here to help
Staff member
Local time
Today, 13:08
Joined
Oct 29, 2018
Messages
12,595
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, 15:08
Joined
Sep 17, 2019
Messages
124
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, 15:08
Joined
Sep 17, 2019
Messages
124
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, 13:08
Joined
Oct 29, 2018
Messages
12,595
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, 15:08
Joined
Sep 17, 2019
Messages
124
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, 13:08
Joined
Oct 29, 2018
Messages
12,595
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, 15:08
Joined
Sep 17, 2019
Messages
124
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, 13:08
Joined
Oct 29, 2018
Messages
12,595
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