Zero in Access Cross-tab Query

Posted on 2009-02-20
Last Modified: 2013-11-29
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?


Question by:IEHP1
    LVL 25

    Expert Comment

    You can use the the following  function -

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

    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!

    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.
    LVL 74

    Expert Comment

    by:Jeffrey Coachman
    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." 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.


    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!
    LVL 74

    Expert Comment

    by:Jeffrey Coachman

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Free Trending Threat Insights Every Day

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    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…

    760 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    7 Experts available now in Live!

    Get 1:1 Help Now