Solved

# Zero in Access Cross-tab Query

Posted on 2009-02-20
1,075 Views
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
Question by:IEHP1

LVL 25

Expert Comment

You can use the the following  function -

IF(<FieldName> IS NULL, 0, <FieldName>)
0

Author Comment

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

Accepted Solution

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

LVL 74

Expert Comment

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

Author Comment

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

LVL 74

Expert Comment

;-)
0

## Featured Post

### Suggested Solutions

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…