Null Value Question

  • Thread starter Thread starter Anthony Viscomi
  • Start date Start date
A

Anthony Viscomi

I've created a query that filters for the following criteria:
"ITEM" this is passed by a value within a combo box on the loaded form
"OPTION" this is passed by a value within a combo box on the loaded form

There is also a "Price" field; this is the value I am looking for.

There is always an ITEM; but not always an OPTION for a particuliar ITEM.
Thus when the OPTION field is Null, no values are returned in the query.
Basically, I need a way for the query to handle these occurences.

Any thoughts?
Thanks!
Anthony Viscomi
 
This example filters the form based on the user's entries in the 2 unbound
combo boxes. It returns the records where OptionID matches the entry and
also those where OptionID is blank.

Dim strWhere As String
Dim lngLen As Long

If Me.Dirty Then 'Save first.
Me.Dirty = False
End If

If Not IsNull(Me.cboFindItem) Then
strWhere = "(ItemID = " & Me.cboFindItem & ") AND "
End If
If Not IsNull(Me.cboFindOption) Then
strWhere = strWhere & "((OptionID = " & Me.cboFindOption & _
") OR (OptionID Is Null)) AND "
End If

lngLen = Len(strWhere) - 5 'Without trailing " AND ".
If lngLen <= 0 Then
Me.FilterOn = False
Else
Me.Filter = Left(strWhere, lngLen)
Me.FilterOn = True
Endif


Notes:
1. If ItemID is a Text field (not a Number field), you need extra quotes:
strWhere = "(ItemID = """ & Me.cboFindItem & """) AND "
Same for OptionID.

2. The procedure is constructed so it is easy to add further unbound search
boxes if you wish. Each one adds the " AND " at the end, and the final part
chops off the trailing " AND ".
 
Thanks!
Allen Browne said:
This example filters the form based on the user's entries in the 2 unbound
combo boxes. It returns the records where OptionID matches the entry and
also those where OptionID is blank.

Dim strWhere As String
Dim lngLen As Long

If Me.Dirty Then 'Save first.
Me.Dirty = False
End If

If Not IsNull(Me.cboFindItem) Then
strWhere = "(ItemID = " & Me.cboFindItem & ") AND "
End If
If Not IsNull(Me.cboFindOption) Then
strWhere = strWhere & "((OptionID = " & Me.cboFindOption & _
") OR (OptionID Is Null)) AND "
End If

lngLen = Len(strWhere) - 5 'Without trailing " AND ".
If lngLen <= 0 Then
Me.FilterOn = False
Else
Me.Filter = Left(strWhere, lngLen)
Me.FilterOn = True
Endif


Notes:
1. If ItemID is a Text field (not a Number field), you need extra quotes:
strWhere = "(ItemID = """ & Me.cboFindItem & """) AND "
Same for OptionID.

2. The procedure is constructed so it is easy to add further unbound search
boxes if you wish. Each one adds the " AND " at the end, and the final part
chops off the trailing " AND ".
Click to expand...
 
Back
Top