Like '*' or Is Null

  • Thread starter Thread starter Ann
  • Start date Start date
A

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

Marshall Barton said:
Ann said:
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.
Click to expand...
[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
Click to expand...
 
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.

Marshall Barton said:
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.

Marshall Barton said:
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
Click to expand...
Click to expand...
.
Click to expand...
 
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


Marshall Barton said:
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.

Marshall Barton said:
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
Click to expand...
Click to expand...
.
Click to expand...
 
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!

Marshall Barton said:
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
Click to expand...

.
Click to expand...
 
Back
Top