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.
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
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
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.
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!
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.
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
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.
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.
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.