Filtering query all months with combo box (2 Viewers)

plog

Banishment Pending
Local time
Today, 11:35
Joined
May 11, 2011
Messages
11,611
Code:
   Dim cbMonth As Integer
...

    cbMonth = Forms!AuditAccuracy_Reports!cboMonth

I do not think Integers can be NULL in VBA. So when you set cbMonth to a NULL value you blow it up. I suggest getting rid of all your variables except strCriteria. Instead, just use Forms!AuditAccuracy_Reports!cboMonth instead of trying to assign it to a variable. Then, don't compare it to 0, do an IsNumeric() on it or test it for NULL.

 

Db-why-not

Registered User.
Local time
Today, 11:35
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
    
    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!!!!!!
 

plog

Banishment Pending
Local time
Today, 11:35
Joined
May 11, 2011
Messages
11,611
Nulls Null Nulls. Gotta test for them.
 

Db-why-not

Registered User.
Local time
Today, 11:35
Joined
Sep 17, 2019
Messages
159
I tried this for my label captions and its not displaying what I want.
Showing up as “1=1 AND [mth]”
Code:
Private Sub Report_Open(Cancel As Integer)

Dim strCriteria As String

strCriteria = "1=1"

If IsNumeric(Forms!AuditAccuracy_Reports!cboMonth) Then strCriteria = strCriteria & " AND [mth] = " & MonthName(Forms!AuditAccuracy_Reports!cboMonth)
    If IsNumeric(Forms!AuditAccuracy_Reports!cboYear) Then strCriteria = strCriteria & " AND [Yr] = " & Forms!AuditAccuracy_Reports!cboYear
        Debug.Print strCriteria
End If

Me.lbMonth.Caption = strCriteria
End Sub
 

Gasman

Enthusiastic Amateur
Local time
Today, 16:35
Joined
Sep 21, 2011
Messages
14,044
That will be beacuse of this line?

Code:
Me.lbMonth.Caption = strCriteria

What are you expecting/hoping to see?
 

Db-why-not

Registered User.
Local time
Today, 11:35
Joined
Sep 17, 2019
Messages
159
I got it working now.
Code:
Private Sub Report_Open(Cancel As Integer)

Dim strCriteriaMth As String
Dim strCriteriaYr As String

If (IsNumeric(Forms!AuditAccuracy_Reports!cboMonth) = True) Then
strCriteriaMth = MonthName(Forms!AuditAccuracy_Reports!cboMonth)
Else: strCriteriaMth = " "
End If
    
    If (IsNumeric(Forms!AuditAccuracy_Reports!cboYear) = True) Then
    strCriteriaYr = Forms!AuditAccuracy_Reports!cboYear
    Else: strCriteriaYr = " "
 End If
 
Me.lbMonth.Caption = strCriteriaMth & " " & strCriteriaYr

End Sub
 

Db-why-not

Registered User.
Local time
Today, 11:35
Joined
Sep 17, 2019
Messages
159
So I am trying to modify this code to use for a different form to filter for 3 different combo boxes. I want to filter for month, year and name of Technician. I want a option to filter by "All technicians" I set the form so it automatically filters for current month, current year and "all technicians".
I am getting error messages though.

Code:
Private Sub Form_Load()
'Sets the filter comboboxes
Me.cboMonth = Month(Now())
Me.cboYear = Year(Now())
Me.cboTechnician = "All Technicians"

Dim strCriteria As String
strCriteria = "1=1"

If Me.cboTechnician = "All Technicians" Then
  strCriteria = strCriteria & " AND  [Name]=" & Me.cboTechnician & " AND
[Yr] = " & Me.cboYear & "   AND [mth] = " & Me.cboMonth
Debug.Print strCriteria
Else
strCriteria = [name] = " & Me.cboTechnician & " And [Yr] = " & Me.cboYear &
" And [mth] = " & Me.cboMonth"
Debug.Print strCriteria
End If

Me.Filter = strCriteria
Me.FilterOn = True
Me.Requery

End Sub

Error Message I am getting is Runtime error 3075
syntax error in query expression ; 1=1 and [name]=All technicians and [yr]= 2020 and [mth] =1

My form is a continuous form.

Any help on fixing the code. OR is there a better way to do this? Thanks.
 

Attachments

  • error message.PNG
    error message.PNG
    5.4 KB · Views: 132
  • vba.PNG
    vba.PNG
    18.8 KB · Views: 145

theDBguy

I’m here to help
Staff member
Local time
Today, 09:35
Joined
Oct 29, 2018
Messages
21,358
So I am trying to modify this code to use for a different form to filter for 3 different combo boxes. I want to filter for month, year and name of Technician. I want a option to filter by "All technicians" I set the form so it automatically filters for current month, current year and "all technicians".
I am getting error messages though.

Code:
Private Sub Form_Load()
'Sets the filter comboboxes
Me.cboMonth = Month(Now())
Me.cboYear = Year(Now())
Me.cboTechnician = "All Technicians"

Dim strCriteria As String
strCriteria = "1=1"

If Me.cboTechnician = "All Technicians" Then
  strCriteria = strCriteria & " AND  [Name]=" & Me.cboTechnician & " AND
[Yr] = " & Me.cboYear & "   AND [mth] = " & Me.cboMonth
Debug.Print strCriteria
Else
strCriteria = [name] = " & Me.cboTechnician & " And [Yr] = " & Me.cboYear &
" And [mth] = " & Me.cboMonth"
Debug.Print strCriteria
End If

Me.Filter = strCriteria
Me.FilterOn = True
Me.Requery

End Sub

Error Message I am getting is Runtime error 3075
syntax error in query expression ; 1=1 and [name]=All technicians and [yr]= 2020 and [mth] =1

My form is a continuous form.

Any help on fixing the code. OR is there a better way to do this? Thanks.
Hi. The reason for initially setting strCriteria to "1=1" was so that you don't have to create a criteria when there isn't one asked for by the user. So, I would try something like this instead.
Code:
strCriteria = " 1=1 "

If Me.cboYear > "" Then
    strCriteria = strCriteria & " AND [yr] = " & Me.cboYear
End If

If Me.cboMonth > "" Then
    strCriteria = strCriteria & " AND [mth]=" & Me.cboMonth
End If

If Me.cboTechnician > "" AND Me.cboTechnician <> "All Technicians" Then
    strCriteria = strCriteria & " AND [name]='" & Me.cboTechnician & "'"
End If
Hope that helps...

PS. I assumed the technician's name field is a Text, so I added the delimiters. If it's a Number field, just take out the delimiters, like you had in your original code. Cheers!
 
Last edited:

plog

Banishment Pending
Local time
Today, 11:35
Joined
May 11, 2011
Messages
11,611
With each new post you change this thing just a little, which is fine, but you really need to understand what it is we are saying instead of just implementing code. That way you can continue to make your little tweaks.

With the last code you posted there will always be values for your 3 fields (current month, current year, 'All Technicians') and month and year will always be used as criteria. That means we can replace str_Criteria="1=1" with code for year/month and just test the name for 'All Technicians' and add it fi necessary. So your code for strCriteria will look like this (warning psuedo code below, used to demonstrate what you need to do):

Code:
strCriteria=(month criteria) & (year criteria)
  ' default criteria, will always include month and year

If (cobTechnician=='All Technicians) Then strCriteria = strCriteria & (name criteria)
  ' if specific technician is used then we will add it to the criteria, if not leave it off

Again, you've hard coded your values into that function so we know what all 3 of those values are going to be on the Load of the form. So theres no real reason to test for anything because those values are defined by the function itself. This cannot be the final code you need. Maybe we should step back and you should explain what it is you ultimately want to accomplish and we can work on that.

My guess is DBguys code will be closer to what you want in the end.
 

Db-why-not

Registered User.
Local time
Today, 11:35
Joined
Sep 17, 2019
Messages
159
Hi. The reason for initially setting strCriteria to "1=1" was so that you don't have to create a criteria when there isn't one asked for by the user. So, I would try something like this instead.
Code:
strCriteria = " 1=1 "

If Me.cboYear > "" Then
    strCriteria = strCriteria & " AND [yr] = " & Me.cboYear
End If

If Me.cboMonth > "" Then
    strCriteria = strCriteria & " AND [mth]=" & Me.cboMonth
End If

If Me.cboTechnician > "" AND Me.cboTechnician <> "All Technicians" Then
    strCriteria = strCriteria & " AND [name]='" & Me.cboTechnician & "'"
End If
Hope that helps...

PS. I assumed the technician's name field is a Text, so I added the delimiters. If it's a Number field, just take out the delimiters, like you had in your original code. Cheers!
So I did what you said. It seems to open my form correctly. When I use the combo boxes filters for month and year it works, but When I try to use the combo box for the cboTechnicans it doesn't filter the data properly anymore. cboTechnician drop down box gets it data from a table it has a list of names of technicians. I also added All Technicians to the list in the table. If All technicians is selected from drop down box I want it to show all technicians names. IF someone selects "John Doe" name from the cboTechnican drop down box, I only want John Doe to show up for my [name] field. Once I start using the cboTechnician drop down box it stops working and doesnt show any data anymore, even if I put the selection back to "All Technicians" it still shows no data. I have it where when you select the filter button it should refilter and requery the data.

In my query for the data I already have the [Name] field being queryed where it only shows the names of Technician who is not currently logged into the access database. I don't want the person that is logged in to see records they worked on with their name. I tried taking that off the query because I thought that might be causing the issue but it still had the problem when I removed that.
The way that I have the drop down boxes is something will always be selected for each of them.

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));



Code:
private sub cmdFilter_Click

Dim strCriteria As String
strCriteria = "1=1"

If Me.cboYear > " " Then
  strCriteria = strCriteria & " AND [Yr] = " & Me.cboYear
End If

If Me.cboMonth > " " Then
  strCriteria = strCriteria & " AND [Mth] = " & Me.cboMonth
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
 

theDBguy

I’m here to help
Staff member
Local time
Today, 09:35
Joined
Oct 29, 2018
Messages
21,358
cboTechnician drop down box gets it data from a table it has a list of names of technicians. I also added All Technicians to the list in the table.
Can you please post the Row Source of the combobox? Thanks.
 

Db-why-not

Registered User.
Local time
Today, 11:35
Joined
Sep 17, 2019
Messages
159
Can you please post the Row Source of the combobox? Thanks.
Rowsource SELECT [tblUser].[lUserID], [tblUser].[szUser] FROM tblUser ORDER BY [szUser];

Bound to column 1

When I change Bound Column to 2 then it starts working whenever I select "All Technicians" but doesn't work for any of the other names selected.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 09:35
Joined
Oct 29, 2018
Messages
21,358
Rowsource SELECT [tblUser].[lUserID], [tblUser].[szUser] FROM tblUser ORDER BY [szUser];

Bound to column 1

When I change Bound Column to 2 then it starts working whenever I select "All Technicians" but doesn't work for any of the other names selected.
Hi. Can you tell us what is the UserID for the user "All Technicians?" Thanks.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 09:35
Joined
Oct 29, 2018
Messages
21,358
its just a number 31
Okay, then try changing this line to:
Code:
If Me.cboTechnician > "" AND Me.cboTechnician <> 31 Then
    strCriteria = strCriteria & " AND [UserID]=" Me.cboTechnician
End If
 

Db-why-not

Registered User.
Local time
Today, 11:35
Joined
Sep 17, 2019
Messages
159
Okay, then try changing this line to:
Code:
If Me.cboTechnician > "" AND Me.cboTechnician <> 31 Then
    strCriteria = strCriteria & " AND [UserID]=" Me.cboTechnician
End If
It doesn't show any data when I do that.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 09:35
Joined
Oct 29, 2018
Messages
21,358
It doesn't show any data when I do that.
Let's double-check... Comment out the above section, and in it's place, just use this:

MsgBox Me.cboTechnician

Then, select "All Technicians" from the combo and tell us what you get in the message box.
 

Db-why-not

Registered User.
Local time
Today, 11:35
Joined
Sep 17, 2019
Messages
159
Let's double-check... Comment out the above section, and in it's place, just use this:

MsgBox Me.cboTechnician

Then, select "All Technicians" from the combo and tell us what you get in the message box.
So I did
If Me.cboTechnician > "" AND Me.cboTechnician <> 31 Then
'strCriteria = strCriteria & " AND [UserID]=" Me.cboTechnician
MsgBox Me.cboTechnician

and I got the message box that said All Technicians when I have all technicians selected and when I hit the filter button. If I selected a different name in the cbo box the message box says the name I select.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 09:35
Joined
Oct 29, 2018
Messages
21,358
So I did
If Me.cboTechnician > "" AND Me.cboTechnician <> 31 Then
'strCriteria = strCriteria & " AND [UserID]=" Me.cboTechnician
MsgBox Me.cboTechnician

and I got the message box that said All Technicians when I have all technicians selected and when I hit the filter button. If I selected a different name in the cbo box the message box says the name I select.
Interesting. You may have to post a sample db for us to look into.

Can you post the Record Source for your Form? I want to see the SQL statement. So, it's a query, pleas post the SQL. If it's a table, please post the field list.
 

Db-why-not

Registered User.
Local time
Today, 11:35
Joined
Sep 17, 2019
Messages
159
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.
 

Users who are viewing this thread

Top Bottom