Simple way to give all Null values 0 in calculated fields..? (1 Viewer)

I have a query that performs some calculations, these calculations feed into another query and produce a final value. If there are no null values everything works perfectly. But when there are null values, I get errors.

What I have tried:
1) I tried to implement the Nz function for each field of the formula that is not calculated but from user input.

2) I tried to implement the Nz function for just the field that calculated the last result before feeding into the next query, but it didn't show the 0 value I gave.

3) I tried to use UPDATE TABLE SET in SQL, but I don't think I was using it right. I tried to use it for all records but always got an error when it ran.

The first two above result in showing no values at all, even if there are some. The third didn't work. Does anyone have an idea as to how to easily update null values in the query to 0? All I could think of was to somehow use Criteria or SQL, but I'm open to other suggestions as well.

Any help is much appreciated!

it is simply nz(myfield,0)

things like nz(myfield1+myfield2,0) will not work - you have to nz each field separately

If you have calculated values, these should also be nz'd if there is a possibility of a null being created

e.g.

SELECT Val1, Val2, Val1+Val2 as total from sometable

needs to be

SELECT Val1 Va2, nz(Val1)+nz(Val2) as total from sometable

As an a addendum to CJ_London's reply you can find out more about the Nz() function here.

Actually, I think that site is in error.

Quote:
“If this parameter is omitted and the variant is a null value, the Nz function will return a zero or a zero-length string.”

I am inclined to think that if a Function is passed a Null, and no method of evaluation is supplied, then the Function can only return one data type. With no method of evaluation supplied then there is no method to decide which data type should be returned.

This seems to be born out in tests. When no method of evaluation is supplied the Nz function returns one and one only data type. That data type is Empty.


So, without a method of evaluation supplied, the Nz function forces the Null to Empty. Without a method of evaluation supplied, the Nz function does not need to decide on what data type to return, it always returns Empty.

The return data type of Empty is then type cast depending on usage. If the usage requires a number then the number is initialised to zero. If the usage requires a string then the string is initialised to a zero-length string.

Chris.

To be honest Chris I'm have a hard time finding any fundamental difference in the truth of;

ChrisO said:

...

The return data type of Empty is then type cast depending on usage. If the usage requires a number then the number is initialised to zero. If the usage requires a string then the string is initialised to a zero-length string.

Chris.

Click to expand...

and

value_if_null is optional. It is the value to use when the variant is a null value. If this parameter is omitted and the variant is a null value, the Nz function will return a zero or a zero-length string.

Click to expand...

At the end of the day if you are looking for a string you will get a ZLS, but if your looking for an integer you get zero. Whilst your explanation may provide a fuller description of what is going on behind the scenes, I do not believe that it invalidates the site I linked to.

I guess you could say that the Nz() function returns a quantum result, that is both zero and ZLS, until such time as you choose to examine it

John.

I didn’t say it invalidates the site you linked to I said I think it is in error in the statement I quoted. I think that is a big difference because if one statement invalidated a site then there would be very few valid sites, if any.

I think it helps to try and prevent illogical situations, if that is what it is.

A person could read that statement and ask a perfectly valid question:-

If the Nz function can return one of two values with no means to make the choice then how does it do it?
The answer is that, with no means to make the choice, the Nz function only returns one value and that value is Empty.

That then leads on to the next question:-

How does the value of Empty get changed to a zero or zero-length string?
The answer to that question is that Empty can be type cast to either a number or a string depending on usage.

That then leads on to a third question:-

But why is the number zero and the string a zero-length string?
The answer is that they are the initialisation value of those two variables.


It also avoids the question:-

Why does MsgBox VarType(Nz(X)) return 0 when the return data type could be a number or a string?
The answer is that Nz(X) is Empty and the variable type of Empty is 0.

So the sites statement:-
“If this parameter is omitted and the variant is a null value, the Nz function will return a zero or a zero-length string.”
While the end result may be as above, that statement is incorrect and could lead to someone having to make up some other reason for the observed behaviour.

For example: someone might think it is due to some quantum observation thingymajig.

Chris.

ChrisO said:

...

For example: someone might think it is due to some quantum observation thingymajig.

Chris.

Click to expand...

In effect it does, as the type of value that is expected determines the outcome;

No it doesn’t, and that is what I’m trying to get away from.

X holds Empty.

On assigning X to S, the Empty is type cast to String because S is declared as String.
On assigning X to I, the Empty is type cast to Integer because I is declared as Integer.

The Nz function returned one and one only value which was Empty.
That Empty was assigned to X.
X was then assigned to S as String.
X was then assigned to I as Integer.

The type casting happens at assignment time when the type of the receiving variable is known.

The technical term in VBA is Let-coercion.

http://msdn.microsoft.com/en-us/library/ee177491.aspx

Chris.

In effect proving that the method of observation/interrogation determines the result, and thus we must be dealing with quantum value

Chris, I see nothing you have said here, that invalidates or proves the wrongness or erroneous nature of the statement that you took issue with vis a vie;

value_if_null is optional. It is the value to use when the variant is a null value. If this parameter is omitted and the variant is a null value, the Nz function will return a zero or a zero-length string.

Click to expand...

The long and the short of it is that if value_if_null is omitted the value that is returned will be directly determined by the type of value that is be requested

Most users just won't get (or even care about) the esoteric nature of your argument.

Esoteric can be a strange word.

>>The long and the short of it is that if value_if_null is omitted the value that is returned will be directly determined by the type of value that is be requested.<<

As I have been saying, that is incorrect. The value returned is Empty.
MsgBox VarType(Nz(x))
Produces 0 which is Empty.

The value returned by the Nz function, without a method to coerce the return value, is Empty.
Under that circumstance, the coercion happens at assignment time not on return.
If the return value is not assigned then the value remains Empty.
The above code proves it.

That is not esoteric to me; it’s a fact, plain and simple. It is also a fact to Microsoft. It is also a fact in VBA. It is not esoteric, it is just a fact.

If it appears to be esoteric to some then they had better get over it. If they don’t understand it then they will revert to Schrodinger's cat, or fairies at the bottom of the garden, or religion, or …

It is none of those, it is simple, it is a fact. It does not need dumbing down to the lowest common denominator. It is an insult to the reader to try and dismiss a fact as esoteric.

Let the reader decide what may be fact. It may be esoteric but it still can be fact.

Chris.

What made me chuckle was that you spend two posts arguing that nz returns empty and yet say

Produces 0 which is Empty.

Click to expand...

I'm in John's camp on this , when do you not assign the variable before using the Nz , therefore we get returned what we expect.

Brian

Brian.

>>What made me chuckle was that you spend two posts arguing that nz returns empty and yet say<<
>>Produces 0 which is Empty<<

In the line of code above “Produces 0 which is Empty”:-
MsgBox VarType(Nz(x))
The MsgBox is reporting the return value of the VarType function.
The VarType function is reporting the return type of the Nz function.
The VarType function is reporting vbEmpty which is 0.

The return of the Nz(x) function is not assigned to a variable and hence no let-coercion takes place.

The VarType function then tests for the subtype of the variant returned and determines that it is type 0 which is Empty.

It is only when that Empty is assigned to a variable of type numeric or type string that any let-coercion takes place.

In all cases the numeric type will be initialised to zero.
Numbers are set to zero.
Booleans are set to False which is zero.
Dates are set to 12/30/1899 which is zero.

All strings are set to spaces.
A zero length string is set to zero spaces.
A non-zero length string is set to the non-zero number of spaces.

All of that can be learned from the documentation and testing.

------------

People should jump at the chance to refine the knowledge they have.
Why is it so difficult to shake people away from their current beliefs?


Chris.

Chris I trust you will be taking this egregious error up with the Admins over at techonthenet.com

John.

I think that is just a remark which indicates you have no intension of trying to understand what is going on technically.

It is not a big thing except if someone wants to refine their knowledge on a subject. Some people, for whatever reason, don’t want to refine their knowledge and would prefer to carry on as they are.

Well, that’s their choice but when they try to step in and prevent others from learning then it is my choice to try and set it straight.

And make no mistake about it, that is what you are doing. You do not like being corrected and so you put up a diversion away from the facts. If you don’t want to learn then fine, don’t, but try not to impede the learning of others in the process.

If you have something technical to say, and prove, on the subject then by all means say it. If not then your choice should be obvious.

Chris.

And by the way, when Brian agrees with John and John thanks Brian then they are playing the personal game.

I think they are both wrong:-


When either John or Brian can explain that error, in detail, then I will start listening to them.
(Hint, Y is in fact a numerical value.)

And that is technical, not personal.

Chris.

ChrisO said:

John.

I think that is just a remark which indicates you have no intension of trying to understand what is going on technically.

...

Click to expand...

How very prescient of you

ChrisO said:

...

It is not a big thing ...

...

Click to expand...

It was big enough for you to hijack this thread, on the basis of a site I linked to. I suspect that, that reveals a great deal about the motives behind your initial posting in this thread.

John.

You are continuing to deflect away form the technical aspects of this thread.

You are quoting:-
>>It is not a big thing ...<<
when in fact I said:-
>>It is not a big thing except if someone wants to refine their knowledge on a subject.<<

You then use that miss-quote to go on and say:-
>>It was big enough for you to hijack this thread, on the basis of a site I linked to.<<

It is a very large jump to go from a miss-quote to a hijack.
It may suit your purpose to make that jump but I refuse to accept that criticism.
Please do not miss-quote me again.

This is how I see it...
You are trying to deflect attention away from your lack of technical knowledge in VBA.
Here is the simple proof of why I say that:-
1. You have many downloads available in the Sample Databases forum.
a. Thousands of people have downloaded those samples.
b. As far as I have checked, you do not use Option Explicit.
c. That is novice stuff.

2. You refuse to address the technical aspects.
a. You originally deflect to quantum result, post #5.
b. You then deflect to incorrect code, post #7.
c. You then go back to the deflection of quantum value, post #9
d. You then deflect by introducing the word esoteric, post #10.
e. In post #12, Brian gives you a pat on the back which you jump to.
f. You then call it a egregious error and ask me to take it up with techonthenet.com, post #14. (Two deflections in the same post.)
g. You then deflect by saying “How very prescient of you”, post #17. You also introduce the word hijack in the same post. If it was a hijack then both you and Brian also took part in that same hijack.

From g.
If you really want an answer to how prescient I can be with VBA then the answer is I can see crap coming over the horizon before the Sun rises. If you really meant persistent then you really don’t want to find out.

You do not know VBA all that well and that is obvious. You spend your time deflecting people away from that conclusion. You will not answer the question I asked in post #16. You may try but you will not answer it well.

If you want to know something then just ask, we will try to help if we can. But don’t pretend to know or try to sweep your ignorance under the carpet.

Ignorance is a fact of life; we all have it and in some subjects to a more or less degree.
The first thing to accept is that we are ignorant because if we don’t accept that fact we can’t learn.

It’s technical, not personal, because it applies to all of us.

Chris.

Once again you have gone above and beyond the call.

Thank you Chris.

With my assistance (and to my shame) you have managed to transform a mole hill into an insurmountable mountain.

So long and thanks for all the fish.

John

For the life of me, I cannot understand your challenge to Chris’s technical expertise. His knowledge was gained through years of low level programming which he now applies to Access.

It is this knowledge that has enabled Chris to build a Library of Databases that most would think is impossible to create. You can view these via a link in his signature.

When Chris starts commenting on methods and theories, he does so from a wealth of knowledge.

I would suggest you take the time to test his findings before replying with what you believe to be correct or worse still quoting other’s points of view without first testing

Chris often leads you to find some subtitle but important differences to the currently accepted points of view.