Zero in Access Cross-tab Query
Hi Experts,
I am creating a cross tab query.
I found out that there is empty fields in the table after I ran it.
May I know if there is any way to make the empty fields to be zeros?
Thanks!
I am creating a cross tab query.
I found out that there is empty fields in the table after I ran it.
May I know if there is any way to make the empty fields to be zeros?
Thanks!
ASKER
it is a cross-tab query...
i tried the way you mentioned... but it does not work for cross tab query...
moreover i need to use "iif" instead of "if"
Thanks, reb73!
i tried the way you mentioned... but it does not work for cross tab query...
moreover i need to use "iif" instead of "if"
Thanks, reb73!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Just a note:
If you convert Nulls to zero then you may have incorrect values returned.
The average of:
115, Null, 100, 125=113.333 (Correct)
...the average of:
115,0,100,125=85 (INCORRECT)
Of course if you were just adding the numbers, it would not matter, so you must be very carefull to know where it matters and where it does not.
So you statement that:
"If you don't plan to do anything with the returned value that involves
arithmetic then you can drop the CDbl."
...is inherently flawed because converting Nulls to Zero can actually produces an incorrect result.
;-)
Mathematically, Nulls and Zeros cannot be interchanged
Do a google search of: Null Zero Difference
This is why many Experts only use this to avoid the "Invalid use of null" error.
Then use NZ() to insert an actual value.
Finally NZ() is not used in any other High level database for these reasons.
The preferred method of dealing with these situations is to use IIF()
So actually using NZ() to turn Nulls into zeros is incorrect mathematically.
Converting Nulls to Zeros is usually done for cosmetic reasons, not for mathematical reasons.
;-)
Your feedback on this topic is welcome.
;-)
JeffCoachman
If you convert Nulls to zero then you may have incorrect values returned.
The average of:
115, Null, 100, 125=113.333 (Correct)
...the average of:
115,0,100,125=85 (INCORRECT)
Of course if you were just adding the numbers, it would not matter, so you must be very carefull to know where it matters and where it does not.
So you statement that:
"If you don't plan to do anything with the returned value that involves
arithmetic then you can drop the CDbl."
...is inherently flawed because converting Nulls to Zero can actually produces an incorrect result.
;-)
Mathematically, Nulls and Zeros cannot be interchanged
Do a google search of: Null Zero Difference
This is why many Experts only use this to avoid the "Invalid use of null" error.
Then use NZ() to insert an actual value.
Finally NZ() is not used in any other High level database for these reasons.
The preferred method of dealing with these situations is to use IIF()
So actually using NZ() to turn Nulls into zeros is incorrect mathematically.
Converting Nulls to Zeros is usually done for cosmetic reasons, not for mathematical reasons.
;-)
Your feedback on this topic is welcome.
;-)
JeffCoachman
ASKER
Thank you, Jeff, for the exact information!!!
I will keep this in mind.
At this point, I only need to have a total of the number.
Once again, thank you for your message!
I will keep this in mind.
At this point, I only need to have a total of the number.
Once again, thank you for your message!
;-)
IF(<FieldName> IS NULL, 0, <FieldName>)