Filter for null values

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have several forms (layout is continuous) that wrote the following function
to run on when the user clicks on any field in my form. Can anyone tell me
why this function isn't finding null values, but finds any other values other
than null?

Thanks for all of your help!

Arlene

Public Function FilterIt()
Dim frm As Form, ctl As Control, strField As String
Set frm = Screen.ActiveForm
Set ctl = Screen.ActiveForm.ActiveControl
strField = ctl.Name
DoCmd.ApplyFilter , strField & "='" & ctl & "'"
Set frm = Nothing
Set ctl = Nothing
End Function
 
Null is a special case: you cannot use = in conjunction with Null.

To find Null characters, you'd need

DoCmd.ApplyFilter , strField & " IS NULL"
 
The first issue is that nothing ever equals null. For an explanation, see:
Common errors with Null
at:
http://allenbrowne.com/casu-12.html

The second issue is that the expression in your ApplyFilter line includes a
single quote as the delimiter around the value. That's okay for text fields,
but won't work for numeric fields (which use no delimiter) or date fields
(which use # as the delimiter.) You therefore need a way to determine the
data type of the field. It might be possible to derive that by examining the
Type of the Field in the RecordsetClone of the form.

A third issue is that when you change the value of the control and then
filter the form, Access saves that change. If you are attempting to use it
just for filtering, this will mess up your data.

There are other pitfalls as well, such as if:
- the control is unbound;
- the control that is bound to an expression;
- the control's ControlSource is different from its Name;
- the control has no ControlSource (e.g. a command button).
 
You need to account for the NULL. Try:

Public Sub FilterIt()

On Error GoTo ErrHandler

Dim frm As Form, ctl As Control, strField As String
Set frm = Screen.ActiveForm
Set ctl = Screen.ActiveForm.ActiveControl
strField = ctl.Name
If (Not (Nz(ctl.Value, "") = "")) Then
DoCmd.ApplyFilter , strField & "='" & ctl & "'"
End If
Set frm = Nothing
Set ctl = Nothing

Exit Sub

ErrHandler:

MsgBox "Error in FilterIt( )." & vbCrLf & vbCrLf & _
"Error #" & Err.Number & vbCrLf & vbCrLf & Err.Description
Err.Clear

End Sub ' FilterIt( )

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.
 
Thanks to all of you for your help.

I changed the following two lines and it works whether or not there is a
value or the value is null.

If Not Nz(ctl.Value, "") = "" Then
DoCmd.ApplyFilter , strField & "='" & ctl & "'"
Else
DoCmd.ApplyFilter , strField & " is null"
End If
 
Back
Top