Like '*' or Is Null

[Ann]

I am using Access 2002. I'm not a programmer but can alter code I find. I
have the following code listed below on a button to filter the data depending
on what data the user picks from the combo boxes.

The problem I'm having is sometimes there isn't any data in a field so the
Like '*' keeps me from getting the correct result. I need to somehow tell it
that any field that was not chosen by picking from the combo box could be
Like '*' or it could be null. Thanks in advance for the help.

Private Sub cmdApplyFilter_Click()

'Check if report is closed.
'If the report is closed open the report after clicking the OK button.
If SysCmd(acSysCmdGetObjectState, acReport, "rptClassesFilter") <> _
acObjStateOpen Then
MsgBox "You must open the report first."

Dim stDocName As String

stDocName = "rptClassesFilter"
DoCmd.OpenReport stDocName, acPreview

Exit Sub
End If

'Declare variables
Dim strBrochureType As String
Dim strBuildingLocation As String
Dim strPrimaryCompetency As String
Dim strSecondaryCompetency As String
Dim strSupplementalCompetency As String
Dim strDepartmentType As String
Dim strInstructorName As String
Dim strTrainingRoom As String
Dim strWorkLifeType As String
Dim strFilter As String

'Brochure Type
If IsNull(Me.cboBrochure.Value) Then
strBrochureType = "Like '*'"
Else
strBrochureType = "='" & Me.cboBrochure.Value & "'"
End If

'Building Location
If IsNull(Me.cboBuildingLocation.Value) Then
strBuildingLocation = "Like '*'"
Else
strBuildingLocation = "='" & Me.cboBuildingLocation.Value & "'"
End If

'Primary Competency
If IsNull(Me.cboPrimaryCompetency.Value) Then
strPrimaryCompetency = "Like '*'"
Else
strPrimaryCompetency = "='" & Me.cboPrimaryCompetency.Value & "'"
End If

'Secondary Competency
If IsNull(Me.cboSecondaryCompetency.Value) Then
strSecondaryCompetency = "Like '*'"
Else
strSecondaryCompetency = "='" & Me.cboSecondaryCompetency.Value & "'"
End If

'Supplemental Competency
If IsNull(Me.cboSupplementalCompetency.Value) Then
strSupplementalCompetency = "Like '*'"
Else
strSupplementalCompetency = "='" &
Me.cboSupplementalCompetency.Value & "'"
End If

'Department
If IsNull(Me.cboDepartment.Value) Then
strDepartmentType = "Like '*'"
Else
strDepartmentType = "='" & Me.cboDepartment.Value & "'"
End If

'Instructor Name
If IsNull(Me.cboInstructors.Value) Then
strInstructorName = "Like '*'"
Else
strInstructorName = "='" & Me.cboInstructors.Value & "'"
End If

'Training Room
If IsNull(Me.cboTrainingRoom.Value) Then
strTrainingRoom = "Like '*'"
Else
strTrainingRoom = "='" & Me.cboTrainingRoom.Value & "'"
End If

'Work Life
If IsNull(Me.cboWorkLife.Value) Then
strWorkLifeType = "Like '*'"
Else
strWorkLifeType = "='" & Me.cboWorkLife.Value & "'"
End If

'Filter the string for each declared variable

strFilter = "[txtBrochureType] " & strBrochureType & _
" AND [txtBuildingLocation] " & strBuildingLocation & _
" AND [txtPrimaryCompetency] " & strPrimaryCompetency & _
" AND [txtSecondaryCompetency] " & strSecondaryCompetency & _
" AND [txtSupplementalCompetency] " & strSupplementalCompetency & _
" AND [txtDepartmentType] " & strDepartmentType & _
" AND [lngInstructorID] " & strInstructorName & _
" AND [txtTrainingRoom] " & strTrainingRoom & _
" AND [txtWorkLifeType] " & strWorkLifeType

'Filter Property is turned on
With Reports![rptClassesFilter]
.Filter = strFilter
.FilterOn = True
End With

End Sub

 

[Marshall Barton]

I am using Access 2002. I'm not a programmer but can alter code I find. I
have the following code listed below on a button to filter the data depending
on what data the user picks from the combo boxes.

The problem I'm having is sometimes there isn't any data in a field so the
Like '*' keeps me from getting the correct result. I need to somehow tell it
that any field that was not chosen by picking from the combo box could be
Like '*' or it could be null.

[snip]

It would be way better to not have a criteria when the user
does not specify it, The "standard" approach uses code more
like:

Dim strFilter As String

'Brochure Type
If Not IsNull(Me.cboBrochure) Then
strFilter = " AND BrochureType='" & Me.cboBrochure &
"'"
End If

'Building Location
If Not IsNull(Me.cboBuildingLocation) Then
strFilter = " AND BuildingLocation='" &
Me.cboBuildingLocation & "'"
End If
. . .

'If the report is closed, open the report
If SysCmd(acSysCmdGetObjectState, acReport,
"rptClassesFilter") <> acObjStateOpen Then
DoCmd.OpenReport "rptClassesFilter", acPreview, _
WhereCondition:= Mid(strFilter, 6)
Else 'if report was open, use filter
With Reports![rptClassesFilter]
.Filter = Mid(strFilter, 6)
.FilterOn = True
End With
End If

End Sub

 

[Ann]

Hi, thank you for helping me. I added the code to the button and it worked
fine for Brochure Type by itself and Building Location by itself but it
didn't work when I tried to use them together. It still only gave me the
records for Building Location. It didn't string everything together.

I am using Access 2002. I'm not a programmer but can alter code I find. I
have the following code listed below on a button to filter the data depending
on what data the user picks from the combo boxes.

The problem I'm having is sometimes there isn't any data in a field so the
Like '*' keeps me from getting the correct result. I need to somehow tell it
that any field that was not chosen by picking from the combo box could be
Like '*' or it could be null.

[snip]

It would be way better to not have a criteria when the user
does not specify it, The "standard" approach uses code more
like:

Dim strFilter As String

'Brochure Type
If Not IsNull(Me.cboBrochure) Then
strFilter = " AND BrochureType='" & Me.cboBrochure &
"'"
End If

'Building Location
If Not IsNull(Me.cboBuildingLocation) Then
strFilter = " AND BuildingLocation='" &
Me.cboBuildingLocation & "'"
End If
. . .

'If the report is closed, open the report
If SysCmd(acSysCmdGetObjectState, acReport,
"rptClassesFilter") <> acObjStateOpen Then
DoCmd.OpenReport "rptClassesFilter", acPreview, _
WhereCondition:= Mid(strFilter, 6)
Else 'if report was open, use filter
With Reports![rptClassesFilter]
.Filter = Mid(strFilter, 6)
.FilterOn = True
End With
End If

End Sub

 

[Marshall Barton]

Sorry, that's a common typo for me to make. You'd think I
would have learned by now.

They all should be like:

strFilter = strFilter & " AND BrochureType='" &
Me.cboBrochure & "' "

Note that your code is written as all the fields are Text
fields.

For a number type field, the line for would be like:
strFilter = strFilter & " AND numberfield=" & Me.cbonumbers

and for date fields:
strFilter = strFilter & " AND datefield='" & _
Format(Me.cbodates, "\#yyyy-m-d\#")
--
Marsh
MVP [MS Access]

Hi, thank you for helping me. I added the code to the button and it worked
fine for Brochure Type by itself and Building Location by itself but it
didn't work when I tried to use them together. It still only gave me the
records for Building Location. It didn't string everything together.

I am using Access 2002. I'm not a programmer but can alter code I find. I
have the following code listed below on a button to filter the data depending
on what data the user picks from the combo boxes.

The problem I'm having is sometimes there isn't any data in a field so the
Like '*' keeps me from getting the correct result. I need to somehow tell it
that any field that was not chosen by picking from the combo box could be
Like '*' or it could be null.

[snip]

It would be way better to not have a criteria when the user
does not specify it, The "standard" approach uses code more
like:

Dim strFilter As String

'Brochure Type
If Not IsNull(Me.cboBrochure) Then
strFilter = " AND BrochureType='" & Me.cboBrochure &
"'"
End If

'Building Location
If Not IsNull(Me.cboBuildingLocation) Then
strFilter = " AND BuildingLocation='" &
Me.cboBuildingLocation & "'"
End If
. . .

'If the report is closed, open the report
If SysCmd(acSysCmdGetObjectState, acReport,
"rptClassesFilter") <> acObjStateOpen Then
DoCmd.OpenReport "rptClassesFilter", acPreview, _
WhereCondition:= Mid(strFilter, 6)
Else 'if report was open, use filter
With Reports![rptClassesFilter]
.Filter = Mid(strFilter, 6)
.FilterOn = True
End With
End If

End Sub

 

[Ann]

Hi Marshall,

Thanks so much, that worked great!! Plus, it's so much easier then what I
had to begin with.

Also, thanks for the number and date code. I'm going to add it to my file.
I know I'll definitely use it again.

Sorry, that's a common typo for me to make. You'd think I
would have learned by now.

They all should be like:

strFilter = strFilter & " AND BrochureType='" &
Me.cboBrochure & "' "

Note that your code is written as all the fields are Text
fields.

For a number type field, the line for would be like:
strFilter = strFilter & " AND numberfield=" & Me.cbonumbers

and for date fields:
strFilter = strFilter & " AND datefield='" & _
Format(Me.cbodates, "\#yyyy-m-d\#")
--
Marsh
MVP [MS Access]

Hi, thank you for helping me. I added the code to the button and it worked
fine for Brochure Type by itself and Building Location by itself but it
didn't work when I tried to use them together. It still only gave me the
records for Building Location. It didn't string everything together.

Ann wrote:
I am using Access 2002. I'm not a programmer but can alter code I find. I
have the following code listed below on a button to filter the data depending
on what data the user picks from the combo boxes.

The problem I'm having is sometimes there isn't any data in a field so the
Like '*' keeps me from getting the correct result. I need to somehow tell it
that any field that was not chosen by picking from the combo box could be
Like '*' or it could be null.

[snip]

It would be way better to not have a criteria when the user
does not specify it, The "standard" approach uses code more
like:

Dim strFilter As String

'Brochure Type
If Not IsNull(Me.cboBrochure) Then
strFilter = " AND BrochureType='" & Me.cboBrochure &
"'"
End If

'Building Location
If Not IsNull(Me.cboBuildingLocation) Then
strFilter = " AND BuildingLocation='" &
Me.cboBuildingLocation & "'"
End If
. . .

'If the report is closed, open the report
If SysCmd(acSysCmdGetObjectState, acReport,
"rptClassesFilter") <> acObjStateOpen Then
DoCmd.OpenReport "rptClassesFilter", acPreview, _
WhereCondition:= Mid(strFilter, 6)
Else 'if report was open, use filter
With Reports![rptClassesFilter]
.Filter = Mid(strFilter, 6)
.FilterOn = True
End With
End If

End Sub

.

 

[Ann]

Hi Marshall,

I'm having a problem with the report and was hoping you would still help me.
It was working so well I added a few more combo boxes. On occassion, even
though I have cleared out all the combo boxes choices, I will get an
incorrect result.

For example. If I choose a Course Title, Course 1, and apply the filter I
get the report for Course 1. Then I click the clear filter button to wipe out
my choice and pick another one, Course 2. When I apply the filter I am
getting a blank report because the new filter that was applied is asking for
Course 1 and Course 2.

I tried adding a line to clear the Filter property from the report but it
didnt' seem to work. Here is the code:

Dim strFilter As String

'Course Title
If Not IsNull(Me.cboCourse) Then
strFilter = strFilter & " AND txtCourseTitle=""" & Me.cboCourse &
""" "
End If

'Vendor
If Not IsNull(Me.cboVendor) Then
strFilter = strFilter & " AND txtVendorName=""" & Me.cboVendor & """ "
End If

'Quarter
If Not IsNull(Me.cboQuarter) Then
strFilter = strFilter & " AND txtQuarter='" & Me.cboQuarter & "' "
End If

'Brochure Type
If Not IsNull(Me.cboBrochure) Then
strFilter = strFilter & " AND txtBrochureType='" & Me.cboBrochure &
"' "
End If

'Building Location
If Not IsNull(Me.cboBuildingLocation) Then
strFilter = strFilter & " AND txtBuildingLocation='" &
Me.cboBuildingLocation & "'"
End If

'Primary Competency
If Not IsNull(Me.cboPrimaryCompetency) Then
strFilter = strFilter & " AND txtPrimaryCompetency='" &
Me.cboPrimaryCompetency & "'"
End If

'Secondary Competency
If Not IsNull(Me.cboSecondaryCompetency) Then
strFilter = strFilter & " AND txtSecondaryCompetency='" &
Me.cboSecondaryCompetency & "'"
End If

'Supplemental Competency
If Not IsNull(Me.cboSupplementalCompetency) Then
strFilter = strFilter & " AND txtSupplementalCompetency='" &
Me.cboSupplementalCompetency & "'"
End If

'Department
If Not IsNull(Me.cboDepartment) Then
strFilter = strFilter & " AND txtDepartmentType='" &
Me.cboDepartment & "'"
End If
'Instructors
If Not IsNull(Me.cboInstructors) Then
strFilter = strFilter & " AND txtInstructorName='" &
Me.cboInstructors & "'"
End If

'Training Room
If Not IsNull(Me.cboTrainingRoom) Then
strFilter = strFilter & " AND txtTrainingRoom='" &
Me.cboTrainingRoom & "'"
End If

'Work-Life
If Not IsNull(Me.cboWorkLife) Then
strFilter = strFilter & " AND txtWorkLifeType='" & Me.cboWorkLife &
"'"
End If

'If the report is closed, open the report
If SysCmd(acSysCmdGetObjectState, acReport, "rptClassesFilter") <>
acObjStateOpen Then
DoCmd.OpenReport "rptClassesFilter", acPreview, _
WhereCondition:=Mid(strFilter, 6)
Else 'if report was open, use filter
With Reports![rptClassesFilter]
.Filter = Mid(strFilter, 6)
.FilterOn = True
End With
End If

End Sub

Private Sub cmdRemoveFilter_Click()

'Remove all values from the combo boxes.
On Error Resume Next

Me.cboCourse = Null
Me.cboVendor = Null
Me.cboQuarter = Null
Me.cboBrochure = Null
Me.cboBuildingLocation = Null
Me.cboPrimaryCompetency = Null
Me.cboSecondaryCompetency = Null
Me.cboSupplementalCompetency = Null
Me.cboDepartment = Null
Me.cboInstructors = Null
Me.cboTrainingRoom = Null
Me.cboWorkLife = Null

End Sub

Sorry, that's a common typo for me to make. You'd think I
would have learned by now.

They all should be like:

strFilter = strFilter & " AND BrochureType='" &
Me.cboBrochure & "' "

Note that your code is written as all the fields are Text
fields.

For a number type field, the line for would be like:
strFilter = strFilter & " AND numberfield=" & Me.cbonumbers

and for date fields:
strFilter = strFilter & " AND datefield='" & _
Format(Me.cbodates, "\#yyyy-m-d\#")
--
Marsh
MVP [MS Access]

Hi, thank you for helping me. I added the code to the button and it worked
fine for Brochure Type by itself and Building Location by itself but it
didn't work when I tried to use them together. It still only gave me the
records for Building Location. It didn't string everything together.

Ann wrote:
I am using Access 2002. I'm not a programmer but can alter code I find. I
have the following code listed below on a button to filter the data depending
on what data the user picks from the combo boxes.

The problem I'm having is sometimes there isn't any data in a field so the
Like '*' keeps me from getting the correct result. I need to somehow tell it
that any field that was not chosen by picking from the combo box could be
Like '*' or it could be null.

[snip]

It would be way better to not have a criteria when the user
does not specify it, The "standard" approach uses code more
like:

Dim strFilter As String

'Brochure Type
If Not IsNull(Me.cboBrochure) Then
strFilter = " AND BrochureType='" & Me.cboBrochure &
"'"
End If

'Building Location
If Not IsNull(Me.cboBuildingLocation) Then
strFilter = " AND BuildingLocation='" &
Me.cboBuildingLocation & "'"
End If
. . .

'If the report is closed, open the report
If SysCmd(acSysCmdGetObjectState, acReport,
"rptClassesFilter") <> acObjStateOpen Then
DoCmd.OpenReport "rptClassesFilter", acPreview, _
WhereCondition:= Mid(strFilter, 6)
Else 'if report was open, use filter
With Reports![rptClassesFilter]
.Filter = Mid(strFilter, 6)
.FilterOn = True
End With
End If

End Sub

.

 

[Marshall Barton]

The code looks ok so I'm wondering if the WhereCondition is
being retained even after the Filter property is changed. I
don't have time to set up a test scenario right now, but you
xan try an experiment. There may be some screen flashing,
but try changing the code to:

If SysCmd(acSysCmdGetObjectState, acReport,
"rptClassesFilter") <> acObjStateOpen Then
DoCmd.OpenReport "rptClassesFilter", acPreview
End If
With Reports![rptClassesFilter]
.Filter = Mid(strFilter, 6)
.FilterOn = True
End With
--
Marsh
MVP [MS Access]

I'm having a problem with the report and was hoping you would still help me.
It was working so well I added a few more combo boxes. On occassion, even
though I have cleared out all the combo boxes choices, I will get an
incorrect result.

For example. If I choose a Course Title, Course 1, and apply the filter I
get the report for Course 1. Then I click the clear filter button to wipe out
my choice and pick another one, Course 2. When I apply the filter I am
getting a blank report because the new filter that was applied is asking for
Course 1 and Course 2.

I tried adding a line to clear the Filter property from the report but it
didnt' seem to work. Here is the code:

Dim strFilter As String

'Course Title
If Not IsNull(Me.cboCourse) Then
strFilter = strFilter & " AND txtCourseTitle=""" & Me.cboCourse &
""" "
End If

'Vendor
If Not IsNull(Me.cboVendor) Then
strFilter = strFilter & " AND txtVendorName=""" & Me.cboVendor & """ "
End If

'Quarter
If Not IsNull(Me.cboQuarter) Then
strFilter = strFilter & " AND txtQuarter='" & Me.cboQuarter & "' "
End If

'Brochure Type
If Not IsNull(Me.cboBrochure) Then
strFilter = strFilter & " AND txtBrochureType='" & Me.cboBrochure &
"' "
End If

'Building Location
If Not IsNull(Me.cboBuildingLocation) Then
strFilter = strFilter & " AND txtBuildingLocation='" &
Me.cboBuildingLocation & "'"
End If

'Primary Competency
If Not IsNull(Me.cboPrimaryCompetency) Then
strFilter = strFilter & " AND txtPrimaryCompetency='" &
Me.cboPrimaryCompetency & "'"
End If

'Secondary Competency
If Not IsNull(Me.cboSecondaryCompetency) Then
strFilter = strFilter & " AND txtSecondaryCompetency='" &
Me.cboSecondaryCompetency & "'"
End If

'Supplemental Competency
If Not IsNull(Me.cboSupplementalCompetency) Then
strFilter = strFilter & " AND txtSupplementalCompetency='" &
Me.cboSupplementalCompetency & "'"
End If

'Department
If Not IsNull(Me.cboDepartment) Then
strFilter = strFilter & " AND txtDepartmentType='" &
Me.cboDepartment & "'"
End If
'Instructors
If Not IsNull(Me.cboInstructors) Then
strFilter = strFilter & " AND txtInstructorName='" &
Me.cboInstructors & "'"
End If

'Training Room
If Not IsNull(Me.cboTrainingRoom) Then
strFilter = strFilter & " AND txtTrainingRoom='" &
Me.cboTrainingRoom & "'"
End If

'Work-Life
If Not IsNull(Me.cboWorkLife) Then
strFilter = strFilter & " AND txtWorkLifeType='" & Me.cboWorkLife &
"'"
End If

'If the report is closed, open the report
If SysCmd(acSysCmdGetObjectState, acReport, "rptClassesFilter") <>
acObjStateOpen Then
DoCmd.OpenReport "rptClassesFilter", acPreview, _
WhereCondition:=Mid(strFilter, 6)
Else 'if report was open, use filter
With Reports![rptClassesFilter]
.Filter = Mid(strFilter, 6)
.FilterOn = True
End With
End If

End Sub

Private Sub cmdRemoveFilter_Click()

'Remove all values from the combo boxes.
On Error Resume Next

Me.cboCourse = Null
Me.cboVendor = Null
Me.cboQuarter = Null
Me.cboBrochure = Null
Me.cboBuildingLocation = Null
Me.cboPrimaryCompetency = Null
Me.cboSecondaryCompetency = Null
Me.cboSupplementalCompetency = Null
Me.cboDepartment = Null
Me.cboInstructors = Null
Me.cboTrainingRoom = Null
Me.cboWorkLife = Null

End Sub

 

[Ann]

Hi Marshall,

Thanks for taking the time I know you must be very busy. I did make the
change and that seemed to do the trick. I ran a number of tests without it
happening.

Thanks again and have a great day!

The code looks ok so I'm wondering if the WhereCondition is
being retained even after the Filter property is changed. I
don't have time to set up a test scenario right now, but you
xan try an experiment. There may be some screen flashing,
but try changing the code to:

If SysCmd(acSysCmdGetObjectState, acReport,
"rptClassesFilter") <> acObjStateOpen Then
DoCmd.OpenReport "rptClassesFilter", acPreview
End If
With Reports![rptClassesFilter]
.Filter = Mid(strFilter, 6)
.FilterOn = True
End With
--
Marsh
MVP [MS Access]

I'm having a problem with the report and was hoping you would still help me.
It was working so well I added a few more combo boxes. On occassion, even
though I have cleared out all the combo boxes choices, I will get an
incorrect result.

For example. If I choose a Course Title, Course 1, and apply the filter I
get the report for Course 1. Then I click the clear filter button to wipe out
my choice and pick another one, Course 2. When I apply the filter I am
getting a blank report because the new filter that was applied is asking for
Course 1 and Course 2.

I tried adding a line to clear the Filter property from the report but it
didnt' seem to work. Here is the code:

Dim strFilter As String

'Course Title
If Not IsNull(Me.cboCourse) Then
strFilter = strFilter & " AND txtCourseTitle=""" & Me.cboCourse &
""" "
End If

'Vendor
If Not IsNull(Me.cboVendor) Then
strFilter = strFilter & " AND txtVendorName=""" & Me.cboVendor & """ "
End If

'Quarter
If Not IsNull(Me.cboQuarter) Then
strFilter = strFilter & " AND txtQuarter='" & Me.cboQuarter & "' "
End If

'Brochure Type
If Not IsNull(Me.cboBrochure) Then
strFilter = strFilter & " AND txtBrochureType='" & Me.cboBrochure &
"' "
End If

'Building Location
If Not IsNull(Me.cboBuildingLocation) Then
strFilter = strFilter & " AND txtBuildingLocation='" &
Me.cboBuildingLocation & "'"
End If

'Primary Competency
If Not IsNull(Me.cboPrimaryCompetency) Then
strFilter = strFilter & " AND txtPrimaryCompetency='" &
Me.cboPrimaryCompetency & "'"
End If

'Secondary Competency
If Not IsNull(Me.cboSecondaryCompetency) Then
strFilter = strFilter & " AND txtSecondaryCompetency='" &
Me.cboSecondaryCompetency & "'"
End If

'Supplemental Competency
If Not IsNull(Me.cboSupplementalCompetency) Then
strFilter = strFilter & " AND txtSupplementalCompetency='" &
Me.cboSupplementalCompetency & "'"
End If

'Department
If Not IsNull(Me.cboDepartment) Then
strFilter = strFilter & " AND txtDepartmentType='" &
Me.cboDepartment & "'"
End If
'Instructors
If Not IsNull(Me.cboInstructors) Then
strFilter = strFilter & " AND txtInstructorName='" &
Me.cboInstructors & "'"
End If

'Training Room
If Not IsNull(Me.cboTrainingRoom) Then
strFilter = strFilter & " AND txtTrainingRoom='" &
Me.cboTrainingRoom & "'"
End If

'Work-Life
If Not IsNull(Me.cboWorkLife) Then
strFilter = strFilter & " AND txtWorkLifeType='" & Me.cboWorkLife &
"'"
End If

'If the report is closed, open the report
If SysCmd(acSysCmdGetObjectState, acReport, "rptClassesFilter") <>
acObjStateOpen Then
DoCmd.OpenReport "rptClassesFilter", acPreview, _
WhereCondition:=Mid(strFilter, 6)
Else 'if report was open, use filter
With Reports![rptClassesFilter]
.Filter = Mid(strFilter, 6)
.FilterOn = True
End With
End If

End Sub

Private Sub cmdRemoveFilter_Click()

'Remove all values from the combo boxes.
On Error Resume Next

Me.cboCourse = Null
Me.cboVendor = Null
Me.cboQuarter = Null
Me.cboBrochure = Null
Me.cboBuildingLocation = Null
Me.cboPrimaryCompetency = Null
Me.cboSecondaryCompetency = Null
Me.cboSupplementalCompetency = Null
Me.cboDepartment = Null
Me.cboInstructors = Null
Me.cboTrainingRoom = Null
Me.cboWorkLife = Null

End Sub

.

 

[Marshall Barton]

Glad to hear it worked. I appreciate you running the test
and posting the result so we all know the final solution to
your question.
--
Marsh
MVP [MS Access]

Thanks for taking the time I know you must be very busy. I did make the
change and that seemed to do the trick. I ran a number of tests without it
happening.

The code looks ok so I'm wondering if the WhereCondition is
being retained even after the Filter property is changed. I
don't have time to set up a test scenario right now, but you
xan try an experiment. There may be some screen flashing,
but try changing the code to:

If SysCmd(acSysCmdGetObjectState, acReport,
"rptClassesFilter") <> acObjStateOpen Then
DoCmd.OpenReport "rptClassesFilter", acPreview
End If
With Reports![rptClassesFilter]
.Filter = Mid(strFilter, 6)
.FilterOn = True
End With

I'm having a problem with the report and was hoping you would still help me.
It was working so well I added a few more combo boxes. On occassion, even
though I have cleared out all the combo boxes choices, I will get an
incorrect result.

For example. If I choose a Course Title, Course 1, and apply the filter I
get the report for Course 1. Then I click the clear filter button to wipe out
my choice and pick another one, Course 2. When I apply the filter I am
getting a blank report because the new filter that was applied is asking for
Course 1 and Course 2.

I tried adding a line to clear the Filter property from the report but it
didnt' seem to work. Here is the code:

Dim strFilter As String

'Course Title
If Not IsNull(Me.cboCourse) Then
strFilter = strFilter & " AND txtCourseTitle=""" & Me.cboCourse &
""" "
End If

'Vendor
If Not IsNull(Me.cboVendor) Then
strFilter = strFilter & " AND txtVendorName=""" & Me.cboVendor & """ "
End If

'Quarter
If Not IsNull(Me.cboQuarter) Then
strFilter = strFilter & " AND txtQuarter='" & Me.cboQuarter & "' "
End If

'Brochure Type
If Not IsNull(Me.cboBrochure) Then
strFilter = strFilter & " AND txtBrochureType='" & Me.cboBrochure &
"' "
End If

'Building Location
If Not IsNull(Me.cboBuildingLocation) Then
strFilter = strFilter & " AND txtBuildingLocation='" &
Me.cboBuildingLocation & "'"
End If

'Primary Competency
If Not IsNull(Me.cboPrimaryCompetency) Then
strFilter = strFilter & " AND txtPrimaryCompetency='" &
Me.cboPrimaryCompetency & "'"
End If

'Secondary Competency
If Not IsNull(Me.cboSecondaryCompetency) Then
strFilter = strFilter & " AND txtSecondaryCompetency='" &
Me.cboSecondaryCompetency & "'"
End If

'Supplemental Competency
If Not IsNull(Me.cboSupplementalCompetency) Then
strFilter = strFilter & " AND txtSupplementalCompetency='" &
Me.cboSupplementalCompetency & "'"
End If

'Department
If Not IsNull(Me.cboDepartment) Then
strFilter = strFilter & " AND txtDepartmentType='" &
Me.cboDepartment & "'"
End If
'Instructors
If Not IsNull(Me.cboInstructors) Then
strFilter = strFilter & " AND txtInstructorName='" &
Me.cboInstructors & "'"
End If

'Training Room
If Not IsNull(Me.cboTrainingRoom) Then
strFilter = strFilter & " AND txtTrainingRoom='" &
Me.cboTrainingRoom & "'"
End If

'Work-Life
If Not IsNull(Me.cboWorkLife) Then
strFilter = strFilter & " AND txtWorkLifeType='" & Me.cboWorkLife &
"'"
End If

'If the report is closed, open the report
If SysCmd(acSysCmdGetObjectState, acReport, "rptClassesFilter") <>
acObjStateOpen Then
DoCmd.OpenReport "rptClassesFilter", acPreview, _
WhereCondition:=Mid(strFilter, 6)
Else 'if report was open, use filter
With Reports![rptClassesFilter]
.Filter = Mid(strFilter, 6)
.FilterOn = True
End With
End If

End Sub

Private Sub cmdRemoveFilter_Click()

'Remove all values from the combo boxes.
On Error Resume Next

Me.cboCourse = Null
Me.cboVendor = Null
Me.cboQuarter = Null
Me.cboBrochure = Null
Me.cboBuildingLocation = Null
Me.cboPrimaryCompetency = Null
Me.cboSecondaryCompetency = Null
Me.cboSupplementalCompetency = Null
Me.cboDepartment = Null
Me.cboInstructors = Null
Me.cboTrainingRoom = Null
Me.cboWorkLife = Null

End Sub

 

[tom_willpa]

high quality Soccer jerseys NBA Jersey tracksuit and jackets, GHD
hairstraightener supplier from www.willpa.com

Are you a Retail businessman who bother by the purchase price? China
Cheapest TOP wholesale website can help you

we are specialize in replica sport goods manufacturing in china, we can
offer you all kinds of soccer jersey, NBA jersey,shoes and so on. they are
the best brand replica goods whih are look the same as the original goods.
excellent quality and steady supply for them. we have been marketed in Europe
and American for 3 year. all the goods we offer are AAA quality. our soccer
jersey are Thailand style. If any goods you buy from my company have problem,
we will refund or resend them again. Most of ourProducts have no minimum
order requirements,soyou can shop retail goods at wholesale prices. if you
can buy more than 300usd. We offer free shipping. The more you buy the more
discount for you.

National soccer jerseys: http://www.willpa.com
Club soccer jerseys: http://www.willpa.com
NBA Jerseys: http://www.willpa.com
T-shirt and shirt: http://www.willpa.com
Tracksuit: http://www.willpa.com
Hoody & Jackets: http://www.willpa.com
UGG boots: http://www.willpa.com
Hair style: http://www.willpa.com
shopping Index: http://www.willpa.com

EMS shipping. 7days arrive, paypal accept

want more information pls contact us or check our website: www.willpa.com