Filtering query all months with combo box (1 Viewer)

Db-why-not

Registered User.
Local time
Today, 05:41
Joined
Sep 17, 2019
Messages
159
I have a form with a few combo boxes, that will open up different reports and filter the report by month and year. I set the criteria in my queries for the reports to equal the value in the comboboxes. I I have a 3 combos boxes. One combo box is for the type of report, then one combo box is month, then one is for year. I will probably add another combo box for Auditor name also. The user must select the type of report they want. For the Month and year combo boxes I want it to be optional to select those so that they can show all months or all years. I'm not sure how to get it that way because I keep getting errors. I tried adding this code but it causes a problem somewhere else and it doesn't work perfectly.

Code:
WHERE (((Month([dtfixed]))=[Forms]![AuditAccuracy_Reports]![cboMonth]) AND ((Year([dtFixed]))=[Forms]![AuditAccuracy_Reports]![cboYear])) OR (([Forms]![AuditAccuracy_Reports]![cboYear] Is Null) AND (([Forms]![AuditAccuracy_Reports]![cboMonth]) Is Null));

I also get an error with this code after adding the above code. I have a label on my report that gets the caption from the date and month that is selected from the combo box. When nothing is selected in the month or year combobox it causes an error for that code. I would like the caption just to be blank if nothing is selected for the comboboxs.

Code:
Private Sub Report_Open(Cancel As Integer)

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


End Sub

I'm wondering how I should fix this error with the caption? Or should I just do this combobox differently. Should I add a Selection "All Months", then add selection "All Years" to my combo boxes? If I add All month selection to my combobox how can I get it to show all months in my query? I'm not sure what the best way would be to do that.
 

plog

Banishment Pending
Local time
Today, 05:41
Joined
May 11, 2011
Messages
11,611
You should remove the criteria from the query and use DoCmd.OpenReport:


That allows you to open a report and apply criteria there. That way you can dynamically build your criteria using just the criteria the user selects.
 

Db-why-not

Registered User.
Local time
Today, 05:41
Joined
Sep 17, 2019
Messages
159
I am trying to do that and getting this error "Runtime error ' 3075' syntax error in string in query expression ' [mth]='4' ( iget that error when April is selected, so its showing the correct value for the cboMonth.

Code:
Private Sub cmdGetReport_Click()
If Me.cboReportType = "Q1 - MergeAudit Reports" Then

DoCmd.OpenReport "R_MA_audits_Total_Accuracy", acViewReport, , "[mth]='" & Forms!AuditAccuracy_Reports!cboMonth

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

What am I doing wrong? Do I need to add the name of the query that the report is based off? Or something else

Code:
DoCmd.OpenReport "R_MA_audits_Total_Accuracy", acViewReport, , "[mth]='" & Forms!AuditAccuracy_Reports!cboMonth
 

Attachments

  • error message.JPG
    error message.JPG
    36.8 KB · Views: 182

plog

Banishment Pending
Local time
Today, 05:41
Joined
May 11, 2011
Messages
11,611
Quote marks need buddies. You're first DoCmd.Openreport has a lonely single quote mark. If [mth] is a number you need 0 of them, if its a string, you need 2.
 

Gasman

Enthusiastic Amateur
Local time
Today, 10:41
Joined
Sep 21, 2011
Messages
14,038
If the cboMonth is numeric, which you show it to be, you do not surround the value with any characters.?

You are using a single quote in front of the control, yet nothing after it?
 

Db-why-not

Registered User.
Local time
Today, 05:41
Joined
Sep 17, 2019
Messages
159
I got this code to work properly.
Code:
DoCmd.OpenReport "R_MA_audits_Total_Accuracy", acViewReport, , "[mth]=" & Forms!AuditAccuracy_Reports!cboMonth
 

Db-why-not

Registered User.
Local time
Today, 05:41
Joined
Sep 17, 2019
Messages
159
When I add the year also, it doesn't let me only select month or only the year though. It gives me an error message if both aren't selected. It give me that same problem I had before.

I want to have an option to show all the month and to show all the years for the user.

Code:
DoCmd.OpenReport "R_MA_audits_Total_Accuracy", acViewReport, , "[mth]=" & Forms!AuditAccuracy_Reports!cboMonth & " And [Yr]=" & Forms!AuditAccuracy_Reports!cboYear
 

plog

Banishment Pending
Local time
Today, 05:41
Joined
May 11, 2011
Messages
11,611
You have to dynamically build your string. Make a new variable and then add the criteria the user wants to it. That means testing the inputs from the form for values (IsNull()) and then adding it to the string if they have been input (pseudo code warning--the below will not exactly work, for demonstrating purposes):

str_Criteria="(1=1)"
if (IsNull(InputMonth)=False) str_Criteria = str_Criteria & " AND [Mnth]=" & InputMonth
DoCmd.OpenReport "R_MA_audits_Total_Accuracy", acViewReport, , str_Criteria
 

Db-why-not

Registered User.
Local time
Today, 05:41
Joined
Sep 17, 2019
Messages
159
You have to dynamically build your string. Make a new variable and then add the criteria the user wants to it. That means testing the inputs from the form for values (IsNull()) and then adding it to the string if they have been input (pseudo code warning--the below will not exactly work, for demonstrating purposes):

str_Criteria="(1=1)"
if (IsNull(InputMonth)=False) str_Criteria = str_Criteria & " AND [Mnth]=" & InputMonth
DoCmd.OpenReport "R_MA_audits_Total_Accuracy", acViewReport, , str_Criteria

I'm not sure If Im on the right track or not

This is what I have so far.
Code:
Dim cbMonth as String
Dim cbYear As String
Dim strMth As String
Dim strYr as String

cbMonth = Forms!AuditAccuracy_Reports!cboMonth
cbYear = Forms!AuditAccuracy_Reports!cboYear

If (IsNull(cbMonth) = False) Then
strMth = cbMonth 
Else strMth =" "
End If
If (IsNull(cbYear) = False) Then
strYr = cbYear 
Else strYr =" "
End If


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

DoCmd.OpenReport "R_MA_audits_Total_Accuracy", acViewReport, , "[mth]=" & strMth & " And [Yr]=" & cbYear
 

plog

Banishment Pending
Local time
Today, 05:41
Joined
May 11, 2011
Messages
11,611
Nope. You are still building the criteria string inside the DoCmd.OpenReport. That line should look exactly like this:

Code:
DoCmd.OpenReport "R_MA_audits_Total_Accuracy", acViewReport, , str_Criteria

str_Criteria is a variable and you will add to it the criteria the user selects. You do not need strMth and strYr variables.
 

Db-why-not

Registered User.
Local time
Today, 05:41
Joined
Sep 17, 2019
Messages
159
I forgot to change the last part. cbYear to strYr. Is that correct. I'm sorry I am still a novice with programming.
Code:
DoCmd.OpenReport "R_MA_audits_Total_Accuracy", acViewReport, , "[mth]=" & strMth & " And [Yr]=" & strYr
 

Db-why-not

Registered User.
Local time
Today, 05:41
Joined
Sep 17, 2019
Messages
159
[mth] and [yr} data I believe are string data.

I get those fields from month([datefield]) Year([datefield]) query

Should they be integer?
 

Gasman

Enthusiastic Amateur
Local time
Today, 10:41
Joined
Sep 21, 2011
Messages
14,038
[mth] and [yr} data I believe are string data.

I get those fields from month([datefield]) Year([datefield]) query

Should they be integer?
I would have thought so?, some form of numeric at least?
 

Db-why-not

Registered User.
Local time
Today, 05:41
Joined
Sep 17, 2019
Messages
159
Code:
Private Sub cmdGetReport_Click()

Dim cbMonth As Integer
Dim cbYear As String
Dim strMth As Integer
Dim strYr As String

cbMonth = Forms!AuditAccuracy_Reports!cboMonth
cbYear = Forms!AuditAccuracy_Reports!cboYear

If (IsNull(cbMonth) = False) Then
strMth = cbMonth
Else: strMth = " "
End If
If (IsNull(cbYear) = False) Then
strYr = cbYear
Else: strYr = " "
End If

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

DoCmd.OpenReport "R_MA_audits_Total_Accuracy", acViewReport, , "[mth]=" & strMth & " And [Yr]=" & strYr

I changed cbMonth to an Integer and it was working as an integer. When I changed Year to integer it gave me error messages.

Everything works if I select both month and year combo box. IT just gives me errors if only one box is selected. I think I will just add All Months to the combo box and all Years to combo box and set those values to show the values I want.

Error" Syntax error (missing operator) in query expression '[mth]=7 and [yr]='
Thats me selecting july for month and leaving year blank

How Do I set a variable to display all values for a query/ Where clause?
I want to show all months and all years. I don't know how to get that variable to show all the values I want to display, the following didnt work.

Code:
If (IsNull(cbMonth) = False) Then
strMth = cbMonth
Else: strMth = ">=1 and <=12"
End If
If (IsNull(cbYear) = False) Then
strYr = cbYear
Else: strYr = ">=2019 and<=2020"
End If
 

Gasman

Enthusiastic Amateur
Local time
Today, 10:41
Joined
Sep 21, 2011
Messages
14,038
You need to build the WHERE criteria depending on what control values are entered.?
What happens if you leave Month empty and enter a value for Year?

If you wanted all months and years, you would not supply any criteria and then you would get everything.?
 

Minty

AWF VIP
Local time
Today, 10:41
Joined
Jul 26, 2013
Messages
10,353
To illustrate what @plog was saying does this make sense;
Code:
Sub Test1()

    
    Dim cbMonth As Integer
    Dim cbYear As Integer
    Dim strCriteria As String

    cbMonth = Forms!AuditAccuracy_Reports!cboMonth
    cbYear = Forms!AuditAccuracy_Reports!cboYear

    strCriteria = "1=1"

    If cbMonth > 0 Then strCriteria = strCriteria & " AND [mth] = " & cbMonth

    If cbYear > 0 Then strCriteria = strCriteria & " AND [Yr] = " & cbYear
    
    Debug.Print strCriteria

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

        DoCmd.OpenReport "R_MA_audits_Total_Accuracy", acViewReport, , strCriteria
    
    End If


End Sub

As your criteria are defined as integers they won't be null, so test if they are greater than 0 (their default value) .
 

Db-why-not

Registered User.
Local time
Today, 05:41
Joined
Sep 17, 2019
Messages
159
To illustrate what @plog was saying does this make sense;
Code:
Sub Test1()

   
    Dim cbMonth As Integer
    Dim cbYear As Integer
    Dim strCriteria As String

    cbMonth = Forms!AuditAccuracy_Reports!cboMonth
    cbYear = Forms!AuditAccuracy_Reports!cboYear

    strCriteria = "1=1"

    If cbMonth > 0 Then strCriteria = strCriteria & " AND [mth] = " & cbMonth

    If cbYear > 0 Then strCriteria = strCriteria & " AND [Yr] = " & cbYear
   
    Debug.Print strCriteria

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

        DoCmd.OpenReport "R_MA_audits_Total_Accuracy", acViewReport, , strCriteria
   
    End If


End Sub

As your criteria are defined as integers they won't be null, so test if they are greater than 0 (their default value) .

I did what you said, but I got an error message still. Runtime error ' 13" Type mismatch.
Then the following code was highlighted:

Code:
cbMonth = Forms!AuditAccuracy_Reports!cboMonth

All the code that I have is :
Code:
Private Sub cmdGetReport_Click()

Dim cbMonth As Integer
Dim cbYear As Integer
Dim strCriteria As String

cbMonth = Forms!AuditAccuracy_Reports!cboMonth
cbYear = Forms!AuditAccuracy_Reports!cboYear
strCriteria = "1=1"

If cbMonth > 0 Then strCriteria = strCriteria & " AND [mth] = " & cbMonth

    If cbYear > 0 Then strCriteria = strCriteria & " AND [Yr] = " & cbYear
    
    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
1600450793018.png
 

Db-why-not

Registered User.
Local time
Today, 05:41
Joined
Sep 17, 2019
Messages
159
So my cboMonth combo box uses a table to get the data from there is a field with the number of the month 1-12, then the next field has the actual month January, February etc I also have a field where it has an actual date, 1/1/2020, 2/1/2020, 3/1/20. I'm just wondering if that might be causing the issue?
 

Users who are viewing this thread

Top Bottom