Checking for null in a listbox column

[BlueJacket]

I'm having trouble with figuring out how to check if a column in a listbox is null or not.

Private Sub butOpenServiceChart_Click()

    If gEnableErrorHandling Then On Error GoTo Errhandle

    Dim stDocName As String
    Dim stQTRecord As String

    stDocName = "frmServiceChart"
    stQTRecord = "[PropertyID]=" & Me![lboMainSearchResults]
    
    If IsNull(Me![lboMainSearchResults].Column(4)) Then
        MsgBox "There is no case number associated with this property. You must create a new case" & _
        vbCrLf & " before you can edit the service chart."
    Else
        DoCmd.OpenForm stDocName, , , stQTRecord
    End If
    Exit Sub
    
Errhandle:

    Select Case Err
        Case 3075
            MsgBox "Select a record you want to edit."
        Case Else
            MsgBox "Error " & Err.Number & ": " & Err.Description & _
                   vbCrLf & "Please make a note of this error and when it occured." & _
                   vbCrLf & " " & FormatDateTime(Now(), vbGeneralDate)
    End Select

End Sub

Any help? I know the listbox columns start at 0 and a ?Me![lboMainSearchResults].Column(4) in the immediate pane does get me the results I want.

Thanks in advance.

 

[pbaldy]

I would expect that to work. Are you sure it's Null? This will check for both Null and a zero length string:

If Len(Me![lboMainSearchResults].Column(4) & vbNullString) = 0 Then

 

[The_Doc_Man]

Instead of IsNull, try

IF Nz( Me![lboMainSearchResults].Column(4, Me![lboMainSearchResults].ListIndex) ), 0 ) = 0 THEN
....

As long as 0 is not a valid case number, this should work. I'm being maybe a BIT pedantic, but the .ListIndex jumps to the selected row just as the first argument jumps to the selected column.

 

[BlueJacket]

I would expect that to work. Are you sure it's Null? This will check for both Null and a zero length string:

If Len(Me![lboMainSearchResults].Column(4) & vbNullString) = 0 Then

The record doesn't exist, so it should be null, right? The listbox is seeing if there is a case linked to that property and if there is, it displays the case number.

 

[BlueJacket]

Instead of IsNull, try

IF Nz( Me![lboMainSearchResults].Column(4, Me![lboMainSearchResults].ListIndex) ), 0 ) = 0 THEN
....

As long as 0 is not a valid case number, this should work. I'm being maybe a BIT pedantic, but the .ListIndex jumps to the selected row just as the first argument jumps to the selected column.

While I'm still in this stage, it's not pedantic at all. Thanks for the info

 

[BlueJacket]

@pbaldy

That worked. Why would it be a 0 length string instead of null?

@The_Doc_Man

I think your original code had one too many closing parenthesis, so I tried:

Nz( Me![lboMainSearchResults].Column(4, Me![lboMainSearchResults].ListIndex), 0 ) = 0 THEN

For some reason it didn't work though, since it seems like this is also checking for a zero length string?

 

[pbaldy]

I don't know about the data or row source, so don't know why it could be a ZLS. Yes, Doc used a different method to check for both, the Nz() function.

 

[The_Doc_Man]

BlueJacket, my parenthesis count probably WAS off. I'm always getting caught by that error and have go back to use fingers, toes, whatever else I can count off to reach the correct parenthesis pairing. Which is why I always leave a blank after an open paren and a blank before a close paren. Makes 'em stand out.

Nz( Me![lboMainSearchResults].Column( 4, Me![lboMainSearchResults].ListIndex ), 0 ) = 0 THEN

This should be better. You are right, I had one too many close parens.

As to ZLS findings, it depends on the origin of the data as to why it would be null or a ZLS, but doesn't matter.

Paul's method concatenates a valid (but null) string to what you have so guarantees that SOMETHING is present - the string infrastructure related to a null string. That works because string rules say that A concatenated to null is A, the string equivalent of A + 0 = A.

My method simply says "oh, by the way, if you hit a null, supply a zero."

My question is why neither method is working for you. Let's step away from the curb and ask another question. What is the format of the case number? Is it a true number (like a LONG)? Or is it a numeric or mixed alphanumeric string of data type Short Text?

 

[BlueJacket]

@The_Doc_Man
Actually, pbaldy's code worked, but when I tried the code you posted, it still wasn't recognizing that field as blank, which is weird. Nz is new to me, but does it have the ability to check for a ZLS? Doesn't it just check for a null value, since it's essentially saying, Nz(If this is null, return this)?

 

[pbaldy]

Nz() could work, but it would need to be:

If Nz(whatever, "") = "" Then

 

[The_Doc_Man]

The issue with NZ is that you can return ANYTHING YOU WANT as the second argument, but it works best if you choose to return an argument in the correct data type for the thing you were testing. If you were testing a string, ask it to return an empty string (""). If you were testing a date, have it return something obviously not useful like #1-Jan-1900# - which is far enough back to pre-date all computers. If you want an integer, return 0. If you want SINGLE or DOUBLE, return 0.0, and so on.

That's why the test is IF ( NZ( [somestringcontrol], "" ) = "" ) THEN... And by the way, this DOES test for a ZLS. But it is the IF, not the NZ, that performs that test. This works because you COULD get a ZLS back from the NZ function if you found a null, but you ALSO get a ZLS if the control had a ZLS to begin with.