Look for NULL Value of a field

K

KIT LAU

Hi,

I have a form with a unbound combo box for the user to
select Category of Product to display in the subform.

Some users may not input data in the Category. I want to
look for those records by choosing "(Blank)" from a combo
box and those records will be displayed in the subform.

Thanks in advance,
KIT LAU
 
A

Allen Browne

You cannot do this using the LinkMasterFields/LinkChildFields property of
the subform control, because Null does not match anything. Instead, set the
Filter property of the form (or subform as it currently is).

Assuming you have a way to get the value "(Blank)" into the combo's
RowSource, the [Event Procedure] for its AfterUpdate would be something like
this:

Private Sub cboCat_AfterUpdate()
Dim strFilter As String

With Me.cboCat
If Not IsNull(.Value) Then
If .Value = "(blank)" Then
strFilter = "[Category of Product] Is Null"
Else
strFilter = "[Category of Product] = """ & .Value & """"
End If
End If
End With

With Me.[NameOfYourSubformControlHere].Form
If Len(strFilter) = 0 Then 'Nothing: show all records.
.FilterOn = False
Else
.Filter = strFilter
.FilterOn = True
End If
End With
End Sub


Note: If Category of Product is a number field, drop the extra quotes, i.e.:
strFilter = "[Category of Product] = " & .Value
 
G

Guest

Thanks a lot! It works very well. :)
-----Original Message-----
You cannot do this using the
Click to expand...
LinkMasterFields/LinkChildFields property of
the subform control, because Null does not match anything. Instead, set the
Filter property of the form (or subform as it currently is).

Assuming you have a way to get the value "(Blank)" into the combo's
RowSource, the [Event Procedure] for its AfterUpdate would be something like
this:

Private Sub cboCat_AfterUpdate()
Dim strFilter As String

With Me.cboCat
If Not IsNull(.Value) Then
If .Value = "(blank)" Then
strFilter = "[Category of Product] Is Null"
Else
strFilter = "[Category of Product] = """ & .Value & """"
End If
End If
End With

With Me.[NameOfYourSubformControlHere].Form
If Len(strFilter) = 0 Then 'Nothing: show all records.
.FilterOn = False
Else
.Filter = strFilter
.FilterOn = True
End If
End With
End Sub


Note: If Category of Product is a number field, drop the extra quotes, i.e.:
strFilter = "[Category of Product] = " & .Value

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have a form with a unbound combo box for the user to
select Category of Product to display in the subform.

Some users may not input data in the Category. I want to
look for those records by choosing "(Blank)" from a combo
box and those records will be displayed in the subform.

Thanks in advance,
KIT LAU
Click to expand...


.
Click to expand...
 
Top