By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,847 Members | 2,296 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,847 IT Pros & Developers. It's quick & easy.

A97 - Experimenting with Nz - getting return values that are strings rather than numbers???

P: n/a
MLH
For example: Nz([LaborCost],0) returns "300" if the value in
[LaborCost] field is 300 (currency data type) and "0" if the value is
zero or null. I get strings in the query output - they are all left
aligned and I cannot add them without first converting them to
values. What might be causing this?
Nov 13 '05 #1
Share this Question
Share on Google+
37 Replies


P: n/a
MLH
I tested this again, making it as simple as I could. The following
SQL describes a query based on a single table and 2 of its fields.
The first is a long integer type the second is a currency type.

SELECT Nz([VehicleJobID],0) AS Case1, Nz([LaborCost],0) AS Case2
FROM tblENF262SpecificInfo;
The output I get in the query dynaset is STRING type data. I figure
I'm nuts so I ran this modification of the above SQL...
SELECT Nz([VehicleJobID],0) AS Case1, Nz([LaborCost],0) AS Case2,
VarType([Case1]) AS Type1, VarType([Case2]) AS Type2
FROM tblENF262SpecificInfo;

And the output below suggests to me that everything is string...
Case1 Case2 Type1 Type2
1 0 8 8
23 0 8 8
24 0 8 8
26 0 8 8
27 0 8 8
28 0 8 8
29 0 8 8
30 0 8 8
31 0 8 8
32 0 8 8
33 0 8 8
34 0 8 8
36 0 8 8
38 0 8 8
42 0 8 8
72 0 8 8
73 0 8 8
74 0 8 8
77 0 8 8
78 0 8 8
83 0 8 8
85 0 8 8
4 0 8 8
5 0 8 8
6 0 8 8
7 0 8 8
8 0 8 8
9 0 8 8
10 0 8 8
11 0 8 8
12 0 8 8
14 0 8 8
15 0 8 8
16 0 8 8
17 0 8 8
19 0 8 8
20 0 8 8
21 0 8 8
76 0 8 8
82 0 8 8
88 0 8 8
89 0 8 8
80 0 8 8
94 0 8 8
95 507 8 8
84 0 8 8
79 0 8 8
90 0 8 8
91 0 8 8
93 0 8 8
97 0 8 8
96 85 8 8
98 0 8 8
99 0 8 8
104 0 8 8
Nov 13 '05 #2

P: n/a
MLH
To further illustrate the point, I used this SQL to extract and
display the actual table field values AND their Nz processed
equivalents...

SELECT Nz([VehicleJobID],0) AS Case1, Nz([LaborCost],0) AS Case2,
tblENF262SpecificInfo.VehicleJobID, tblENF262SpecificInfo.LaborCost
FROM tblENF262SpecificInfo;
Here's the query output...

Case1 Case2 VehicleJobID LaborCost
1 0 1 $0.00
23 0 23
24 0 24
26 0 26
27 0 27
28 0 28
29 0 29
30 0 30
31 0 31
32 0 32
33 0 33
34 0 34
36 0 36
38 0 38
42 0 42
72 0 72
73 0 73
74 0 74
77 0 77
78 0 78
83 0 83
85 0 85
4 0 4
5 0 5
6 0 6
7 0 7
8 0 8
9 0 9
10 0 10
11 0 11
12 0 12
14 0 14
15 0 15
16 0 16
17 0 17
19 0 19
20 0 20
21 0 21
76 0 76
82 0 82
88 0 88
89 0 89
80 0 80
94 0 94
95 507 95 $507.00
84 0 84
79 0 79
90 0 90
91 0 91
93 0 93
97 0 97
96 85 96 $85.00
98 0 98
99 0 99 $0.00
104 0 104 $0.00

The first two columns are both left-aligned
strings. I've gotta be doing something wrong.
Someone please set me straight.
Nov 13 '05 #3

P: n/a

MLH wrote:
For example: Nz([LaborCost],0) returns "300" if the value in
[LaborCost] field is 300 (currency data type) and "0" if the value is
zero or null. I get strings in the query output - they are all left
aligned and I cannot add them without first converting them to
values. What might be causing this?


val(Nz([LaborCost],0))?

I haven't used A97 in a while though.

Nov 13 '05 #4

P: n/a
MLH wrote:
For example: Nz([LaborCost],0) returns "300" if the value in
[LaborCost] field is 300 (currency data type) and "0" if the value is
zero or null. I get strings in the query output - they are all left
aligned and I cannot add them without first converting them to
values. What might be causing this?


Yep. Nz() will generally return a string unless you do something about it.

EX: (to force a number)

=Nz(SomeField, 0) + 0

The addition of zero will convert the string from Nz() back into a numeric
value. If you need a specific numeric type then use the appropriate conversion
function wrapped around the Nz().
--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com
Nov 13 '05 #5

P: n/a
MLH
That's news to me. But then, so is Nz in general. Thx
for the heads up. I'm assuming its OK to wrap it inside
the Val() fn - as opposed to adding zero? I just feel odd
about adding zero to a string. I get a runtime 13 when
I run this in the immediate window: ?0+"harvell"
( type mismatch ) I am old-fashioned. Its a fair call.
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

Yep. Nz() will generally return a string unless you do something about it.

EX: (to force a number)

=Nz(SomeField, 0) + 0

The addition of zero will convert the string from Nz() back into a numeric
value. If you need a specific numeric type then use the appropriate conversion
function wrapped around the Nz().


Nov 13 '05 #6

P: n/a
It's very unlikely that what you are describing is actually happening.
I would like to see it.

1. Can you post the database somewhere so that it can be downloaded and
this peculiarity examined?

2. While posters have given you solutions for dealing with Nulls, in a
simple Access db it may be unnecessary to do so. Reports deal with
nulls quite effectively. Forms deal with nulls quite effectively. What
else is there? Simple Access is almost always the best Access. Most of
the beginner problems we see here are problems of the neophyte who
feels compelled to improve upon the multi-million dollar 15 years in
the making Access way of doing things. I don't try to improve on this
and I have been using Access for almost all those 15 years; as a
result I have a simple advanced interface that always works.

3. My impression is that almost all the multitide of problems you have
posted here result from your straying from the simple path; this
attracts the Access guru wanna-bes, most of whom should not be giving
advice to anyone and the thread degenerates into a collection of
nonsense. As it does, your confusion expands and your bizarre
suggestions increase.

4. Try asking simple questions without assuming half the answer.

5. The notions of wrapping NZ in Val or of adding to 0 to NZ are quite
strange. I have never seen this suggested before. NZ should work for
you just as it's supposed to work.
When you do unnecessary things your application becomes inefficient.
It's much better for you to solve your problem than to patch it.

So compact, zip and post the damn database!

Nov 13 '05 #7

P: n/a
lylefair wrote:
It's very unlikely that what you are describing is actually happening.
I would like to see it. [snip] 5. The notions of wrapping NZ in Val or of adding to 0 to NZ are quite
strange. I have never seen this suggested before. NZ should work for
you just as it's supposed to work.
When you do unnecessary things your application becomes inefficient.
It's much better for you to solve your problem than to patch it.


What is strange? In a query Nz([FieldName], 0) returns a String in the query's
output. I've tried this with every Number type, Currency, and Dates and in
every case what the query returns is a String. It justifies as a String and it
sorts as a String.

I agree that it *should* work as expected, but that is not what happens.
--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

Nov 13 '05 #8

P: n/a
I do not see how you can deduce from this that these are strings. I
tried the following:

Select JobID, nz([Jobs]![JobID],0) , IsNumeric(nz([Jobs]![JobID],0))
From Jobs


and for a row in which JobID is null I get:

null 0 -1 (i.e. true)

So the nz() function does produce a zero and not a string.

Nov 13 '05 #9

P: n/a

Rick Brandt wrote:
What is strange? In a query Nz([FieldName], 0) returns a String in the query's
output. I've tried this with every Number type, Currency, and Dates and in
every case what the query returns is a String. It justifies as a String and it
sorts as a String.


Not here.

Nov 13 '05 #10

P: n/a
Jim Devenish wrote:
I do not see how you can deduce from this that these are strings. I
tried the following:

Select JobID, nz([Jobs]![JobID],0) , IsNumeric(nz([Jobs]![JobID],0))
From Jobs


and for a row in which JobID is null I get:

null 0 -1 (i.e. true)

So the nz() function does produce a zero and not a string.


Perhaps, it would be worthwhile to try to learn why Rick and MLH get
strings, and Lyle and Jim get numbers.
I haven't a clue. Is it the version of Access? Is it something we setup
in Options? Is it the phase of the moon?

Ideas here?
Nov 13 '05 #11

P: n/a

"Jim Devenish" <in***************@foobox.com> schreef in bericht news:11*********************@g14g2000cwa.googlegro ups.com...
I do not see how you can deduce from this that these are strings. I
tried the following:

Select JobID, nz([Jobs]![JobID],0) , IsNumeric(nz([Jobs]![JobID],0))
From Jobs


and for a row in which JobID is null I get:

null 0 -1 (i.e. true)

So the nz() function does produce a zero and not a string.


I also tested this. Numeric here, same ouput as your query Jim.
Both in A97 and A2k.
Even when I change the JobId to text the Nz-output stays Numeric.

BUT in the Help is stated that *the context* defines whether the resultvalue of Nz() will be a string or a number.
So in your query when I add another field like "john" & nz([Jobs]![JobID],0) AS Expr3 this evaluates to "John0"

Arno R
Nov 13 '05 #12

P: n/a
lylefair wrote:
Rick Brandt wrote:
What is strange? In a query Nz([FieldName], 0) returns a String in
the query's output. I've tried this with every Number type,
Currency, and Dates and in every case what the query returns is a
String. It justifies as a String and it sorts as a String.


Not here.


Well, I don't know what makes the difference. I tried this in 97, 2K, 2K2,
and 2K3 in all three file formats and I get the same results in all of them.

--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com
Nov 13 '05 #13

P: n/a

"Lyle Fairfield" <ly***********@aim.com> wrote in message
news:s%******************@read1.cgocable.net...
Jim Devenish wrote:
I do not see how you can deduce from this that these are strings. I
tried the following:

Select JobID, nz([Jobs]![JobID],0) , IsNumeric(nz([Jobs]![JobID],0))
From Jobs


and for a row in which JobID is null I get:

null 0 -1 (i.e. true)

So the nz() function does produce a zero and not a string.


Perhaps, it would be worthwhile to try to learn why Rick and MLH get
strings, and Lyle and Jim get numbers.
I haven't a clue. Is it the version of Access? Is it something we setup
in Options? Is it the phase of the moon?

Ideas here?


Access is extremely adept at doing automatic type conversion:

?IsNumeric("0")
True

This, from A2K
Nov 13 '05 #14

P: n/a
Randy Harris wrote:
"Lyle Fairfield" <ly***********@aim.com> wrote in message
news:s%******************@read1.cgocable.net...
Jim Devenish wrote:
I do not see how you can deduce from this that these are strings. I
tried the following:

Select JobID, nz([Jobs]![JobID],0) , IsNumeric(nz([Jobs]![JobID],0))
From Jobs

and for a row in which JobID is null I get:

null 0 -1 (i.e. true)

So the nz() function does produce a zero and not a string.


Perhaps, it would be worthwhile to try to learn why Rick and MLH get
strings, and Lyle and Jim get numbers.
I haven't a clue. Is it the version of Access? Is it something we
setup in Options? Is it the phase of the moon?

Ideas here?


Access is extremely adept at doing automatic type conversion:

?IsNumeric("0")
True

This, from A2K


Just to clarify here is my example. A table (Table1) with a single
LongInteger field [LngField] populated with values 1 to 10. The following
query...

SELECT Nz([LngField],0) AS NzOutput
FROM Table1
ORDER BY Nz([LngField],0);

....produces this output...

NzOutput
0
1
10
2
3
4
5
6
7
8
9

--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

Nov 13 '05 #15

P: n/a
http://ffdba.com/downloads/TestNZ.dat (created in Ac 2003)
http://ffdba.com/downloads/TestNZ97.dat (above saved in Ac97 format)
[rename "dat" part to mdb]

have sample Table (Table1) and a sample Query (Query1);

They return to me currency, currency, long, currency.

What happens on your machine? (not restricted to Rick, Jim, MLH< of
course).

Nov 13 '05 #16

P: n/a
On 24 Oct 2005 09:21:53 -0700, "lylefair" <ly***********@aim.com> wrote:
http://ffdba.com/downloads/TestNZ.dat (created in Ac 2003)
http://ffdba.com/downloads/TestNZ97.dat (above saved in Ac97 format)
[rename "dat" part to mdb]

have sample Table (Table1) and a sample Query (Query1);

They return to me currency, currency, long, currency.

What happens on your machine? (not restricted to Rick, Jim, MLH< of
course).


The Acc97 version returns currency, currency, long, currency for me.
Nov 13 '05 #17

P: n/a

"lylefair" <ly***********@aim.com> schreef in bericht news:11**********************@g49g2000cwa.googlegr oups.com...
http://ffdba.com/downloads/TestNZ.dat (created in Ac 2003)
http://ffdba.com/downloads/TestNZ97.dat (above saved in Ac97 format)
[rename "dat" part to mdb]

have sample Table (Table1) and a sample Query (Query1);

They return to me currency, currency, long, currency.

What happens on your machine? (not restricted to Rick, Jim, MLH< of
course).


I used the 97.dat
currency, currency, long, currency

Arno R
Nov 13 '05 #18

P: n/a
lylefair wrote:
http://ffdba.com/downloads/TestNZ.dat (created in Ac 2003)
http://ffdba.com/downloads/TestNZ97.dat (above saved in Ac97 format)
[rename "dat" part to mdb]

have sample Table (Table1) and a sample Query (Query1);

They return to me currency, currency, long, currency.

What happens on your machine? (not restricted to Rick, Jim, MLH< of
course).


Yes, if I use TypeName(Nz(FieldName,0)) that is what I get, but if I strip
off TypeName and just look at the output of Nz(FieldName, 0) in the
datasheet they are strings. By that I mean the *appear* as strings (left
justified) and they sort as Strings (10.0 comes before 9.0).

--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com
Nov 13 '05 #19

P: n/a

"Arno R" <ar***********@tiscali.nl> wrote in message
news:43********************@dreader2.news.tiscali. nl...

"lylefair" <ly***********@aim.com> schreef in bericht
news:11**********************@g49g2000cwa.googlegr oups.com...
http://ffdba.com/downloads/TestNZ.dat (created in Ac 2003)
http://ffdba.com/downloads/TestNZ97.dat (above saved in Ac97 format)
[rename "dat" part to mdb]

have sample Table (Table1) and a sample Query (Query1);

They return to me currency, currency, long, currency.

What happens on your machine? (not restricted to Rick, Jim, MLH< of
course). I used the 97.dat
currency, currency, long, currency Arno R

Converted to A2K, got same results:
currency, currency, long, currency

Note also of interest:
?typename(nz(null,0))
Integer

Given what Rick posted earlier, I wonder if this particular coercion might
be a result of TypeName? Perhaps it sequentially attempts - Integer, Long,
String....

Nz is obviously designed so that its output is readily coercible. The docs
say that the default type, if no nullvalue is specified, is a string but it
doesn't state that a 0 nullvalue would necessarily output a numeric type.

Randy
Nov 13 '05 #20

P: n/a
But Rick, EVERYTHING is a STRING, that is, it's a series of one or more
bytes (or it's none at all). These bytes may be in memory, or they may
be stored on a hard drive, but they are all the equivalent of strings.
Depending upon the application and the instructions we give the
applications some will be treated as numbers, some as booleans, some as
objects, etc but they are all just series of bytes, which are in turn
series of bits, enough voltage to indicate on, or not enough voltage to
indicate on. Numbers are ideas, they live in our mminds, our thoughts,
our culture. But they have zero, nada substantive relationship
whatsoever with 2 or 4 bytes; these are numbers only to the extent they
are interpreted as numbers.
What we are really talking about here is what kind of thing does Access
think this Nz returns? MLH reports vartype says "8". I say, "Show me!"
because your Access is not thinking like my Access, nor any Access I
have heard about, or even drempt about.

Nov 13 '05 #21

P: n/a
"lylefair" <ly***********@aim.com> wrote in
news:11**********************@g43g2000cwa.googlegr oups.com:
5. The notions of wrapping NZ in Val or of adding to 0 to NZ are
quite strange. I have never seen this suggested before. NZ should
work for you just as it's supposed to work.
When you do unnecessary things your application becomes
inefficient. It's much better for you to solve your problem than
to patch it.


All your other points were excellent, but this one I don't quite
understand.

If you need proper numeric types in a query and you want zeros
instead of Nulls, you have two choices:

1. Val(Nz([Field])), OR

2. A UDF that is a real NullToZero function and returns a numeric
data type.

Now, in most cases, you *don't* need this kind of thing, except in a
report, where you often want to display 0 in place of the Null
value. IN that case a simple control source takes care of it.

There *are* circumstances where you really do need zeroes:

1. when you need to do aggregate functions and need to count the
Null records.

2. when you need to sort and have both negative and positive values
(Null will sort in a different location than 0).

In both cases, I'd suggest that it was a mistake to store Nulls,
instead of defaulting the field to 0.

So, I think Lyle is perhaps right after all -- most cases where you
end up having to use Val(Nz([Field])) are actually the result of an
erroneous field definition.

The key point is that if in your application you need to repeatedly
present Nulls as being zero, then why the hell aren't you *storing*
the zero instead of the Null?

Of course, I can certainly see a case for keeping the Nulls, because
they definitely mean "haven't yet collected this date", whereas 0
suggests you know that the value is zdro. But if you're constantly
presenting the data as though there are no Nulls, what purpose are
those Nulls serving?

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
Nov 13 '05 #22

P: n/a
"Jim Devenish" <in***************@foobox.com> wrote in
news:11*********************@g14g2000cwa.googlegro ups.com:
I do not see how you can deduce from this that these are strings.
I tried the following:

Select JobID, nz([Jobs]![JobID],0) ,
IsNumeric(nz([Jobs]![JobID],0))
From Jobs


and for a row in which JobID is null I get:

null 0 -1 (i.e. true)

So the nz() function does produce a zero and not a string.


Um, you really ought to read the help on IsNumeric(). It says:

Returns a Boolean value indicating whether an expression can be
evaluated as a number.

It does *not* say:

Returns a Boolean value indicating whether an expression IS a
number.

If you check this:

?Isnumeric("0")

in the Debug Windows, you see that it returns TRUE, even though
you've passed it a string. That's because the string you passed can
be coerced to a numeric data type.

All you are seeing here is that lots of Access functions implicitly
coerce data types. That's why adding 0 works, because it implicitly
coerces the string to a numeric type, as long as the string would
return true from IsNumeric().

It's never a good idea, in my opinion, to return on implicit type
conversion.

My opinion on the subject of null-to-zero is this:

1. if you need to sort or aggregate on the field, don't store Nulls
at all -- store 0 as the default value.

2. if you need to distinguish 0 from Null, then it's silly to hide
that distinction by converint the Nulls to 0 when presenting data to
the user -- either the distinction is meaningful or it is not. And
if it isn't, then Nulls shouldn't be stored at all.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
Nov 13 '05 #23

P: n/a
"lylefair" <ly***********@aim.com> wrote in
news:11**********************@g49g2000cwa.googlegr oups.com:
http://ffdba.com/downloads/TestNZ.dat (created in Ac 2003)
http://ffdba.com/downloads/TestNZ97.dat (above saved in Ac97
format) [rename "dat" part to mdb]

have sample Table (Table1) and a sample Query (Query1);

They return to me currency, currency, long, currency.

What happens on your machine? (not restricted to Rick, Jim, MLH<
of course).


The TypeName() function is obviously doing implicit data type
coercion.

If you pull out the expression being fed to TypeName():

Nz(TestCurrency,0))

you'll see that it returns a string.

Aggregate functions like Sum() will do the implicit coercion, as
well.

It's only sorting that doesn't work.

And any aggregate functions like Count() and Avg() where you may or
may not want to treat Null values as being zero.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
Nov 13 '05 #24

P: n/a
"Rick Brandt" <ri*********@hotmail.com> wrote in
news:Sl***************@newssvr12.news.prodigy.com:
lylefair wrote:
http://ffdba.com/downloads/TestNZ.dat (created in Ac 2003)
http://ffdba.com/downloads/TestNZ97.dat (above saved in Ac97
format) [rename "dat" part to mdb]

have sample Table (Table1) and a sample Query (Query1);

They return to me currency, currency, long, currency.

What happens on your machine? (not restricted to Rick, Jim, MLH<
of course).


Yes, if I use TypeName(Nz(FieldName,0)) that is what I get, but
if I strip off TypeName and just look at the output of
Nz(FieldName, 0) in the datasheet they are strings. By that I
mean the *appear* as strings (left justified) and they sort as
Strings (10.0 comes before 9.0).


TypeName() is storing the input in a variant (read the help file)
and what you're getting out of it is not the actual type of the
input, but the variant subtype. Anyone who has ever worked with
variant subtypes should recognize the behavior here.

In practice, this is a form of implicit type conversion, because
what is being tested is actually not the same as what is being
passed in.

The proof is in sorting the Nz(TestCurrency,0) value by itself. THey
sort as strings, not as numbers. Now, the values provide work OK
because the values are chosen carefully. Add 10.xx and 3.x to the
table and you'll see that the sorting no longer works.

It seems to me, Lyle, that if Nz() were actually not doing something
to the data type, it would return a value of the same datatype as
the input value. Since your Null 0 is coming from a currency field,
why doesn't it come out as currency? What is there about 0 that
makes it a long and not an integer? How does Access know the other
values are currency and not singles or doubles?

Clearly something is getting through (the formatting) and it's a
formatted string that's being typed, not the original value.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
Nov 13 '05 #25

P: n/a
lylefair wrote:
But Rick, EVERYTHING is a STRING, that is, it's a series of one or
more bytes (or it's none at all). These bytes may be in memory, or
they may be stored on a hard drive, but they are all the equivalent
of strings. Depending upon the application and the instructions we
give the applications some will be treated as numbers, some as
booleans, some as objects, etc but they are all just series of bytes,
which are in turn series of bits, enough voltage to indicate on, or
not enough voltage to indicate on. Numbers are ideas, they live in
our mminds, our thoughts, our culture. But they have zero, nada
substantive relationship whatsoever with 2 or 4 bytes; these are
numbers only to the extent they are interpreted as numbers.
What we are really talking about here is what kind of thing does
Access think this Nz returns? MLH reports vartype says "8". I say,
"Show me!" because your Access is not thinking like my Access, nor
any Access I have heard about, or even drempt about.


My point is that if you use Nz() on a numeric or date value and then use
that query in a report and sort on it (or if you need the query itself
sorted) it will not do what you expect if you are expecting a numeric or
chronological sort. I'm just going by the old adage "If it looks like a
duck...".

The output from Nz() clearly *acts* like a string. The fact that there is a
VBA function that disputes this doesn't change the fact that if I treat it
as anything but a String I will get unexpected/incorrect results.

So I will revise my orginal statement of "Nz() returns a String." to "The
output of Nz() will act lik a String with respect to format and sorting."
If that is undesired then you have to explicitly coerce the result to a
different type.

--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com


Nov 13 '05 #26

P: n/a
MLH
On 23 Oct 2005 19:37:24 -0700, "lylefair" <ly***********@aim.com>
wrote:
It's very unlikely that what you are describing is actually happening.
I would like to see it.

***************************************
Wow. You're really not kidding. I read all 5 of your numbered points.
Number 4, in particular. You do infer a lot. Maybe that's a good
thing. And maybe I can learn something here. I'm relatively sure THAT
would be a good thing. Would you mind terribly pointing out the
assumptions stated in the O.P.?
Nov 13 '05 #27

P: n/a
After looking at the various examples, I agree with you.
From a theoretical point of view probably we should restrict Nz to

acting upon strings, as a NULL is never zero, and treating it as such
may result in errors and, of course, its acting like a string when it
acts upon a string will not surprise us.

Nov 13 '05 #28

P: n/a
MLH
On 23 Oct 2005 19:37:24 -0700, "lylefair" <ly***********@aim.com>
wrote:
5. The notions of wrapping NZ in Val or of adding to 0 to NZ are quite
strange. I have never seen this suggested before. NZ should work for
you just as it's supposed to work.


I agree, they are quite strange. I too have never seen them suggested
before. I too think NZ should work. I wish that you knew why it was
not. Here, on my box, that is. I'm sure you would tell me if you knew.
Thread's not dead yet. Maybe someone'll come up with an answer.
Nov 13 '05 #29

P: n/a
Bri
lylefair wrote:
http://ffdba.com/downloads/TestNZ.dat (created in Ac 2003)
http://ffdba.com/downloads/TestNZ97.dat (above saved in Ac97 format)
[rename "dat" part to mdb]

have sample Table (Table1) and a sample Query (Query1);

They return to me currency, currency, long, currency.

What happens on your machine? (not restricted to Rick, Jim, MLH< of
course).


FWIW, I also get currency, currency, long, currency with the AC97
version of this.

--
Bri
Nov 13 '05 #30

P: n/a
MLH
I get currency, currency, long, currency.
Nov 13 '05 #31

P: n/a
"MLH" <CR**@NorthState.net> wrote in message
news:ho********************************@4ax.com...
I get currency, currency, long, currency.


I think we have established that everyone gets the same results from the
TypeName function. That was never the issue (for me at least).

--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

Nov 13 '05 #32

P: n/a
MLH
Same here. Since some doubt has been cast on the
validity of what I stated my findings to be, I did not wish
to appear uncooperative. I stand by what I said.
Nov 13 '05 #33

P: n/a
MLH
On 23 Oct 2005 19:37:24 -0700, "lylefair" <ly***********@aim.com>
wrote:
1. Can you post the database somewhere so that it can be downloaded and
this peculiarity examined?


I don't really have anywhere to post it. If someone has an FTP site to
offer, I'll be glad to put it there. Regards.
Nov 13 '05 #34

P: n/a
MLH wrote:
On 23 Oct 2005 19:37:24 -0700, "lylefair" <ly***********@aim.com>
wrote:
1. Can you post the database somewhere so that it can be downloaded and
this peculiarity examined?


I don't really have anywhere to post it. If someone has an FTP site to
offer, I'll be glad to put it there. Regards.


Send it to me and I'll post it.
Nov 13 '05 #35

P: n/a
MLH
Okey Dokey.
Nov 13 '05 #36

P: n/a
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

Nov 13 '05 #37

P: n/a
MLH
Thank-you
Nov 13 '05 #38

This discussion thread is closed

Replies have been disabled for this discussion.