What is the personal game, are posters not permitted to agree or disagree with each other? You made it personal with your comment about Opt ion Explicit, if VIP s are going to insult each other they should do it in the VIP thread not in a users thread.
Rain we were not challenging any technical issue, at least I wasn't and I don't think John was, but the semantics. To the user Nz returns a 0 or a ZLS dependent upon the assignment of the variable, I doubt that he is worried about the internals of this or any other function.
My one question to Chris is if Nz returns empty how do I see this?
Rain we were not challenging any technical issue, at least I wasn't and I don't think John was, but the semantics. To the user Nz returns a 0 or a ZLS dependent upon the assignment of the variable, I doubt that he is worried about the internals of this or any other function.
Brian
All this started because Big John posted a link to a site that Chris believed to be incorrect.
Code:
As an a addendum to CJ_London's reply you can find out more about the Nz() function [URL="http://www.techonthenet.com/access/functions/advanced/nz.php"][COLOR=#0066cc]here[/COLOR][/URL].
Chris did not think that that was OK, so he posted his belief that it was not correct.
We do want the OP to be given correct information if possible.
I believe that I said Chris tested his code before posting and that he also tested Big John's code that he linked to. Chris likes to make sure he has not made a mistake.
Brian did you do any testing before you came up with an opinion. If you did then I congratulate you. On the other hand, if you did not test you have put yourself into a position of simply taking sides.
Finally, we do not know exactly what the OP wanted. Did he want a get out of Jail Free Card so he could simply move on? Or did he want some sort of explanation of the fault.
Big John attempted to give an explanation, which Chris believed to be incorrect so he posted what he believes to be correct.
The only way to find the truth is to test both sets of code and if you do not understand, come back here and ask both Big John and ChrisO for further detail.
So with that been said, that is about all I can comment on, Chris can look after his own.
Brian what are the "semantics" you brought up. I am not sure how you meant to use the word.
Other than that, we have just started Saturday morning and the forecast looks promising. So I am chasing a good weekend. Actually, a double good weekend as both my kids are home.
I hope you have a good one as well. Your summer sports season must be well under away.
Rain I tested nothing as there was nothing to test. I was agreeing with what John posted in post 5 , I would challege nobody on techy facts as what little I knew has faded over the 7 years of retirement, which is why I nolonger show the vip title.
The semantics are that John, and I, say that the Nz returns a 0 or ZLS dependent upon the assignment of the variable the user is using it on. Chris is saying it always returns empty and then the value in the variable is determined by the assignment of that variable, this seems to be a very fine distinction and in my opinion not worth raising in this thread but by all means discuss the finer points in a thread dedicated to it, here it may well confuse a novice and NZ has been discussed before without Chris jumping in.
I think that I may not have posted if Chris's posts had been less aggressive.
Have a good weekend, the weather here is wet so I will be spending it indoors with the grandkids (3yrs and 9months) rather than in the parks.
Lets get the facts right. 1.)A question was asked by Heatshiver 2.)Answered by CJ London. (This should have been the end) 3.)John Big Booty come in with a Link to another site. 4.)ChrisO looks at the link and realises that the link is not purely correct. 5.)ChrisO posts the point that John’s Link is flawed. 6.)John Booty does not understand the difference and says so. 7.)ChrisO tries to explain better 8.)John appears to be confused and becomes aggressive.
It is as plain as the nose on your face that John is the agressor
The semantics are that John, and I, say that the Nz returns a 0 or ZLS dependent upon the assignment of the variable the user is using it on.
Chris is saying it always returns empty and then the value in the variable is determined by the assignment of that variable, this seems to be a very fine distinction and in my opinion not worth raising in this thread but by all means discuss the finer points in a thread dedicated to it, here it may well confuse a novice and NZ has been discussed before without Chris jumping in.
It isn't a "fine point". The fact is that the return when the optional argument is omitted is Empty which neither a ZLS nor zero. It is cast automatically to those values because of the context, just as any other datatype can be cast automatically.
If we accept your claim that Nz returns a different datatype depending on the context and the argument was just semantics I could equally claim that all functions return a datatype that depends on their context.
The following code is trivial but you and JBB both seem to need it spelled out because you are unwilling to accept what Chris has already shown utterly unambiguously.
Code:
Dim x As String
x = InStr("abc","a")
y = InStr("abc","a")
Debug.Print TypeName(x)
Debug.Print TypeName(y)
Look! Look! InStr() returns a data type that depends on the context. Sometimes it is an Long sometimes it is a String.
Now how long do you think my post would go unchallenged if I seriously suggested that? Yet it is no different from what you have claimed about Nz(). The only difference is that you are backed by Microsoft's wrong information.
VBE Help: "If the value of the variant argument is Null, the Nz function returns the number zero or a zero-length string (always returns a zero-length string when used in a query expression), depending on whether the context indicates the value should be a number or a string."
Indeed as far as I can determine, Chris is the only person ever to have pointed this out online. It needs to be pointed out to Microsoft so they can fix the documentation.
Here is a representation of the Nz function definition which matches its behaviour:
Code:
Public Function Nz(Value As Variant, Optional ValueIfNull As Variant) As Variant
If Not IsNull(Value) Then
Nz = Value
ElseIf Not IsMissing(ValueIfNull) Then
Nz = ValueIfNull
Else
Nz = Empty
End If
End Function
Now please define the atlernative that returns ZLS or Zero depending on the context.
Here is a start for you:
Code:
Public Function Nz(Value As Variant, Optional ValueIfNull As Variant) As String or Long or Integer or Byte or Double or Single
Galaxiom has presented a correct and an explicit representation of the Nz() function.
It leads us to the outcome that the Nz() function returns Empty.
Now I will go down the path of the implicit outcome:-
Code:
Sub TestIt_1()
Dim X As Variant
Select Case IsEmpty(X)
Case True
MsgBox "X is Empty"
Case Else
MsgBox "X is Not Empty"
End Select
End Sub
Sub TestIt_2()
Dim X As Variant
X = Null
Select Case IsEmpty(X)
Case True
MsgBox "X is Empty"
Case Else
MsgBox "X is Not Empty"
End Select
End Sub
Sub TestIt_3()
Dim X As Variant
X = Null
Select Case IsEmpty(Nz(X))
Case True
MsgBox "X is Empty"
Case Else
MsgBox "X is Not Empty"
End Select
End Sub
In the above examples, the implicit outcome is that the Nz() function returns Empty.
The point being is that, by the explicit and implicit, both paths lead to the same conclusion. When two distinct paths lead to the same conclusion then that conclusion becomes almost irresistibly correct.
Since I introduced the word into this thread I’ll give it a shot.
I post #25, Galaxiom users the line of code ‘Nz = Empty’ in the else clause to explicitly set the value of his Nz function to Empty. It was done for educational purposes to explicitly show what is happening.
In post #26, I used the line of code ‘Select Case IsEmpty(Nz(X))’ which implicitly returns Empty from the Nz function. Nowhere in my code does anything get set to Empty.
Yet in both cases, #25 and #26, the Nz function returns Empty.
This is why I think it is important to point this out. If the Empty is returned implicitly then we should be able to make an assumption that it is not required to explicitly set the return value to Empty.
But that would be just an assumption and so we go and test it. If the assumption is correct then we should be able to comment out that line in post #25. When we do that we find that the function still works the same. That line of code is there only for educational purposes, but it is not required.
So then we might ask “How does the return value get set to Empty?” The answer to that is that Empty is the default or uninitialised value of a Variant. That then might lead to the question “Then would that also apply to other Variants in VBA?” The answer to that question is yes.
Now as far as I am concerned, this is the whole point of this thread. It is not just a fine point or making a mountain out of a mole hill.
It is about getting the fundamentals correct. If the basics are wrong then we might get a quick fix but that quick fix can not lead on to other knowledge. All we will have is the quick fix.
If we get the fundamentals correct then that knowledge can lead on to other knowledge. It’s not just about the Nz() function it is about Variants in general. And it’s not just about the Nz() function and Variants, it is about all knowledge. Get the fundamentals correct and we can build on them. Get fundamentals wrong and we will continue to have to look for a quick fix.
