Filter a report and display field even if blank

  • Thread starter Thread starter KPatel
  • Start date Start date
K

KPatel

I have a code that helps me to filter a report based on
criteria entered in a form. Here is a part of the code...

Dim strid As String
Dim strlast As String
Dim strfirst As String
Dim strfilter As String
If IsNull(Me.Id.Value) Then
strid = "Like '*'"
Else
strid = "='" & Me.Id.Value & "'"
End If
If IsNull(Me.Last.Value) Then
strlast = "Like '*'"
Else
Select Case Me.fraLastName.Value
Case 1
strlast = "Like '" & Me.Last.Value & "*'"
Case 2
strlast = "Like '*" & Me.Last.Value & "*'"
Case 3
strlast = "Like '*" & Me.Last.Value & "'"
Case 4
strlast = "= '" & Me.Last.Value & "'"
End Select
End If
If IsNull(Me.First.Value) Then
strfirst = "Like '*'"
Else
Select Case Me.fraFirstName.Value
Case 1
strfirst = "Like '" & Me.First.Value & "*'"
Case 2
strfirst = "Like '*" & Me.First.Value & "*'"
Case 3
strfirst = "Like '*" & Me.First.Value & "'"
Case 4
strfirst = "= '" & Me.First.Value & "'"
End Select
End If
strfilter="[C&R ID] " & strid & " AND [Last Name] " &
strlast & " AND [First Name] " & strfirst

This code would work fine if all the fields in a record
had data. But there are quite a few fields that have
been left blank and when I run the above code it does not
consider the records that have the blank fields.

For example,
If a record had the following values C&R ID-1001, Last
Name-Brown and First Name-not entered, and the filter
asked for records with last name Brown, the record for ID
1001 would not show up.

What condition should I give here to make sure that the
records with some blank fields are displayed as well if
they meet one or more of the filter criteria. Do let me
know if I have made myself clear enough. Thanks in
advance for whatever help anyone can provide.

Thanks
 
KPatel said:
I have a code that helps me to filter a report based on
criteria entered in a form. Here is a part of the code...

Dim strid As String
Dim strlast As String
Dim strfirst As String
Dim strfilter As String
If IsNull(Me.Id.Value) Then
strid = "Like '*'"
Else
strid = "='" & Me.Id.Value & "'"
End If
If IsNull(Me.Last.Value) Then
strlast = "Like '*'"
Else
Select Case Me.fraLastName.Value
Case 1
strlast = "Like '" & Me.Last.Value & "*'"
Case 2
strlast = "Like '*" & Me.Last.Value & "*'"
Case 3
strlast = "Like '*" & Me.Last.Value & "'"
Case 4
strlast = "= '" & Me.Last.Value & "'"
End Select
End If
If IsNull(Me.First.Value) Then
strfirst = "Like '*'"
Else
Select Case Me.fraFirstName.Value
Case 1
strfirst = "Like '" & Me.First.Value & "*'"
Case 2
strfirst = "Like '*" & Me.First.Value & "*'"
Case 3
strfirst = "Like '*" & Me.First.Value & "'"
Case 4
strfirst = "= '" & Me.First.Value & "'"
End Select
End If
strfilter="[C&R ID] " & strid & " AND [Last Name] " &
strlast & " AND [First Name] " & strfirst

This code would work fine if all the fields in a record
had data. But there are quite a few fields that have
been left blank and when I run the above code it does not
consider the records that have the blank fields.

For example,
If a record had the following values C&R ID-1001, Last
Name-Brown and First Name-not entered, and the filter
asked for records with last name Brown, the record for ID
1001 would not show up.

What condition should I give here to make sure that the
records with some blank fields are displayed as well if
they meet one or more of the filter criteria. Do let me
know if I have made myself clear enough. Thanks in
advance for whatever help anyone can provide.

Thanks
Click to expand...

Try this version of the code -- it only filters on those fields for
which values have been entered:

'----- start of code -----
Dim strfilter As String

If Not IsNull(Me.ID.Value) Then
strfilter = strfilter & " AND [C&R ID] ='" & Me.ID.Value & "'"
End If

If Not IsNull(Me.Last.Value) Then
strfilter = strfilter & " AND [Last Name] "
Select Case Me.fraLastName.Value
Case 1
strfilter = strfiler & "Like '" & Me.Last.Value & "*'"
Case 2
strfilter = strfiler & "Like '*" & Me.Last.Value & "*'"
Case 3
strfilter = strfiler & "Like '*" & Me.Last.Value & "'"
Case 4
strfilter = strfiler & "= '" & Me.Last.Value & "'"
End Select
End If

If Not IsNull(Me.First.Value) Then
strfilter = strfilter & " AND [First Name] "
Select Case Me.fraFirstName.Value
Case 1
strfilter = strfiler & "Like '" & Me.First.Value & "*'"
Case 2
strfilter = strfiler & "Like '*" & Me.First.Value & "*'"
Case 3
strfilter = strfiler & "Like '*" & Me.First.Value & "'"
Case 4
strfilter = strfiler & "= '" & Me.First.Value & "'"
End Select
End If

' strip off leading " AND ", if present.
If Len(strfilter) > 0 Then
strfilter = Mid$(strfilter, 6)
End If

'----- end of code -----

That's untested "air code", but it should at least give you the idea.
 
I tried the below suggested code and it did not seem to
work. I guess I did not explain the issue that well.
The problem is not with the form taking the input for the
filter criteria, but the problem is with the table from
which the report is created which has blank fields for
certain records. If all the fields in a record have
values and the form is trying to filter for any one value
it works perfectly well. Any suggestions????

Thanks
-----Original Message-----
I have a code that helps me to filter a report based on
criteria entered in a form. Here is a part of the code...

Dim strid As String
Dim strlast As String
Dim strfirst As String
Dim strfilter As String
If IsNull(Me.Id.Value) Then
strid = "Like '*'"
Else
strid = "='" & Me.Id.Value & "'"
End If
If IsNull(Me.Last.Value) Then
strlast = "Like '*'"
Else
Select Case Me.fraLastName.Value
Case 1
strlast = "Like '" & Me.Last.Value & "*'"
Case 2
strlast = "Like '*" & Me.Last.Value & "*'"
Case 3
strlast = "Like '*" & Me.Last.Value & "'"
Case 4
strlast = "= '" & Me.Last.Value & "'"
End Select
End If
If IsNull(Me.First.Value) Then
strfirst = "Like '*'"
Else
Select Case Me.fraFirstName.Value
Case 1
strfirst = "Like '" & Me.First.Value & "*'"
Case 2
strfirst = "Like '*" & Me.First.Value & "*'"
Case 3
strfirst = "Like '*" & Me.First.Value & "'"
Case 4
strfirst = "= '" & Me.First.Value & "'"
End Select
End If
strfilter="[C&R ID] " & strid & " AND [Last Name] " &
strlast & " AND [First Name] " & strfirst

This code would work fine if all the fields in a record
had data. But there are quite a few fields that have
been left blank and when I run the above code it does not
consider the records that have the blank fields.

For example,
If a record had the following values C&R ID-1001, Last
Name-Brown and First Name-not entered, and the filter
asked for records with last name Brown, the record for ID
1001 would not show up.

What condition should I give here to make sure that the
records with some blank fields are displayed as well if
they meet one or more of the filter criteria. Do let me
know if I have made myself clear enough. Thanks in
advance for whatever help anyone can provide.

Thanks
Click to expand...

Try this version of the code -- it only filters on those fields for
which values have been entered:

'----- start of code -----
Dim strfilter As String

If Not IsNull(Me.ID.Value) Then
strfilter = strfilter & " AND [C&R ID] ='" & Me.ID.Value & "'"
End If

If Not IsNull(Me.Last.Value) Then
strfilter = strfilter & " AND [Last Name] "
Select Case Me.fraLastName.Value
Case 1
strfilter = strfiler & "Like '" & Me.Last.Value & "*'"
Case 2
strfilter = strfiler & "Like '*" & Me.Last.Value & "*'"
Case 3
strfilter = strfiler & "Like '*" & Me.Last.Value & "'"
Case 4
strfilter = strfiler & "= '" & Me.Last.Value & "'"
End Select
End If

If Not IsNull(Me.First.Value) Then
strfilter = strfilter & " AND [First Name] "
Select Case Me.fraFirstName.Value
Case 1
strfilter = strfiler & "Like '" & Me.First.Value & "*'"
Case 2
strfilter = strfiler & "Like '*" & Me.First.Value & "*'"
Case 3
strfilter = strfiler & "Like '*" & Me.First.Value & "'"
Case 4
strfilter = strfiler & "= '" & Me.First.Value & "'"
End Select
End If

' strip off leading " AND ", if present.
If Len(strfilter) > 0 Then
strfilter = Mid$(strfilter, 6)
End If

'----- end of code -----

That's untested "air code", but it should at least give you the idea.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)


.
Click to expand...
 
KPatel said:
I tried the below suggested code and it did not seem to
work. I guess I did not explain the issue that well.
The problem is not with the form taking the input for the
filter criteria, but the problem is with the table from
which the report is created which has blank fields for
certain records. If all the fields in a record have
values and the form is trying to filter for any one value
it works perfectly well. Any suggestions????
Click to expand...

I understand what you're trying to do, and the code I gave you was
intended to solve that problem. The problem is that comparisons such as
"Null Like '*'" or "Null = anything" return Null. So you have to either
build a much more complex filter expression, or leave out those terms
you don't want to filter on.

I suspect that the code I posted failed because I had a repeated typo in
it: I wrote "strfiler" on many lines, when I meant to write
"strfilter". Hey, I did say it was untested air code. If you had
Option Explicit specified in the Declarations section of your module, as
you always should, you would have gotten a compile error that would have
highlighted the problem.

Please edit the code and replace "strfiler" with "strfilter" wherever it
occurs, and try it again. It would be a very good idea also to add
Option Explicit at the top of the module (and all other existing
modules), and check the option box (in the VB Editor's option dialog)
labelled "Require Variable Declaration" to avoid having this sort of
problem occur in new modules.
 
Thanks a lot. It is working. It wasn't the "strfilter"
variable or the Option Explicit statement. (I have that
in my code). I just didn't follow thru all the steps.
Once I did it it started working.

Once again Thank You
 
Nice work. If only a thousand readers could throw a dime into the "Tips"
jar...
Paul
 
Back
Top