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

[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?

[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 tblENF262Specif icInfo;
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 tblENF262Specif icInfo;

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

[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,
tblENF262Specif icInfo.VehicleJ obID, tblENF262Specif icInfo.LaborCos t
FROM tblENF262Specif icInfo;
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.

[Dean]

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.

[Rick Brandt]

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

[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.
xxxxxxxxxxxxxxx xxxxxxxxxxxxxxx xxxxxxxxxxx

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

EX: (to force a number)

=Nz(SomeFiel d, 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().

[lylefair]

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!

[Rick Brandt]

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

[Jim Devenish]

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.

[Lyle Fairfield]

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?