Null Values as Option in List Box

[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

[Rick Collard]

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

On Thu, 26 Feb 2004 11:33:47 -0500, "William Wisnieski"
<ww********@admissions.umass.edu> wrote:

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

[Rick Collard]

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

On Thu, 26 Feb 2004 11:33:47 -0500, "William Wisnieski"
<ww********@admissions.umass.edu> wrote:

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

[William Wisnieski]

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" <no****@nospam.nospam> wrote in message
news:40**************@news.west.earthlink.net...

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

On Thu, 26 Feb 2004 11:33:47 -0500, "William Wisnieski"
<ww********@admissions.umass.edu> wrote:

Hello Everyone,

I have a query by form with several list boxes. The user selects items fromthe 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 arereturned. 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 theuser to be able to select ONLY those records where the value of the field isNull.

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

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

[Rick Collard]

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
On Mon, 1 Mar 2004 16:55:21 -0500, "William Wisnieski"
<ww********@admissions.umass.edu> wrote:

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

[William Wisnieski]

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" <no****@nospam.nospam> wrote in message
news:40***************@news.west.earthlink.net...

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
On Mon, 1 Mar 2004 16:55:21 -0500, "William Wisnieski"
<ww********@admissions.umass.edu> wrote:

Thanks Rick. I like your suggestion to add an additional entry to the listbox, which I've done (No Status). But I can't figure out how to insert thenecessary 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