[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1093
  • Last Modified:

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!

0
IEHP1
Asked:
IEHP1
  • 3
  • 2
1 Solution
 
reb73Commented:
You can use the the following  function -

IF(<FieldName> IS NULL, 0, <FieldName>)
0
 
IEHP1Author Commented:
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!
0
 
IEHP1Author Commented:
i found the solution...

(1) Go to SQL View.
(2) Change the first line to include the NZ function.
          eg. From -> TRANSFORM Count(Status) as TotalStatus
                To -> TRANSFORM CDbl(NZ(Count(Status),0)) as TotalStatus

The CDbl is there to handle the fact that Access queries will usually
return the result of NZ as a string - even when they should return a
number or a date value.  So CDbl will force the value back to a number.
If you don't plan to do anything with the returned value that involves
arithmetic then you can drop the CDbl.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Jeffrey CoachmanCommented:
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
0
 
IEHP1Author Commented:
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!
0
 
Jeffrey CoachmanCommented:
;-)
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now