Null Values in Listboxes for Dynamic SQL String

[alsoascientist]

Hi All,

I think this should be pretty straight forward but I seem to be missing something with my code!

I have a SQL WHERE argument that builds off of a number of listboxes and eventually creates a query with the result.
The issue that I am having is that it doesn't seem to recognise Null values.

I have tried the code below with half my data set with null and half with zerolength strings and it always returns "FieldName = ' ' OR FieldName = ' '". This means that the null values aren't returned in the result.

I did think about replacing all the null fields with 0strings but I have concerns that these will not stay this way.

There should be a way to return "FieldName Is Null"?

        For Each Ctrl In RptFrm.Controls
        If Ctrl.ControlType = acListBox Then
        If Ctrl.Visible = True Then
        For Each varItem In Ctrl.ItemsSelected
        If IsNull(Ctrl.ItemData(varItem)) = True Then varWhere = varWhere & Ctrl.Name & "Is Null OR "
        varWhere = varWhere & Ctrl.Name & "='" & Ctrl.ItemData(varItem) & "' OR "
        Next
        End If
        End If

I've also tried
If Ctrl.ItemData(varItem) = "" Then varWhere = varWhere & Ctrl.Name & "Is Null OR "
and
varWhere = varWhere & Ctrl.Name & "='" & Nz(Ctrl.ItemData(varItem),'Is Null' & "' OR "

and

 

[sparks80]

Hi,

Is the row source for the listbox set to allow all records to show. Is it possible that the null records are missing? You can check this easily by seeing how many items appear in the listbox, against the number of items in the table/query you are using.

You don't need to use IsNull(...) = True, IsNull(...) will suffice.

I am not sure but is it possible that a null value will be represented by a zero-length string in the listbox?

 

[sparks80]

Hi,

I have just tested this, and the listbox seems to store the null values as zero length strings, so you won't be able to distinguish.

You could use an ADODB recordset based on the control's RowSource which will let you evaluate null values. Here is my attempt at an example, although not properly tested.

Please note before this will work you will need to set a reference to the ADO library. If you're not sure how to do this I can explain.

Private Sub Test()
    Dim ctrl As Control
    Dim rst As ADODB.Recordset
    Dim strSQL As String
    
    Set rst = New ADODB.Recordset
    
    For Each ctrl In RptFrm.Controls
        If ctrl.ControlType = acListBox Then
            strSQL = ctrl.RowSource
            rst.Open strSQL, CurrentProject.Connection, adOpenStatic, adLockReadOnly
            
            Do While Not rst.EOF
                If IsNull(rst!myfield) Then
                    varWhere = varWhere & ctrl.Name & " Is Null OR "
                End If
                varWhere = varWhere & ctrl.Name & "='" & rst!myfield & "' OR "
                rst.MoveNext
            Loop
        
            rst.Close
        End If
    Next
    
    Debug.Print varWhere
    
    Set rst = Nothing
End Sub

 

[alsoascientist]

Thanks for this sparks...

I thought I had it there but maybe I'm not as sure as I thought I was about setting the reference! Could you give me a few hints?!

Re the list box storing null values as 0strings, I mentioned earlier that I had set half of my dataset to "" and left the other half null so that I could test and I've noticed that the listbox shows two blank fields (what I presume to be one for "" and one for null). When i fire the code with both selected it will only return half and with only one will either return half or none depending on which is selected.
Also when I was stepping through my original code the IsNull() was definitely showing 0 for one and 1 for the other, it just didn't move to the 'then' after.

I'm not too good with loops and to be honest don't understand how your code works but if it does then I'm happy!!

 

[alsoascientist]

ok so I have this half sorted....

I managed to get round this using

        For Each varItem In ctrl.ItemsSelected
        If ctrl.ItemData(varItem) = "" Then varWhere = varWhere & ctrl.Name & " Is Null OR "
        varWhere = varWhere & ctrl.Name & "='" & ctrl.ItemData(varItem) & "' OR "

(actually returns FIELD Is Null OR FIELD = ' ' OR FIELD Is Null OR FIELD = ' ' but I guess that doesn't matter too much)

The only issue I have is that I've still got both values in the list box... any idea how I can join them up? (or even better get them to say "Blank")? I've been playing around with the Union query for the source but I really can't get my head around it - the only examples I can find are to add "All" and I really don't understand the syntax. At the moment my rowsource is SELECT DISTINCT [FIELD] FROM Query.

 

[alsoascientist]

Sussed!!

Managed to cobble both together for one solution - though I'm sure there is an easier way to do this!

Using a row source as
SELECT DISTINCT [FIELD] FROM QUERY WHERE [SFN25] <> " " UNION SELECT "<No Data>" FROM QUERY

        For Each ctrl In RptFrm.Controls
        If ctrl.ControlType = acListBox Then
        If ctrl.Visible = True Then
        For Each varItem In ctrl.ItemsSelected
        If ctrl.ItemData(varItem) = "<No Data>" Then _
        varWhere = varWhere & ctrl.Name & " Is Null OR " _
        & ctrl.Name & "=' ' OR "
        varWhere = varWhere & ctrl.Name & "='" & ctrl.ItemData(varItem) & "' OR "
        Next
        End If
        End If

So it removes the blanks completely whether they are null or 0strings and replaces it with a much tidier "No Data", then using this to drive the string.

Thanks for your help sparks - wouldn't have got there without your musings!

 

[sparks80]

Hi,

Glad I could give you some pointers, although I think you did most of the hard work!