assign the Null value to a string

[smig]

as assigning a null value to a string and number variables is not possible, what is better ?
declare these variables as Variant type, or checking for a null value before trying to assign the value to he variable.

checking for a null value before trying to assign the value to he variable require more code to be writen and proccessed, and taking this approach is sure more vanurable for errors.
I'm sure there are good reasons not declaring all variables as Variant.

 

[boblarson]

Just use the Nz function where you want to ensure no nulls>

for numbers:

Nz([FieldorControlName],0)

for text:

Nz([FieldorControlName],"")

for dates:

Nz([FieldorControlName],0) ' you can use zero which is 12/31/1899 or you can use a specific date for a default
Nz([FieldorControlName],#1/1/2008#)

 

[smig]

thanks

is it OK to use this code:

dim str_MyString as string
 
 
str_MyString = nz(blablabla...)

or do I must assign it a "" value for null value ?

 

[gemma-the-husky]

essentially you are saying, with nz

if isnull(str_MyString) then mystring = "somedefaultstring"

the usage is

str_mystring = nz(str_mystring,"somedefaultstring")

the nice thing about nz is that it is overloaded and works with any variable type.

the PROBLEM is that you cannot tell whether a string is null, or a zero length string by inspection - so in the above case, if the string WAS a zls, it wont change, and you will still end up with a zls

so you are better to do


mystrg = nz(mystrg,"")
if mystrg = "" then mystrg = "somedefaultstring"


you get this problem with dates especially. a datetype is numeric, and therefore is not a zls, but may have a default of 0 - which actually corresponds to a date of #30/12/1899#. so you can deal with this by this sort of thing


mydate = nz(mydate,0)
if mydate = 0 then mydate = #somedefaultdate#



its just being aware of exactly how you want to handle nulls, and default values for certain fields/controls

 

[vbaInet]

I think smig was asking whether the zls argument was optional or not. If that's the case then yes it is optional. The default is "" which is the zero length string. For brevity, it's best to put something in there. Always check the help files for things like this.

As for handling dates, I would opt for IsDate() because it will handle Null cases as well.

 

[ChipperT]

essentially you are saying, with nz

if isnull(str_MyString) then mystring = "somedefaultstring"

the usage is

str_mystring = nz(str_mystring,"somedefaultstring")

the nice thing about nz is that it is overloaded and works with any variable type.

the PROBLEM is that you cannot tell whether a string is null, or a zero length string by inspection - so in the above case, if the string WAS a zls, it wont change, and you will still end up with a zls

so you are better to do


mystrg = nz(mystrg,"")
if mystrg = "" then mystrg = "somedefaultstring"


you get this problem with dates especially. a datetype is numeric, and therefore is not a zls, but may have a default of 0 - which actually corresponds to a date of #30/12/1899#. so you can deal with this by this sort of thing


mydate = nz(mydate,0)
if mydate = 0 then mydate = #somedefaultdate#



its just being aware of exactly how you want to handle nulls, and default values for certain fields/controls

Not sure why that would be better than
if (isNull(mystrng)) or mystrng="" Then
mystrng="somedefaultstring"
end if

or

if (isNull(mydate)) or mydate=0 Then
mydate="#somedefaultdate#
end if

 

[boblarson]

Not sure why that would be better than
if (isNull(mystrng)) or mystrng="" Then
mystrng="somedefaultstring"
end if

or

if (isNull(mydate)) or mydate=0 Then
mydate="#somedefaultdate#
end if

shorter code. Usually I use this to handle nulls and empty strings:

If Len(SomethingHere & "") > 0 Then

as a test to make sure something isn't null or empty.

Chipper there is nothing wrong with the code you have (although you don't need the parens), but it is longer than using the NZ function which can be nice and compact. But the result would be the same. (also your date one has a double quote where it shouldn't (probably a typo, eh?)

 

[ChipperT]

shorter code. Usually I use this to handle nulls and empty strings:

If Len(SomethingHere & "") > 0 Then

as a test to make sure something isn't null or empty.

Chipper there is nothing wrong with the code you have (although you don't need the parens), but it is longer than using the NZ function which can be nice and compact. But the result would be the same. (also your date one has a double quote where it shouldn't (probably a typo, eh?)

Yep, you are right about the typo. It has been awhile since I worried about the "theory" end of things but I learned many moons ago that it is quicker and more efficient evaluate two booleans in a single if then statement (and/or, etc) than it is to call a function. Of course it probably does not amount to more than a couple of nanoseconds these days.

I also prefer the Len(SomethingHere & "") > 0 route as it is more efficient still and even if I were to go to the route I laid out the if then statement would all be on one line. I always include parents on a multi-part evaluation just for readbility. Again it is merely a quibble and style.

 

[smig]

thank you all

for dates I do use the IsDate() function.

I looked for a short code for strings
on some cases I use the DLookUp function to set a value to a string, and checking if it's null or not before asigning it to the string seems to be too long code and spending too much resources.

when a variable declared as string it already get the value of "", that's why I asked if assigning the "Null" value in the Nz() function is optional.

 

[gemma-the-husky]

the op asked whether he could use nz to assign a value OTHER than a zls

i was trying to point out that this alone would not detect a value that was ALREADY a zls, and would just leave it untouched - which he may not have appreciated.

and not particularly suggest any certain method of testing for a zls.

 

[ChrisO]

If we look at the code in post #3 we should ask; how could a String ever contain a Null?

If a String can not contain a Null then why use the Nz() function on it because the Nz() function will always evaluate to False with a String?

 

[boblarson]

If we look at the code in post #3 we should ask; how could a String ever contain a Null?

If a String can not contain a Null then why use the Nz() function on it because the Nz() function will always evaluate to False with a String?

But you might have a NULL Control value which you are trying to assign to a String and that is where you need to use the NZ function (or something else to avoid trying to assign a null where it can't be assigned).

If you are assigning something from a string variable then, yes, it can't be null. But assigning TO a string variable CAN run into problems of NULL values.

 

[ChrisO]

In which case the Nz() function uses the Value of the Control, which can be Null, not the String that might attempt to receive the Null.

What argument do we put inside the Nz() function; the Control or the String?

 

[boblarson]

In which case the Nz() function uses the Value of the Control, which can be Null, not the String that might attempt to receive the Null.

What argument do we put inside the Nz() function; the Control or the String?

If I was using it in code it would be

strVariable = NZ(Me.Controlname,"")

 

[ChrisO]

Of course because the Control might contain the Null not the String.

But so many posts so far have put the String inside the Nz() function and the String can not contain a Null.

 

[ChrisO]

Sub Test_1()
    Dim vntX As Variant
    Dim strY As String

    strY = Nz(vntX, "Some Value")
    MsgBox strY     ' ZLS
    
    vntX = Null
    strY = Nz(vntX, "Some Value")
    MsgBox strY     ' Some Value
    
    vntX = ""
    strY = Nz(vntX, "Some Value")
    MsgBox strY     ' ZLS
    
    vntX = Empty
    strY = Nz(vntX, "Some Value")
    MsgBox strY     ' ZLS
    
End Sub


Sub Test_2()
    Dim vntX As Variant
    Dim strY As String

    strY = IIf(Len(vntX), vntX, "Some Value")
    MsgBox strY     ' Some Value
    
    vntX = Null
    strY = IIf(Len(vntX), vntX, "Some Value")
    MsgBox strY     ' Some Value
    
    vntX = ""
    strY = IIf(Len(vntX), vntX, "Some Value")
    MsgBox strY     ' Some Value
    
    vntX = Empty
    strY = IIf(Len(vntX), vntX, "Some Value")
    MsgBox strY     ' Some Value

End Sub

 

[smig]

peace brothers

I got the answer for my question.

for me it was clear few posts ago that I must use this:
strVariable = NZ(Me.Controlname,"")
or
strVariable = NZ(SomeFunctionThatCanReturnANull,"")
as both of this try to assign a null value to a string variable.


thanks

 

[gemma-the-husky]

I think the distinction between field, control and variable can often be blurred, or inaccurate

I am sure I tend to use the generic "string" when really i mean a control or field of type "string". And I am sure that without thinking, I often do unnecessarily test variables for null!

I assume the OP is talking about something that could be a null.

 

[ChrisO]

Sub Test()
    Dim vntX As Variant
    Dim strY As String

    strY = Nz(vntX, "Some Value")
    MsgBox strY     ' ZLS

    strY = IIf(Len(vntX), vntX, "Some Value")
    MsgBox strY     ' Some Value

End Sub

Yes but what I'm also talking about is with the above code.

strY = Nz(vntX, "Some Value")

vntX As Variant is Dim'ed and is not assigned a value.
If we use the Nz() function on it it returns a ZLS not the default we would like.
vntX is not Null but Empty and so the Nz() function equates to False.
Since it equates to False vntX is returned not the default.
The Empty value of vntX is type cast to a String because the MsgBox expects a String.
It raises no error but is probably not what the user had in mind.



strY = IIf(Len(vntX), vntX, "Some Value")

Again, vntX As Variant is Dim'ed and is not assigned a value.
The Len() of vntX equates to False and so the IIF() function returns the default.
I suspect that the return of the default is what the user had in mind.
Len(Null), Len(ZLS) and Len(Empty) all equate to False so the IIF() function returns the default.

So, if we use the IIf(Len(vntX),True,False) construct we cover all three cases.

 

[smig]

@gemma - I talked about variables that declared as string.
these can't get a null value.

sometimes I try to assign them a null value either by assigning them data from a field on a form or assigning them a result of a function.
in these cases using the Nz() function is by for more simple then testing if the assigned data is null before trying to assign it to the variable.

strVariable = NZ(Me.Controlname,"")
is by far more simple then:
strVariable = Iif(IsNull(Me.ControlName)=false, Me.ControlName, "")

or:
strVariable = NZ(LongFunctionWithVariables(Var1, Var2),"")
is by far more simple then:
strVariable = Iif(IsNull(LongFunctionWithVariables(Var1, Var2))=false, &_
LongFunctionWithVariables(Var1, Var2), "")