Search form not returning records with blank fields

[crystal2000a]

Hi Experts,

I'm creating a search form to filter out data based on certain inputs. My VBA code looks like:

Private Sub Command18_Click()
On Error GoTo errorcatch

Me.Filter = "([Experiments.Log] Like ""*" & Me.Text21 & "*"") AND ([Expdate] Like ""*" & Me.Text22 & "*"") AND ([BaseSolution] Like ""*" & Me.Text24 & "*"") AND([AddCom] Like ""*" & Me.Text25 & "*"") AND ([Test] Like ""*" & Me.Text26 & "*"") AND ([Plan] Like ""*" & Me.Text23 & "*"")"


Me.FilterOn = True
Exit Sub
errorcatch:
MsgBox "Error #: " & Err.Number & vbCrLf & vbCrLf & Err.Description

End Sub

However, the output does not include records where other fields are blank. I have read that I may need to use Is Null but am not sure how to. Any help would be appreciated!!

 

[pr2-eugin]

Try this code..

Me.Filter = "(([Experiments.Log] Like ""*" & Me.Text21 & "*"") OR ([Experiments.Log] Is Null)) AND " & _
            "(([Expdate] Like ""*" & Me.Text22 & "*"") OR ([Expdate] Is Null)) AND " & _
            "(([BaseSolution] Like ""*" & Me.Text24 & "*"") OR ([BaseSolution] Is Null)) AND " & _
            "(([AddCom] Like ""*" & Me.Text25 & "*"") OR ([AddCom] Is Null)) AND " & _
            "(([Test] Like ""*" & Me.Text26 & "*"") OR ([Test] Is Null)) AND " & _
            "(([Plan] Like ""*" & Me.Text23 & "*"") OR ([Plan] Is Null))"

 

[crystal2000a]

Thank you!! It worked!!

 

[namliam]

With this kind of complex search going on, it would seem unlikely you want to actually search on all those fields all the time...

Instead you would be better off making an actualy "search by form"
Check out this thread: http://www.access-programmers.co.uk/forums/showthread.php?p=1300908#post1300908