Filtering query all months with combo box (1 Viewer)

theDBguy

I’m here to help
Staff member
Local time
Today, 08:44
Joined
Oct 29, 2018
Messages
21,360
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, 10:44
Joined
Sep 17, 2019
Messages
159
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, 10:44
Joined
Sep 17, 2019
Messages
159
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, 08:44
Joined
Oct 29, 2018
Messages
21,360
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, 10:44
Joined
Sep 17, 2019
Messages
159
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, 08:44
Joined
Oct 29, 2018
Messages
21,360
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, 10:44
Joined
Sep 17, 2019
Messages
159
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, 08:44
Joined
Oct 29, 2018
Messages
21,360
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.
 

Db-why-not

Registered User.
Local time
Today, 10:44
Joined
Sep 17, 2019
Messages
159
I got it working now with this code. Thanks.
Code:
Private Sub cmdGetReport_Click()
Dim strCriteria As String

strCriteria = "1=1"

If IsNumeric(Forms!AuditAccuracy_Reports!cboMonth) Then strCriteria = strCriteria & " AND [mth] = " & Forms!AuditAccuracy_Reports!cboMonth

    If IsNumeric(Forms!AuditAccuracy_Reports!cboYear) Then strCriteria = strCriteria & " AND [Yr] = " & Forms!AuditAccuracy_Reports!cboYear
   
[/QUOTE]

[QUOTE="Db-why-not, post: 1720607, member: 148922"]

    Debug.Print strCriteria

If Me.cboReportType = "Q1 - MergeAudit Reports" Then
DoCmd.OpenReport "R_MA_audits_Total_Accuracy", acViewReport, , strCriteria

ElseIf Me.cboReportType = "Q2 - Patient Registration Errors Reports" Then
DoCmd.OpenReport "R_Q1_audits_total_accuracy", acViewReport
ElseIf Me.cboReportType = "Admin - Q1 Report" Then
DoCmd.OpenReport "R_admin_Detailed_Q1_audits", acViewReport
ElseIf Me.cboReportType = "Admin - Q2 Report" Then
DoCmd.OpenReport "R_admin_Detailed_MA_audits", acViewReport
End If

End Sub

Now I am getting error with this code. its in my on form open event.

Code:
Me.lbMonth.Caption = MonthName(Forms!AuditAccuracy_Reports!cboMonth) & " " & Forms!AuditAccuracy_Reports!cboYear

I just need to do the same thing I did with the other code, I think. I will post that part once I have it working. Thank you all for your help!!!!!!
I'm trying to edit this code again to add another dropdown box(cboTechnician) that will query my report further. The drop down will query field [Name] which is names of technicians for the reports. I used ISnull to test that something is selected for the combobox.

This the is the code that I used. I get error message: "Runtime error '3464' Data type mismatch in criteria expression' then it highlight the code with the report I selected in combo boxes "DoCmd.OpenReport "R_MA_audits_Total_Accuracy", acViewReport, , strCriteria"

Code:
Private Sub cmdGetReport_Click()

Dim strCriteria As String

strCriteria = "1=1"

If IsNumeric(Forms!AuditAccuracy_Reports!cboMonth) Then strCriteria = strCriteria & " AND [mth] = " & Forms!AuditAccuracy_Reports!cboMonth

    If IsNumeric(Forms!AuditAccuracy_Reports!cboYear) Then strCriteria = strCriteria & " AND [Yr] = " & Forms!AuditAccuracy_Reports!cboYear

   If (IsNull(Forms!AuditAccuracy_Reports!cboTechnician)=False) Then strCriteria = strCriteria & " AND [Name] = " & Forms!AuditAccuracy_Reports!cboTechnician

    Debug.Print strCriteria


If Me.cboReportType = "Q1 - MergeAudit Reports" Then

DoCmd.OpenReport "R_MA_audits_Total_Accuracy", acViewReport, , strCriteria

ElseIf Me.cboReportType = "Q2 - Patient Registration Errors Reports" Then

DoCmd.OpenReport "R_Q1_audits_total_accuracy", acViewReport

ElseIf Me.cboReportType = "Admin - Q1 Report" Then

DoCmd.OpenReport "R_admin_Detailed_Q1_audits", acViewReport

ElseIf Me.cboReportType = "Admin - Q2 Report" Then

DoCmd.OpenReport "R_admin_Detailed_MA_audits", acViewReport

End If

End Sub
[/CODE]
 

theDBguy

I’m here to help
Staff member
Local time
Today, 08:44
Joined
Oct 29, 2018
Messages
21,360
I'm trying to edit this code again to add another dropdown box(cboTechnician) that will query my report further. The drop down will query field [Name] which is names of technicians for the reports. I used ISnull to test that something is selected for the combobox.

This the is the code that I used. I get error message: "Runtime error '3464' Data type mismatch in criteria expression' then it highlight the code with the report I selected in combo boxes "DoCmd.OpenReport "R_MA_audits_Total_Accuracy", acViewReport, , strCriteria"

Code:
Private Sub cmdGetReport_Click()

Dim strCriteria As String

strCriteria = "1=1"

If IsNumeric(Forms!AuditAccuracy_Reports!cboMonth) Then strCriteria = strCriteria & " AND [mth] = " & Forms!AuditAccuracy_Reports!cboMonth

    If IsNumeric(Forms!AuditAccuracy_Reports!cboYear) Then strCriteria = strCriteria & " AND [Yr] = " & Forms!AuditAccuracy_Reports!cboYear

   If (IsNull(Forms!AuditAccuracy_Reports!cboTechnician)=False) Then strCriteria = strCriteria & " AND [Name] = " & Forms!AuditAccuracy_Reports!cboTechnician

    Debug.Print strCriteria


If Me.cboReportType = "Q1 - MergeAudit Reports" Then

DoCmd.OpenReport "R_MA_audits_Total_Accuracy", acViewReport, , strCriteria

ElseIf Me.cboReportType = "Q2 - Patient Registration Errors Reports" Then

DoCmd.OpenReport "R_Q1_audits_total_accuracy", acViewReport

ElseIf Me.cboReportType = "Admin - Q1 Report" Then

DoCmd.OpenReport "R_admin_Detailed_Q1_audits", acViewReport

ElseIf Me.cboReportType = "Admin - Q2 Report" Then

DoCmd.OpenReport "R_admin_Detailed_MA_audits", acViewReport

End If

End Sub
[/CODE]
Are you able to post a sample db with test data? It might go faster with something to use for testing.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 08:44
Joined
Oct 29, 2018
Messages
21,360
I dont want to share the data I have. I don't have time to make some dummy data. I can post it without the data.
Yes, try to post without any data. We'll see if we can make it work.

However, if you really need our help, you could try to spend a little bit of time to help us help you. Couldn't you create one single sample data, at least? Just wondering...
 

Db-why-not

Registered User.
Local time
Today, 10:44
Joined
Sep 17, 2019
Messages
159
Maybe I can do that tommorrow, report is a summary of lots of data. I would need to create several records for each report.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 08:44
Joined
Oct 29, 2018
Messages
21,360
Maybe I can do that tommorrow, report is a summary of lots of data. I would need to create several records for each report.
Do we need all the reports to see and fix the problem?
 

Db-why-not

Registered User.
Local time
Today, 10:44
Joined
Sep 17, 2019
Messages
159
I just decided to do the reports in Excel. I linked some of the querys from access and created some pivot tables in excel formatted the way I want and able to filter the way I want easily. Thank you all. It seems like doing reports in excel is a lot easier. I think I might just skip reports in access and just do my linked reports in excel from now on. I hadn't really combined excel and access together like that very much before.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 08:44
Joined
Oct 29, 2018
Messages
21,360
I just decided to do the reports in Excel. I linked some of the querys from access and created some pivot tables in excel formatted the way I want and able to filter the way I want easily. Thank you all. It seems like doing reports in excel is a lot easier. I think I might just skip reports in access and just do my linked reports in excel from now on. I hadn't really combined excel and access together like that very much before.
Hi. Congratulations! I try to avoid making reports as well. I just recommend exporting the data to Excel, so the user can freely manipulate it to their liking.
 

Users who are viewing this thread

Top Bottom