Null Values as Option in List Box

  • Thread starter Thread starter William Wisnieski
  • Start date Start date
W

William Wisnieski

Hello Everyone,

I have a query by form with several list boxes. The user selects items from
the list boxes and clicks a button that returns results in a datasheet
subform.

One of the list boxes [lstStatus] contains two values: Call Back and No
Answer. The user can select either one or both and the proper results are
returned. If the user doesn't use this list box, then all Call Backs, No
Answers, and Null values are returned. As another option, I would like the
user to be able to select ONLY those records where the value of the field is
Null.

What code could I add to do that? Here is what I have that works as stated
above:

If Me!lstStatus.ItemsSelected.Count > 0 Then
If Len(strWhere) > 0 Then
strWhere = strWhere & "AND" & "[Status] IN ("
For i = 0 To lstStatus.ListCount - 1
If lstStatus.Selected(i) Then
strWhere = strWhere & "'" & lstStatus.Column(0, i) & "', "
End If
Next i
strWhere = Left(strWhere, Len(strWhere) - 2) & ")"
Else
strWhere = strWhere & "[Status] IN ("
For i = 0 To lstStatus.ListCount - 1
If lstStatus.Selected(i) Then
strWhere = strWhere & "'" & lstStatus.Column(0, i) & "', "
End If
Next i
strWhere = Left(strWhere, Len(strWhere) - 2) & ")"
End If
End If

Thanks for your help!

William
 
For this scenario, I thin an option group with four radio buttons
would be simpler: Call Back, No Answer, Both, and None. You could
then create your Where clauses easily based on the option selected:

Call Back
Status = "Call Back"

No Answer
Status = "No Answer"

Both
Status IN ("Call Back", "No Answer")

None
Status IS NULL

From a UI perspective, I think that is also simpler than a multiselect
listbox. HTH

Rick Collard
www.msc-lims.com
 
I realized that my earlier suggestion for an option group may not be
the best solution if your list of status options will grow over time.
In that case, why not search for null values when nothing is selected
in the list:

If IsNull(lstStatus) Then strWhere = "Status IS NULL"

That still may not be terribly intuitive, so you might consider adding
a "(none)" entry to the list and using that selection to search for
Status IS NULL. But what happens if someone selects both (none) and a
valid status?. That's also not very friendly. Perhaps a combination
of check box and your list will work. The check box is labelled "No
status" and when checked you disable the listbox. HTH

Rick Collard
www.msc-lims.com
 
Thanks Rick. I like your suggestion to add an additional entry to the list
box, which I've done (No Status). But I can't figure out how to insert the
necessary code into my existing code from my first post. I was thinking
something like:

If Me.Status.Selection = 'No Status' Then strWhere = [Status] IS NULL

I just don't know how to incorporate this into the existing code.

Thanks again,

William


Rick Collard said:
I realized that my earlier suggestion for an option group may not be
the best solution if your list of status options will grow over time.
In that case, why not search for null values when nothing is selected
in the list:

If IsNull(lstStatus) Then strWhere = "Status IS NULL"

That still may not be terribly intuitive, so you might consider adding
a "(none)" entry to the list and using that selection to search for
Status IS NULL. But what happens if someone selects both (none) and a
valid status?. That's also not very friendly. Perhaps a combination
of check box and your list will work. The check box is labelled "No
status" and when checked you disable the listbox. HTH

Rick Collard
www.msc-lims.com



Hello Everyone,

I have a query by form with several list boxes. The user selects items from
the list boxes and clicks a button that returns results in a datasheet
subform.

One of the list boxes [lstStatus] contains two values: Call Back and No
Answer. The user can select either one or both and the proper results are
returned. If the user doesn't use this list box, then all Call Backs, No
Answers, and Null values are returned. As another option, I would like the
user to be able to select ONLY those records where the value of the field is
Null.

What code could I add to do that? Here is what I have that works as stated
above:

If Me!lstStatus.ItemsSelected.Count > 0 Then
If Len(strWhere) > 0 Then
strWhere = strWhere & "AND" & "[Status] IN ("
For i = 0 To lstStatus.ListCount - 1
If lstStatus.Selected(i) Then
strWhere = strWhere & "'" & lstStatus.Column(0, i) & "', "
End If
Next i
strWhere = Left(strWhere, Len(strWhere) - 2) & ")"
Else
strWhere = strWhere & "[Status] IN ("
For i = 0 To lstStatus.ListCount - 1
If lstStatus.Selected(i) Then
strWhere = strWhere & "'" & lstStatus.Column(0, i) & "', "
End If
Next i
strWhere = Left(strWhere, Len(strWhere) - 2) & ")"
End If
End If

Thanks for your help!

William
Click to expand...
Click to expand...
 
How about something like this:

If Me!lstStatus.ItemsSelected.Count > 0 Then
strStatus = "[Status] IN ("
For i = 0 To lstStatus.ListCount - 1
If lstStatus.Selected(i) Then
If lstStatus.Column(0, i) = "No Status" Then
strStatus = "[Status] IS NULL"
Exit For
End If
strStatus = strStatus & "'" & _
lstStatus.Column(0, i) & "', "
End If
Next i
If strStatus <> "[Status] IS NULL" Then _
strStatus = Left(strStatus, Len(strStatus) - 2) & ")"

If Len(strWhere) > 0 Then
strWhere = strWhere & " AND " & strStatus
Else
strWhere = strStatus
End If
End If

Give that a try.

Rick Collard
www.msc-lims.com
 
Rick,

Thank you very much for the code. It works great. But what you said may
happen sure did. If I click on "No Status" and "No Answer", then the
results are only those with Null values as the status. I'm hoping I can
tweak this so that it will return both those with Null values AND the "No
Answer" value.

Thanks again for your help with this.

William


Rick Collard said:
How about something like this:

If Me!lstStatus.ItemsSelected.Count > 0 Then
strStatus = "[Status] IN ("
For i = 0 To lstStatus.ListCount - 1
If lstStatus.Selected(i) Then
If lstStatus.Column(0, i) = "No Status" Then
strStatus = "[Status] IS NULL"
Exit For
End If
strStatus = strStatus & "'" & _
lstStatus.Column(0, i) & "', "
End If
Next i
If strStatus <> "[Status] IS NULL" Then _
strStatus = Left(strStatus, Len(strStatus) - 2) & ")"

If Len(strWhere) > 0 Then
strWhere = strWhere & " AND " & strStatus
Else
strWhere = strStatus
End If
End If

Give that a try.

Rick Collard
www.msc-lims.com


Thanks Rick. I like your suggestion to add an additional entry to the list
box, which I've done (No Status). But I can't figure out how to insert the
necessary code into my existing code from my first post. I was thinking
something like:

If Me.Status.Selection = 'No Status' Then strWhere = [Status] IS NULL

I just don't know how to incorporate this into the existing code.

Thanks again,

William
Click to expand...
Click to expand...
 
Back
Top