Trouble Selecting Null Values in a MultiSelect Listbox

[kopy]

I'm trying to use a multi-listbox to paramterize a query. I was directed to Duane Hookam's code on Roger's Access Library. It works great for all of the list box selections except for null values in the field in the bound column. Any help with this will be greatly appreciated. Here's the code:

Function IsSelectedVar( _
strFormName As String, _
strListBoxName As String, _
varValue As Variant) _
As Boolean

'strFormName is the name of the form
'strListBoxName is the name of the listbox
'varValue is the field to check against the listbox
Dim lbo As ListBox
Dim item As Variant

If IsNumeric(varValue) Then
varValue = Trim(Str(varValue))
End If

Set lbo = Forms(strFormName)(strListBoxName)
For Each item In lbo.ItemsSelected
If lbo.ItemData(item) = varValue Then
IsSelectedVar = True
Exit Function
End If
Next
End Function

 

[MajP]

use the null to zero function NZ or
Trim(varvalue & " ")
or
isnull
or
do not return any null is the row source.

 

[kopy]

Thank you for replying.

Where in the code should I put it?


- Kopy

 

[TheAceMan1]

How are ya kopy . . .

[blue]   [purple][b]If Trim(varValue & "") <> "" Then[/b][/purple]
      If IsNumeric(varValue) Then
           varValue = Trim(Str(varValue))
      End If
       
      Set lbo = Forms(strFormName)(strListBoxName)
      For Each Item In lbo.ItemsSelected
          If lbo.ItemData(Item) = varValue Then
              IsSelectedVar = True
              Exit Function
          End If
      Next
   [purple][b]End If[/b][/purple][/blue]

See Ya! . . . . . .

 

[MajP]

Actually that is not where his problem is because (varvalue) is just the parameter coming in. If that is not numeric I would pop up a message.

1) do not call a variable "item" this is a reserved word
2)

  Dim varItem As Variant

  If not IsNumeric(varValue) Then
    msgbox "Must supply a Number"
    exit function
  End If
  
 For Each varItem In lbo.ItemsSelected
    varItem = lbo.ItemData(varItem)
    If [b]Trim(varItem & " ")[/b] = varValue Then
       IsSelectedVar = True
       Exit Function
    End If
  Next
End Sub

 

[TheAceMan1]

Howdy MajP . . .
[ol][li]You exit the routine [blue]prematurely[/blue] . . . I don't.[/li]
[li]In this snippet of code:

[blue]   If not IsNumeric(varValue) Then[/blue]

[blue]kopy[/blue] is simply converting any numeric [blue]varValue[/blue] to string. Its not that [blue]varValue[/blue] should be numeric . . . its that it should be converted if it is![/li]
[li]Since arguements allow the function to look at any listbox on any form, I see many datatypes being passed thru [blue]varValue[/blue] espcially since its datatype is [purple]variant[/purple]. This [blue]makes your msgbox ambiguous[/blue] in the code. Better might be a general message afrer the function returns its value.[/li]
[li]Your routine and mine both return the proper value.[/li][/ol]
The post origination is about handling nulls (not numeric values) in the bound column of listboxes. I wonder if we may be [blue]a little off the mark here![/blue] We'll know on [blue]kopy's[/blue] return.

See Ya! . . . . . .

 

[MajP]

Okay I can see your interpretation. Not sure if I understand why you need to do that. If item data returns a variant and you are passing in a variant why would you need to convert to a string even if the underlying field is a string. Or if you need to trim why not trim everything regardless if numeric? He will have to explain that. But I am pretty sure his problem is with null values in the bound column of the listbox.

It works great for all of the list box selections except for null values in the field in the bound column

I Notice that he dimensioned a variable called "item" but does nothing with it. That is why I put the extra check in the loop
If Trim(varItem & " ") = varValue Then
IsSelectedVar = True
Exit Function
End If

 

[TheAceMan1]

[blue]Actually that is not where his problem is because [purple](varvalue) is just the parameter coming in.[/purple] If that is not numeric I would pop up a message.[/blue]

[blue]If item data returns a variant and [purple]you are passing in a variant[/purple] . . .[/blue]

To my knowledge there's no specific data type for a variant as [purple]a variant accepts all data types[/purple] . . . [blue]a variants greatest use![/blue]

Only [blue]kopy[/blue] knows why numeric has to be converted to string!

See Ya! . . . . . .

 

[kopy]

Thank you MajP and TheAceMan1. Your suggestions were helpful. Unfortunetly they did not help me detect the null values. But it wasn't caused by your code. Let me explain:

The row source of the listbox is a query that groups all of the occurences for the field in the bound column of the listbox. One of thise occurences is a null value. The listbox displays it as a blank in the bound column. What I discovered is that the blank in the listbox is a zero length string and not really a null. So I changed the source of the query, that your code is parameter for, to a query that converts the nulls to zero length strings.

Now everything works fine.

Thank you again for your help,

Kopy