NZ Example from MLH
lylefair
http://www.ffdba.com/downloads/testingNZ3.dat
(rename .dat to .mdb)
or
http://www.ffdba.com/downloads/testingNZ3.mdb
(At time of posting I have not opened the file.)
David W. Fenton
news:1130330500.1...@g14g2000cwa.googlegroups.com:
THis shows that Nz() returns the two fields to type string, yes?
That is, just as it was alleged all along?
--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
Larry Linson
"David W. Fenton" <dXXXf...@bway.net.invalid> wrote
> THis shows that Nz() returns the two fields to type string, yes?
>
> That is, just as it was alleged all along?
Yes, it did for me, though a similar test I constructed worked as I had
expected. I haven't identified what, if anything is different. Before I
could try this, I had to repair my installation of Office 97, and did it
from a CD at the SR1 level -- which level may or may not have had any effect
on the problem.
Larry Linson
Microsoft Access MVP
Rick Brandt
> "lylefair" <lylefa...@aim.com> wrote in
> news:1130330500.1...@g14g2000cwa.googlegroups.com:
>
>> The file is now available as
>> http://www.ffdba.com/downloads/testingNZ3.dat
>> (rename .dat to .mdb)
>> or
>> http://www.ffdba.com/downloads/testingNZ3.mdb
>> (At time of posting I have not opened the file.)
>
> THis shows that Nz() returns the two fields to type string, yes?
>
> That is, just as it was alleged all along?
But he's passing the alias name of the calculated fields to TypeName()
instead of the actual Nz() expression within those fields. Is that
legitimate?
--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com
David W. Fenton
news:D1S7f.3119$I65.3@trnddc01:
Lyle's first test database was simply demonstrating that TypeName()
coerces data types according to the rules of variant subtypes. So
his insistence that he wasn't seeing Nz() returning strings was
actually incorrect.
At least, so far as I can tell.
David W. Fenton
news:b2S7f.7194$tV6....@newssvr27.news.prodigy.net:
> David W. Fenton wrote:
>> "lylefair" <lylefa...@aim.com> wrote in
>> news:1130330500.1...@g14g2000cwa.googlegroups.com:
>>
>>> The file is now available as
>>> http://www.ffdba.com/downloads/testingNZ3.dat
>>> (rename .dat to .mdb)
>>> or
>>> http://www.ffdba.com/downloads/testingNZ3.mdb
>>> (At time of posting I have not opened the file.)
>>
>> THis shows that Nz() returns the two fields to type string, yes?
>>
>> That is, just as it was alleged all along?
>
> But he's passing the alias name of the calculated fields to
> TypeName() instead of the actual Nz() expression within those
> fields. Is that legitimate?
Well, sure, and it's demonstrating that the original claim that his
first sample db was supposed to refute was actually true -- Nz()
returns strings.
lylefair
Public Function HowEvaluationServiceandAccessDealWithaVariant(ByVal
SomeField As Variant) As Variant
HowEvaluationServiceandAccessDealWithaVariant = 23
End Function
Run this query:
SELECT HowEvaluationServiceandAccessDealWithaVariant([VehicleJobID]) AS
Case3, VarType([Case3]) AS Type3, TypeName([Case3]) AS TypeNam1
FROM tblENF262SpecificInfo;
What do we find? We find that Access/Jet/Expression Service has no way
of knowing what the type to assign to a field when the only information
it has is the value of the field stored in a variant, (or pointed to by
a variant) and that it defaults the field type to text.
This is what MLH tested, and not the return type of Nz([FieldValue],0).
*********************
Run this query:
SELECT Nz([VehicleJobID],0) AS Case1, Nz([LaborCost],0) AS Case2,
VarType(Nz([VehicleJobID],0)) AS Type1, VarType(Nz([LaborCost],0)) AS
Type2, TypeName(Nz([VehicleJobID],0)) AS TypeNam1,
TypeName(Nz([LaborCost],0)) AS TypeNam2
FROM tblENF262SpecificInfo;
What do we find? We find that Nz([FieldValue],0) returns numeric types,
just as we expected and just as we have assumed for years and years.
*****************
MLH's original query is flawed. It does not test NZ; it tests something
ENTIRELY different.
lylefair
lylefair
calculations as this query shows:
SELECT Nz([VehicleJobID],0)+Nz([LaborCost],0) AS Sum1
FROM tblENF262SpecificInfo;
The notion that we are going to run this query
SELECT Nz([VehicleJobID],0) AS Case1, Nz([LaborCost],0) AS Case2
FROM tblENF262SpecificInfo;
and later add Case1 and Case2 is absurd. Either do all your conversion
and all your calculaton in the query, or do it all in VBA, or the
report or form, but don't mix them. It's entirely unfair to say Nz does
not act as it should because it fails to compensate for shoddy
programming.
Rick Brandt
> "Larry Linson" <bou...@localhost.not> wrote in
> news:D1S7f.3119$I65.3@trnddc01:
>
> > "David W. Fenton" <dXXXf...@bway.net.invalid> wrote
> >
> > > THis shows that Nz() returns the two fields to type string, yes?
> > >
> > > That is, just as it was alleged all along?
> >
> > Yes, it did for me, though a similar test I constructed worked as
> > I had expected. I haven't identified what, if anything is
> > different. Before I could try this, I had to repair my
> > installation of Office 97, and did it from a CD at the SR1 level
> > -- which level may or may not have had any effect on the problem.
>
> Lyle's first test database was simply demonstrating that TypeName()
> coerces data types according to the rules of variant subtypes. So
> his insistence that he wasn't seeing Nz() returning strings was
> actually incorrect.
>
> At least, so far as I can tell.
The help file in A97 indicates that TypeName should only be used against "named
variables". I suspect as you that when used in any other case that it is
actually coercing to a type based on some internal rules and not actually
telling us anything about the original variant other than what type it "could
be".
lylefair
I think not. So Jet has to use "something" and it chooses to use Text
(as it always does when it doesn't know what to do, (which is why we
sometimes get text fields for memo fields in queries). The notion that
NZ returns a string is nonsense. It returns a Variant. Look at the
Object Model.
lylefair
message of October 26 from which it seems to be dangling.
David W. Fenton
news:1130390607.5...@g43g2000cwa.googlegroups.com:
> Of course, it isn't. It's total 100% bull shit!
You know, these quoteless replies are pretty much useless. I had to
look up the message it was a reply to (which I'd read yesterday, and
just didn't happen to remember, as I'm not in the habit of
memorizing MessageIDs and the content associated with them).
The sentence that provoked the comment above was:
But he's passing the alias name of the calculated fields to
TypeName() instead of the actual Nz() expression within those
fields. Is that legitimate?
In that context, your comment makes absolutely no sense, as what
you're doing seems to me to be 100% legitimate.
David W. Fenton
news:1130416308.1...@g14g2000cwa.googlegroups.com:
> This is just bunk. NZ returns a variant. . . .
Of subtype string, in the case we're looking at.
> . . . Is there a JET field
> variant? I think not. So Jet has to use "something" and it chooses
> to use Text (as it always does when it doesn't know what to do,
> (which is why we sometimes get text fields for memo fields in
> queries). The notion that NZ returns a string is nonsense. It
> returns a Variant. Look at the Object Model.
But *you* said it returned the original data type. Whether it
returns a string or a variant of subtype string (neither of which is
what you say above) is really not relevant, since what *you* were
claiming (that Nz() on a numeric field returns a numeric type) is
not by any stretch of the imagination correct.
David W. Fenton
news:1130416441.3...@g43g2000cwa.googlegroups.com:
> This message is a reply to RB's message of October 27, not to his
> message of October 26 from which it seems to be dangling.
Why not just quote what you're replying to, Lyle? Doesn't your
browser allow you to select text and copy it to the clipboard for
pasting into your reply?
David W. Fenton
news:1130390512.2...@o13g2000cwo.googlegroups.com:
> MLH's original query is flawed. It does not test NZ; it tests
> something ENTIRELY different.
MLH made no such claims. So far as I can see, he just asked how to
get string values converted back to numeric values. The fact that
Nz() returns a variant of subtype string (which you continually
misstate as "returns a variant") is something you said was *not* the
case in your original responses to MLH. You then attempted to
demonstrate that you were correct by using TypeName(), which takes a
variant as its argument, and thus identifies variant subtypes
instead of the actual data type.
The original problem was that MLH needed to replace Nulls with
zeros, and the result was that the whole column ended up with
strings (the subtype of a value stored in a variant controls what
type is returned when you look at the value stored in the variant).
It was a literal value, not a variable, so what he was dealing with
was actual strings (variants only exist in code, as variants are a
VBA data type, as you point out in another post).
So, the problem was that applying Nz() to an entire column was
returning strings (not variants, which, as you point out, don't
exist as a Jet or SQL data type).
But you said it was *not* returning strings on your computer, and
posted sample databases that endeavored to show this. All those
showed was that many Access functions implicitly coerce data types.
There are two solutions to MLH's problem:
1. explicitly coerce the string back to a numeric value (wrap the
Nz() in Val()), OR
2. eliminate the requirement for converting Nulls to zero by storing
zero instead of Null.
Which is the best for MLH choice will depend on his application.
But your claim that the problem did not exist was wrong from the
beginning -- you were not testing what you thought you were testing.
David W. Fenton
news:1130393104.4...@z14g2000cwz.googlegroups.com:
Your example relies on implicit type coercion, which I would say is
never a good idea (in my opinion).
I think there's something fundamentally flawed in a schema which
allows Nulls in a numeric field that is going to be used in
calculations.
Would anyone allow Nuil in a sales tax field, for instance? Of
course not! You'd always set a default value of zero.
The only thing lost is the ability to distinguish between a field
that has not been filled out yet and one that is filled out as 0. In
most cases involving numbers of this type, that distinction is of no
use. If it *is* important, then you have to deal with the Nulls.
lylefair
returns a variant; it does not return a variant of subtype string. This
is the end of my conrtibution to this string. Its other contributors
and you can believe whatever you, please; this will not change what
actually happens. There is no remedy for stupidity, and there is no
remedy for those who deliberately misrepresent what they read, and then
pontificate about it.
There is NO problem with Nz, nor has there ever been. There are
problems with incompetent and shoddy programmers and always will be.
rkc
> "lylefair" <lylefa...@aim.com> wrote in
> MLH made no such claims. So far as I can see, he just asked how to
> get string values converted back to numeric values. The fact that
> Nz() returns a variant of subtype string (which you continually
> misstate as "returns a variant") is something you said was *not* the
> case in your original responses to MLH.
NZ does not always return a variant of type string except when used in
a query.
MLH
I told you.
David W. Fenton
news:1130444123.3...@f14g2000cwb.googlegroups.com:
> You're quite stupid, David and not worth debating anything with.
> Nz returns a variant; . . .
I have examined the help file topics on Nz() in both A97 and A2K,
and I don't see anywhere in those help topics anything that suggests
that Nz() returns a variant, subtyped or not. There is nothing at
all definite about return types.
> . . . it does not return a variant of subtype
> string. . . .
Indeed, it appears that you are right about that. Of course, it
seems to me that you are also wrong that it returns a variant.
I think my original statement was likely correct, at least for
queries, that Nz() returns a string.
It's interesting to compare the Nz() help topics for A97 and A2K.
A97 says this (in small part):
If the value of the variant argument is Null, the Nz function
returns the number zero or a zero-length string, depending on
whether the context indicates the value should be a number or a
string. If the optional valueifnull argument is included, then
the Nz function will return the value specified by that
argument if the variant argument is Null.
A2K help says (emphasis added):
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. If the optional
valueifnull argument is included, then the Nz function will
return the value specified by that argument if the variant
argument is Null. **When used in a query expression, the NZ
function should always include the valueifnull argument,**
This points up a major issue that has been largely elided in the
present discussion: Nz() works differently in VBA code than it works
in queries.
> . . . This is the end of my conrtibution to this string. Its
> other contributors and you can believe whatever you, please; this
> will not change what actually happens. . . .
YOu haven't proven that Nz() returns a variant, only that TypeName
accepts a variant as its input, of the subtype appropriate to the
value you passed it, and then returns a type name that is
appropriate. And it's quite obvious that the process of passing the
incoming value through the variant parameter coerces its value to
the narrowest possible data type.
Of course, TypeName is not documented to work for anything but
VARIABLES, so it's use in your example is questionable to begin
withi.
> . . . There is no remedy for
> stupidity, and there is no remedy for those who deliberately
> misrepresent what they read, and then pontificate about it.
> There is NO problem with Nz, nor has there ever been. There are
> problems with incompetent and shoddy programmers and always will
> be.
I know you refuse to continue the discussion, but the above seems to
me to imply that you somehow believe that Nz([NumbericField], 0)
returns a numeric value and not a string. Or you believe that it
returns a variant, I suppose. Your demo database proved that it does
*not* return a numeric data type. What it proved abouve variants, I
can't say -- you seemed not to be interested in offering commentary
on what your last demo MDB was designed to show.
You seem to me to be more off track on this subject than on anything
you've ever written about in this newsgroup. Your posts in this
thread have been unclear as to intention and without foundation in
the documentation for Jet, Access or VBA. Your demos appear to me to
demonstrate exactly the opposite of your original claim, yet, you
attack *me* as being stupid.
It's quite clear:
1. In VBA code, Nz() returns the data type of the input variable or
a string.
2. Nz() returns a string in queries. Period.
3. in form/report controlsources, the returned data type varies, and
is not the same as in VBA code.
I've added a form to Lyle's last demo database that demonstrates how
2 & 3 differ from each other. It is zipped up here:
http://dfenton.com/DFA/download/Access/testingNZ3DWF.zip
It shows that the underlying data type is never passed through
directly, and that Nz() in queries always returns a string. I could
have set it up to compare the values to VBA, but my eyes are really
bothering me today and I can't do a whole lot of coding (can't see
clearly).
David W. Fenton
news:lHb8f.23710$Bv6....@twister.nyroc.rr.com:
MLH's question was about a query.
And so far as I can see, there's no documentation at all to show
that Nz() ever returns a variant, per se. It returns different data
types in different contexts, and my point is to simply demonstrate
that Lyle was wrong in what he categorically declared to be the
case.
In fact, he was wrong twice.
rkc
>
> And so far as I can see, there's no documentation at all to show
> that Nz() ever returns a variant, per se. It returns different data
> types in different contexts, and my point is to simply demonstrate
> that Lyle was wrong in what he categorically declared to be the
> case.
<quote from MSDN article>
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.
</quote quote from MSDN article>
If a function can return either a number or a string then by
definition it's return value is a Variant. How can you continue
to argue about that?
Rick Brandt
Because in a query (what this thread has been talking about all along) it is
simply incorrect. Have you never found a flaw in the help file or other docs
before? Even your own quote talks about "context" and in the absence of
anything to provide that you get a string (okay, a variant that in every way
that matters acts like a string).
?Nz([SomeNullField, 0) + 1
1
?Nz([SomeNullField, 0) & 1
01
Clearly the output is a variant *Needing* some context to establish type and if
none is provided then it will default to a string. I have no issue with this
behavior and I only noticed it quite by accident several years ago when I
noticed how the output of Nz() sorted and justifed itself in a datasheet. My
only issue is that the help file suggests that the DataType of the first
argument provides context and most users will assume that the delimiters (or
lack thereof) on the second argument will provide context when (in a query) they
do not.
rkc
> rkc wrote:
>
>>David W. Fenton wrote:
>>
>>
>>>And so far as I can see, there's no documentation at all to show
>>>that Nz() ever returns a variant, per se. It returns different data
>>>types in different contexts, and my point is to simply demonstrate
>>>that Lyle was wrong in what he categorically declared to be the
>>>case.
>>
>><quote from MSDN article>
>>
>>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.
>>
>></quote quote from MSDN article>
>>
>>If a function can return either a number or a string then by
>>definition it's return value is a Variant. How can you continue
>>to argue about that?
>
>
> Because in a query (what this thread has been talking about all along) it is
> simply incorrect.
I don't read much of what MLH posts so I don't know what he asked. He
needs to read the Help file once in a while himself.
I came in where DWF was arguing about whether the Nz function returns
a Variant. The fact that in a query the value returned is always a
variant of type string does not make what the Nz function returns a
string. The Nz function can return an Array when used in circumstances
other than a query.
jimfo...@compumarc.com
I don't want to jump into the crossfire, but I guess I'd say that the
type of the return value is Null rather than Variant :-). David makes
an excellent distinction about Nz acting differently based on whether
it is used in VBA or in SQL. SQL does a pretty good job guessing how
expressions should be evaluated but doesn't seem to know much about
data types unless specified in PARAMETERS. We're used to Access using
Variant as the default type for non-Dim'ed variables in VBA so our
instincts lead us that way but SQL expressions might be more typeless
than VBA, relying more on its way of evaluating expressions than on
casting a combination of typed variables. Untyped variables like those
used in awk act similar to Variants. I'll be watching this thread
closely to see how SQL really does it. I've had situations where I've
had to use Nz(This, 0) instead of Nz(This) in queries, wondering why
Nz(This) didn't return 0 when 'This' was Null. Now I know why. Be
comforted in that these arguments are helping others.
James A. Fortune
David W. Fenton
news:pQf8f.24677$Bv6....@twister.nyroc.rr.com:
While the return value varies in type, that isn't the same thing as
returning a "vaiant," which term has a specific meaning in a VBA
context, and no meaning whatsoever in a query.
In any event, the context of th eoriginal question was a query,
where, once again, we see that NZ() returns a string, which is what
was said from the beginning, and which Lyle repeatedly denies, so
far as I can follow his points (he's both voluble in critising and
Sphynx-like in explaining his demo databases).
David W. Fenton
> establish type. . .
No, not at all. It's a string, and the + operator coerces strings to
numeric values, where possible (i.e., if IsNumeric() would return
True).
Now, it's not clear whether you mean in a query, or in a code
context. In a query, we know it's a string and always a string
that's returned. In VBA, it's different, though it is not the case,
as Lyle seems to me to have asserted on the front end, that Nz()
returns the underlying data type of the data passed it. My
alteration of Lyle's demo database proves this, in that different
uses of Nz() produce different results, none of which are exactly
the same as the underlying data types of the fields being processed
with Nz().
> . . . and if none is provided then it will default to a
> string. . . .
This is a problem that has been endemic in this thread -- a failure
to recognize when VBA functions are coercing data types. Lyle made
this mistake and now you're making it. The fact that + returns a
number doesn't mean that the Nz() is returning a number.
> . . . I have no issue with this behavior and I only noticed it
> quite by accident several years ago when I noticed how the output
> of Nz() sorted and justifed itself in a datasheet. My only issue
> is that the help file suggests that the DataType of the first
> argument provides context and most users will assume that the
> delimiters (or lack thereof) on the second argument will provide
> context when (in a query) they do not.
Seems to me that the help file is extremely hazy on how Nz() works.
It can be read as implying that the data type of the first argument
*can* provide sufficient context to reliably return an appropriate
data type, but that applies *only* to VBA, not to queries, something
that is not even mentioned in the A97 help file.
But it's clear that the data type returned even then is not
necessrily identical to the input data type, just compatible with
it.
David W. Fenton
news:FHg8f.112862$7b6....@twister.nyroc.rr.com:
> I don't read much of what MLH posts so I don't know what he asked.
> He needs to read the Help file once in a while himself.
>
> I came in where DWF. . .
Perhaps you could contribute more to the thread if you'd read it
first, just as you suggest that MLH should read the help files
before posting in ignorance of things he should be able to find out
for himself.
rkc
> rkc <r...@rochester.yabba.dabba.do.rr.bomb> wrote in
> news:FHg8f.112862$7b6....@twister.nyroc.rr.com:
>
>
>>I don't read much of what MLH posts so I don't know what he asked.
>>He needs to read the Help file once in a while himself.
>>
>>I came in where DWF. . .
>
>
> Perhaps you could contribute more to the thread if you'd read it
> first, just as you suggest that MLH should read the help files
> before posting in ignorance of things he should be able to find out
> for himself.
What would be the point? You're always right. Right?
Rick Brandt
[snip]
> This is a problem that has been endemic in this thread -- a failure
> to recognize when VBA functions are coercing data types. Lyle made
> this mistake and now you're making it. The fact that + returns a
> number doesn't mean that the Nz() is returning a number.
I wasn't clear in typing what I meant. I didn't mean that using plus caused
Nz() to return a number, only that the + was required (in a query) if the
user WANTS a number as the final output.
rkc
> 1. In VBA code, Nz() returns the data type of the input variable or
> a string.
Or the data type of the optional ValueIfNull parameter.
Larry Linson
> You see what I mean, Larry.
> I told you.
My life's too short to waste any more of it in argument with David or Lyle.
I've wasted too much time that way in the past, and am happy to be on better
terms, now.
Both of them delved into this deeper than I did, as is often the case.
Larry Linson
Arno R
"rkc" <r...@rochester.yabba.dabba.do.rr.bomb> schreef in bericht news:1ww8f.99109$K91....@twister.nyroc.rr.com...
>> Perhaps you could contribute more to the thread if you'd read it
>> first, just as you suggest that MLH should read the help files
>> before posting in ignorance of things he should be able to find out
>> for himself.
>
> What would be the point? You're always right. Right?
He is not always right. He is blind also.
<quote from DWF>
> I don't care about Steve's solicitations, because I've never even seen one.
</quote from DWF>
Aro R
Larry Linson
> He is not always right. He is blind also.
<quote from DWF>
> I don't care about Steve's solicitations,
> because I've never even seen one.
</quote from DWF>
That only indicates that David, some time ago, came to the conclusion that
he was unlikely to benefit from reading PCD's posts and doesn't do so any
more.
Some others of us ought, perhaps, to be equally selective and not spend so
much time trying to "save the newsgroups for democracy".
Larry
Trevor Best
Anyone who has the time could check if it's Nz or Jet expression causing
the problem by writing their own Nz that returns a specific type and
testing it with that. I can't look at the db in question as I don't have
A97 and 2K2 tells me I don't have permission to enable or convert the
database.
lylefair
into a new db. Yes, I know I'm breaking my resolve not to post again to
this topic but it's just a matter of fact that I am noting.
Arno R
"Larry Linson" <bou...@localhost.not> schreef in bericht news:imD8f.1034$zT6.589@trnddc06...
> "Arno R" wrote
>
> > He is not always right. He is blind also.
>
> <quote from DWF>
> > I don't care about Steve's solicitations,
> > because I've never even seen one.
> </quote from DWF>
>
> That only indicates that David, some time ago, came to the conclusion that
> he was unlikely to benefit from reading PCD's posts and doesn't do so any
> more.
In which case he should *not* interfere in a thread that is about Steve's advertising.
He 'jumped in' in the thread "The Rules of Conduct..." and even plonked me!
Yet now he is blaming rkc for not reading *this* whole thread from the beginning ...
That just does not make sense to me.
But I will try to concentrate on the advertising, and ignore David's 'contribution' from now on.
Just had to get this 'off my chest' ...
Arno R
David W. Fenton
> David W. Fenton wrote:
> [snip]
>> This is a problem that has been endemic in this thread -- a
>> failure to recognize when VBA functions are coercing data types.
>> Lyle made this mistake and now you're making it. The fact that +
>> returns a number doesn't mean that the Nz() is returning a
>> number.
>
> I wasn't clear in typing what I meant. I didn't mean that using
> plus caused Nz() to return a number, only that the + was required
> (in a query) if the user WANTS a number as the final output.
Well, it's not the only method. You could implicitly coerce the
string to a number with Val(). Whether or not implicit type coercion
using + is more efficient than a call to a VBA function is a
question I can't answer. It feels right that the + would be faster,
but often VBA is counterintuitively efficient in ways that are
surpising.
David W. Fenton
news:imD8f.1034$zT6.589@trnddc06:
> "Arno R" wrote
>
> > He is not always right. He is blind also.
>
> <quote from DWF>
> > I don't care about Steve's solicitations,
> > because I've never even seen one.
> </quote from DWF>
>
> That only indicates that David, some time ago, came to the
> conclusion that he was unlikely to benefit from reading PCD's
> posts and doesn't do so any more.
Actually, no. He's not in my killfile. He's not in my select file,
either. And he doesn't seem to post in threads that are of any
interest to me, so I just don't see his posts.
My news reader also minimizes signatures by allowing me to set a
different font and color for them, and I have them grayed out and
smaller, so sigs in general don't get in my way when reading.
> Some others of us ought, perhaps, to be equally selective and not
> spend so much time trying to "save the newsgroups for democracy".
Whether or not his solicitations bother you is up to you, as the
reader.
David W. Fenton
news:43635674$0$23279$db0f...@news.zen.co.uk:
The A2K help file says explicitly that in queries, Nz() always
returns a string.
Nowhere in any of the help files does it say that Nz() returns a
variant in any context.
Trevor Best
> The A2K help file says explicitly that in queries, Nz() always
> returns a string.
It does indeed, but neither the 97 or 2002 help files say this, so 2000
is on its own in stating this.
> Nowhere in any of the help files does it say that Nz() returns a
> variant in any context.
There's probably confusion in that there's probably more than 1 Nz
function in Access, like there is at least 2 MsgBox functions, one in
Access, one in VBA and work quite differently.
However, the first lines in the 2K2 help file reads:
<---
Nz Function
See Also Applies To Example Specifics
You can use the Nz function to return zero, a zero-length string (" "),
or another specified value when a Variant is Null. Variant.
--->
That "Variant" on its own, in its own sentance sings out to me as being
the return type of the function. But then why they put a space in their
zero-length-string is beyond me :-) Did the help file writers actually
know what they were writing about?
David W. Fenton
> David W. Fenton wrote:
>> The A2K help file says explicitly that in queries, Nz() always
>> returns a string.
>
> It does indeed, but neither the 97 or 2002 help files say this, so
> 2000 is on its own in stating this.
I believe that somebody posted an MSDN article that said the same
thing as the A2K help file.
And, of course, it's actually the way Nz() works in queries -- it
always returns strings.
>> Nowhere in any of the help files does it say that Nz() returns a
>> variant in any context.
>
> There's probably confusion in that there's probably more than 1 Nz
> function in Access, like there is at least 2 MsgBox functions, one
> in Access, one in VBA and work quite differently.
>
> However, the first lines in the 2K2 help file reads:
>
><---
> Nz Function
> See Also Applies To Example Specifics
> You can use the Nz function to return zero, a zero-length string
> (" "), or another specified value when a Variant is Null. Variant.
> --->
>
> That "Variant" on its own, in its own sentance sings out to me as
> being the return type of the function. But then why they put a
> space in their zero-length-string is beyond me :-) Did the help
> file writers actually know what they were writing about?
I read the help files very carefully for any indication that Nz()
was returning a variant. The files never use the term "variant" to
refer to anything but the first *input* paramater.
Granted, any function that is returning different data types in
different circumstances must be behaving like a variant. But I think
it's curious that there is no use of that term to describe the
output in the help files. Surely there must be some reason for that,
as it would seem to me to make a great deal of sense to describe it
so if it actually behaved like a variant.
Variants have no actually meaning in the output of a query, since a
query has only results. And that's the context in which we were
discussing Nz(), so I don't really think there's much point in
dragging in information that applies only to code contexts. That
seems to me to be one of the chief problems with the discussion,
that some were talking about Nz() in code when the original question
was about Nz() in a query.