1. Forum
  2. USENET
  3. comp.databases.ms-sqlserv

  • Is this the same as NZ() ?

    From internet.shopping@internet.shopping@foobox.com to comp.databases.ms-access on Mon Sep 14 04:18:25 2020
    From Newsgroup: comp.databases.ms-sqlserv

    I am looking at another person's code. In fact I have to amend it!!

    There is a line that I would not write and am wondering exactly what it means:

    If ctl.Value & "" = "" Then

    Is it the same as

    If nz(ctl.Value,"") = "" then ?

    If not, how does it differ?

    Jim




    --- Synchronet 3.21d-Linux NewsLink 1.2
  • From Ron Paii@ron81pai@gmail.com to comp.databases.ms-access on Mon Sep 14 05:09:56 2020
    From Newsgroup: comp.databases.ms-sqlserv

    On Monday, September 14, 2020 at 6:18:29 AM UTC-5, internet...@foobox.com wrote:
    I am looking at another person's code. In fact I have to amend it!!

    There is a line that I would not write and am wondering exactly what it means:

    If ctl.Value & "" = "" Then

    Is it the same as

    If nz(ctl.Value,"") = "" then ?

    If not, how does it differ?

    Jim

    ctl.Value & "" will return vbnullstring if ctl.Value is null because string concatenation does not error on null.

    nz(ctl.Value, "") will return vbnullstring if ctl.Value is null because you explicitly called it out.

    --- Synchronet 3.21d-Linux NewsLink 1.2
  • From internet.shopping@internet.shopping@foobox.com to comp.databases.ms-access on Mon Sep 14 05:37:02 2020
    From Newsgroup: comp.databases.ms-sqlserv

    On Monday, 14 September 2020 13:10:01 UTC+1, Ron Paii wrote:
    On Monday, September 14, 2020 at 6:18:29 AM UTC-5, internet...@foobox.com wrote:
    I am looking at another person's code. In fact I have to amend it!!

    There is a line that I would not write and am wondering exactly what it means:

    If ctl.Value & "" = "" Then

    Is it the same as

    If nz(ctl.Value,"") = "" then ?

    If not, how does it differ?

    Jim

    ctl.Value & "" will return vbnullstring if ctl.Value is null because string concatenation does not error on null.

    nz(ctl.Value, "") will return vbnullstring if ctl.Value is null because you explicitly called it out.

    If I understand you correctly, they each result in the same value.

    I will therefore stick with nz(), which to my mind is clearer. It does not rely on knowing the behaviour of string concatenation under these circumstances.

    I hate 'tidying' up someone else's code when I am not sure that I am not altering the behaviour

    Jim
    --- Synchronet 3.21d-Linux NewsLink 1.2
  • From Ron Paii@ron81pai@gmail.com to comp.databases.ms-access on Mon Sep 14 05:56:36 2020
    From Newsgroup: comp.databases.ms-sqlserv

    On Monday, September 14, 2020 at 7:37:09 AM UTC-5, internet...@foobox.com wrote:
    On Monday, 14 September 2020 13:10:01 UTC+1, Ron Paii wrote:
    On Monday, September 14, 2020 at 6:18:29 AM UTC-5, internet...@foobox.com wrote:
    I am looking at another person's code. In fact I have to amend it!!

    There is a line that I would not write and am wondering exactly what it means:

    If ctl.Value & "" = "" Then

    Is it the same as

    If nz(ctl.Value,"") = "" then ?

    If not, how does it differ?

    Jim

    ctl.Value & "" will return vbnullstring if ctl.Value is null because string concatenation does not error on null.

    nz(ctl.Value, "") will return vbnullstring if ctl.Value is null because you explicitly called it out.
    If I understand you correctly, they each result in the same value.

    I will therefore stick with nz(), which to my mind is clearer. It does not rely on knowing the behaviour of string concatenation under these circumstances.

    I hate 'tidying' up someone else's code when I am not sure that I am not altering the behaviour

    Jim

    Because I have learned to NOT to trust VBA automatic type conversion, I would do.

    if cstr(nz(ctl.value, vbnullstring) = vbnullstring then

    if you want to handle user entering a space or 2.

    if trim$(nz(ctl.value,vbnullstring)) = vbnullstring then

    Which WILL change the behavior.

    --- Synchronet 3.21d-Linux NewsLink 1.2