446,224 Members | 1,143 Online
Need help? Post your question and get tips & solutions from a community of 446,224 IT Pros & Developers. It's quick & easy.

# Null Values as Option in List Box

 P: n/a 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 Nov 12 '05 #1
5 Replies

 P: n/a 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" 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 datasheetsubform.One of the list boxes [lstStatus] contains two values: Call Back and NoAnswer. 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, NoAnswers, 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 IfThanks for your help!William Nov 12 '05 #2

 P: n/a 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" 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 datasheetsubform.One of the list boxes [lstStatus] contains two values: Call Back and NoAnswer. 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, NoAnswers, 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 IfThanks for your help!William Nov 12 '05 #3

 P: n/a 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" 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" 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 datasheetsubform.One of the list boxes [lstStatus] contains two values: Call Back and NoAnswer. 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, NoAnswers, 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 IfThanks for your help!William Nov 12 '05 #4

 P: n/a 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" 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 thinkingsomething like:If Me.Status.Selection = 'No Status' Then strWhere = [Status] IS NULLI just don't know how to incorporate this into the existing code.Thanks again,William Nov 12 '05 #5

 P: n/a 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" 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" 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 thinkingsomething like:If Me.Status.Selection = 'No Status' Then strWhere = [Status] IS NULLI just don't know how to incorporate this into the existing code.Thanks again,William Nov 12 '05 #6

### This discussion thread is closed

Replies have been disabled for this discussion.