Solved Nz(control.DefaultValue) returns Null, not 0 or "". Why? (1 Viewer)

Notiophilus

Registered User.
Local time
Today, 10:27
Joined
Jan 18, 2015
Messages
42
ETA: IGNORE EVERYTHING, I'M AN IDIOT

I have some filter controls on my form whose values I save to a bookmarks table on Form_Unload. I save them if and only if the control value (stored in .Tag) <> the default value. Some default values are null, e.g. triple-state checkboxes, so I stuck a Nz on it: If ctl.Tag <> Nz(ctl.DefaultValue) Then do stuff
To my surprise, though, Nz(ctl.DefaultValue) returns Null.

It's not really an issue - I can work around it by making a special case for the one control where DefaultValue actually matters, or just store the DefaultValues to ctl.Tag - but I'd still like to know why Nz() doesn't work as expected here.

Full code in case it's relevant:

Private Sub cmdFilterOn_Click(): ctl.Tag = ctl.Value

Code:
    'Save the last used filter values to bookmarks (control1.name ¬ control1.value | control2.name...). Do not save nulls.
    strStoredControls = ""
    For Each ctl In Me.Form.Controls
        If ctl.ControlType = acTextBox Or ctl.ControlType = acComboBox Or ctl.ControlType = acCheckBox Then
            If ctl.Tag <> Nz(ctl.DefaultValue) Then                'HERE IS THE PROBLEM
                strStoredControls = strStoredControls & ctl.Name & "¬" & ctl.Tag & "|"
            End If
        End If
    Next

    lngLen = Len(strStoredControls)
    If lngLen > 0 Then strStoredControls = Left(strStoredControls, lngLen - 1)
  
    strSQL2 = "UPDATE [luBookmark] " & _
              "SET [Data] = """ & strStoredControls & """" & _
              " WHERE [BookmarkID] = 2"
    DBEngine(0)(0).Execute strSQL2, dbFailOnError
 
Last edited:

theDBguy

I’m here to help
Staff member
Local time
Today, 02:27
Joined
Oct 29, 2018
Messages
21,322
Hi. Just curious, how did you know the Nz() function returned a Null?
 

Notiophilus

Registered User.
Local time
Today, 10:27
Joined
Jan 18, 2015
Messages
42
Wrote it into debug. What alerted me was that ctl.Tag (which defaults to "") <> Nz(ctl.DefaultValue,""), so the bookmark code was storing a bunch of empty controls unnecessarily.
(eta: also damn you're fast)
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 04:27
Joined
Feb 28, 2001
Messages
26,946
According to the MS writeup on NZ, the NZ function in a query will always return a zero-length string (ZLS) if you don't supply the 2nd argument of the call (which you don't). In query context using concatenation, you might not be able to tell the difference between a true NULL and a ZLS. That is, you can put either one between quotes via concatenation and they will concatenate to the same result.


EDIT: Note that if you had a NULL, it is ALWAYS not equal to anything - including another NULL. Basing a conclusion on matching or not matching a NULL is not necessarily a good idea because NULL just doesn't play well with other data.
 
Last edited:

theDBguy

I’m here to help
Staff member
Local time
Today, 02:27
Joined
Oct 29, 2018
Messages
21,322
Wrote it into debug.
Still curious, can you please show us exactly what you wrote? Even better if you could also show the result by posting a screenshot of the Immediate Window. Thanks.
 

Notiophilus

Registered User.
Local time
Today, 10:27
Joined
Jan 18, 2015
Messages
42
EDIT: Note that if you had a NULL, it is ALWAYS not equal to anything - including another NULL. Basing a conclusion on matching or not matching a NULL is not necessarily a good idea because NULL just doesn't play well with other data.
I'd heard that before, but I keep forgetting it. Good to be reminded!

@theDBguy, here you go:
Code:
    For Each ctl In Me.Form.Controls
        If ctl.ControlType = acTextBox Or ctl.ControlType = acComboBox Or ctl.ControlType = acCheckBox Then
            If ctl.Tag & "" <> ctl.DefaultValue & "" Then
                strStoredControls = strStoredControls & ctl.Name & "¬" & ctl.Tag & "|"
                Debug.Print "*" & ctl.Name & ": " & ctl.Tag & "|" & Nz(ctl.DefaultValue, "")
            Else
                Debug.Print ctl.Name & ": " & ctl.Tag & "|" & Nz(ctl.DefaultValue, "")
            End If
        End If
    Next

Immediate window printout:
Code:
*txtSearch: |Null
*chkFavourite: |Null
cboSortBy: 1|1
*chkCopied: |Null
*chkCalibre: |Null
*chkEdited: |Null
*chkComplete: |Null
*cboLengthFilter: 5|
cboAuthorFilter: |
 

Notiophilus

Registered User.
Local time
Today, 10:27
Joined
Jan 18, 2015
Messages
42
...the default value is "Null". Not Null, but "Null". It's a string. Jesus Christ on a bike.

I create my own problems.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 02:27
Joined
Oct 29, 2018
Messages
21,322
...the default value is "Null". Not Null, but "Null". It's a string. Jesus Christ on a bike.

I create my own problems.
Hi. Glad to hear you got it sorted out. Just FYI for others, a Null value prints out as nothing/blank (looks like an empty string) in the Immediate Window.
 

Users who are viewing this thread

Top Bottom