Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Is null in a query

Discussion in 'Information Technology' started by msmuzila@gmail.com, Jul 28, 2006.

Tags:
  1. msmuzila@gmail.com

    msmuzila@gmail.com
    Expand Collapse
    Guest

    I have a sum query that sums up sales for each month, if the there
    where no sales, i get a blank record for the sum. Is there a way to
    turn the null into a 0 for the query
     
  2. Loading...

    Similar Threads Forum Date
    Sikh News Sikh woman's forced marriage annulled in Britain (New Kerala) Breaking News Oct 22, 2008
    Sikh News Sikh woman’s marriage annulled in UK (Hindustan Times) Breaking News Oct 22, 2008
    Concept Of Sunn-Void,nullity, Essays on Sikhism Jan 16, 2008
    Query about Jhatka Meat by Shooting in Head Sikh Sikhi Sikhism Aug 26, 2011
    Who is a sikh? A non sikh friend's query!! Sikh Sikhi Sikhism Apr 30, 2010

  3. Jeff Boyce

    Jeff Boyce
    Expand Collapse
    Guest

    Take a look at the Nz() function (null-to-zero).

    Regards

    Jeff Boyce
    Microsoft Office/Access MVP

    <msmuzila@gmail.com> wrote in message
    news:1150146274.195361.118870@u72g2000cwu.googlegroups.com...
    >I have a sum query that sums up sales for each month, if the there
    > where no sales, i get a blank record for the sum. Is there a way to
    > turn the null into a 0 for the query
    >
     
  4. Graham Mandeno

    Graham Mandeno
    Expand Collapse
    Guest

    Use the Nz function:

    Nz(Sum([FieldName]), 0)

    --
    Good Luck!

    Graham Mandeno [Access MVP]
    Auckland, New Zealand

    <msmuzila@gmail.com> wrote in message
    news:1150146274.195361.118870@u72g2000cwu.googlegroups.com...
    >I have a sum query that sums up sales for each month, if the there
    > where no sales, i get a blank record for the sum. Is there a way to
    > turn the null into a 0 for the query
    >
     
  5. Douglas J. Steele

    Douglas J. Steele
    Expand Collapse
    Guest

    I think it would be better to use Sum(Nz([FieldName], 0))

    If any of the fields being summed are Null, the entire sum will be Null.
    Nz(Sum([FieldName]), 0) will result in a total, therefore, of 0.

    --
    Doug Steele, Microsoft Access MVP
    http://I.Am/DougSteele
    (no private e-mails, please)


    "Graham Mandeno" <Graham.Mandeno@nomail.please> wrote in message
    news:eiTl0hmjGHA.1260@TK2MSFTNGP05.phx.gbl...
    > Use the Nz function:
    >
    > Nz(Sum([FieldName]), 0)
    >
    > --
    > Good Luck!
    >
    > Graham Mandeno [Access MVP]
    > Auckland, New Zealand
    >
    > <msmuzila@gmail.com> wrote in message
    > news:1150146274.195361.118870@u72g2000cwu.googlegroups.com...
    >>I have a sum query that sums up sales for each month, if the there
    >> where no sales, i get a blank record for the sum. Is there a way to
    >> turn the null into a 0 for the query
    >>

    >
    >
     
  6. Graham Mandeno

    Graham Mandeno
    Expand Collapse
    Guest

    Hi Doug

    >I think it would be better to use Sum(Nz([FieldName], 0))
    >
    > If any of the fields being summed are Null, the entire sum will be Null.
    > Nz(Sum([FieldName]), 0) will result in a total, therefore, of 0.


    I think Sum (along with all the other SQL aggregate functions) ignores Null
    fields. That's why people often run into a problem with Count appearing to
    return an incorrect record count.

    I thought it might be slightly faster to perform the Nz only once.
    --
    Cheers,
    Graham M
     
  7. Douglas J. Steele

    Douglas J. Steele
    Expand Collapse
    Guest

    "Graham Mandeno" <Graham.Mandeno@nomail.please> wrote in message
    news:O0BtJQnjGHA.4512@TK2MSFTNGP02.phx.gbl...
    > Hi Doug
    >
    >>I think it would be better to use Sum(Nz([FieldName], 0))
    >>
    >> If any of the fields being summed are Null, the entire sum will be Null.
    >> Nz(Sum([FieldName]), 0) will result in a total, therefore, of 0.

    >
    > I think Sum (along with all the other SQL aggregate functions) ignores
    > Null fields. That's why people often run into a problem with Count
    > appearing to return an incorrect record count.
    >
    > I thought it might be slightly faster to perform the Nz only once.


    Right you are.

    From the Help file: "The Sum function ignores records that contain Null
    fields."

    Pardon my interference.

    --
    Doug Steele, Microsoft Access MVP
    http://I.Am/DougSteele
    (no private e-mails, please)
     
  8. Graham Mandeno

    Graham Mandeno
    Expand Collapse
    Guest

    > Pardon my interference.

    No offence taken, Doug :)

    --
    Cheers,
    Graham
     

Share This Page