Results 1 to 12 of 12
  1. #1
    Join Date
    Feb 2002
    Location
    Silver Spring, Maryland
    Posts
    74

    Unanswered: =IIf(IsNull(Sum([DollarAmount])),0, NZ(Sum([DollarAmount])))????

    Using Access 2000,
    I am creating a report that get info from a Query (QryOneContractFundDocs
    ) whose statement is as shown below:

    SELECT FundingDocs.FundingDocTitle, FundingDocs.DollarAmount, FundingDocs.ContractNumber
    FROM FundingDocs
    WHERE (((FundingDocs.ContractNumber)='NMIPR029209245 DD448-2'));

    That report has “dollar amount” as one the columns and get info from

    [FundingDocs].[DollarAmount]

    as well as a summary which is as shown below and is put in the control’s control source;

    =IIf(IsNull(Sum([DollarAmount])),0, NZ(Sum([DollarAmount])))


    When there is no entty at all (when the query is empty, I get

    “#Error”

    in the summary control.

    Can some help me find a way of getting a zero instead.

    Wango

  2. #2
    Join Date
    Nov 2002
    Location
    San Francisco
    Posts
    251
    one error is in
    =IIf(IsNull(Sum([DollarAmount])),0, NZ(Sum([DollarAmount])))

    in NZ function ...

    =IIf(IsNull(Sum([DollarAmount])),0, NZ(Sum([DollarAmount]),0))


    is is weird structure anyway.....


    what about =Sum(NZ([DollarAmount],0)

  3. #3
    Join Date
    Feb 2002
    Location
    Silver Spring, Maryland
    Posts
    74
    Thanks for you r uick response. I have tried the last two and I still get the same error. Aaaargh.......

  4. #4
    Join Date
    Nov 2002
    Location
    San Francisco
    Posts
    251
    1. make sure that the field name to which you associate this function is different than the field you are calling...

    e.g. the field in Group Footer is called DollarAmountSum and calls DollarAmount

    Access is smart and sums even Null Values.... but keep your NZ in .... or stick NZ into source query.....


    2. did you stick the field to footer or Group Footer/Report Footer - it should be in Group or report footer ....?


    jiri

  5. #5
    Join Date
    Feb 2002
    Location
    Silver Spring, Maryland
    Posts
    74
    Jiri,


    1. make sure that the field name to which you associate this function is different than the field you are calling...

    THE NAMES ARE VERY DIFFERENET.

    e.g. the field in Group Footer is called DollarAmountSum and calls DollarAmount


    2. did you stick the field to footer or Group Footer/Report Footer - it should be in Group or report footer ....?

    IT IS IN THE REPORT FOOTER.

  6. #6
    Join Date
    Nov 2002
    Location
    San Francisco
    Posts
    251
    just to make sure..... is your DollarAmount a number?

  7. #7
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    a possible explanation (i did not check it):

    ?? i guess Sum(null) = error
    and your isnull is looking at Sum([DollarAmount])
    but Sum([DollarAmount]) is never null, it is either error or a value.


    does this work?
    iif(isnull(DSum("[FundingDocs].[DollarAmount]","QryOneContractFundDocs")),0,DSum("[FundingDocs].[DollarAmount]","QryOneContractFundDocs"))

    yes it runs the query two more times, but speed might not be essential in a report.

    izy
    Last edited by izyrider; 01-01-03 at 07:25.

  8. #8
    Join Date
    Feb 2002
    Location
    Silver Spring, Maryland
    Posts
    74
    Wow!!!

    =iif(isnull(DSum("[FundingDocs].[DollarAmount]","QryOneContractFundDocs")),0,DSum("[FundingDocs].[DollarAmount]","QryOneContractFundDocs"))

    seems to be working!

    I thank you very much for the solution. I am going to test it in other locations?

    Happt new yaer!!!

  9. #9
    Join Date
    Nov 2002
    Location
    San Francisco
    Posts
    251
    if DSUM works, then SUM has to work too!

    DSUM is a domain function and you should use SUM in reports as much as you can (mostly because of speed).




    jiri
    Last edited by playernovis; 01-03-03 at 01:38.

  10. #10
    Join Date
    Oct 2003
    Posts
    2

    Re: =IIf(IsNull(Sum([DollarAmount])),0, NZ(Sum([DollarAmount])))????

    I think you should use "on no data" event and don't display report when datasource is empty.

    DungTran



    Originally posted by Wango
    Using Access 2000,
    I am creating a report that get info from a Query (QryOneContractFundDocs
    ) whose statement is as shown below:

    SELECT FundingDocs.FundingDocTitle, FundingDocs.DollarAmount, FundingDocs.ContractNumber
    FROM FundingDocs
    WHERE (((FundingDocs.ContractNumber)='NMIPR029209245 DD448-2'));

    That report has “dollar amount” as one the columns and get info from

    [FundingDocs].[DollarAmount]

    as well as a summary which is as shown below and is put in the control’s control source;

    =IIf(IsNull(Sum([DollarAmount])),0, NZ(Sum([DollarAmount])))


    When there is no entty at all (when the query is empty, I get

    “#Error”

    in the summary control.

    Can some help me find a way of getting a zero instead.

    Wango

  11. #11
    Join Date
    Oct 2003
    Posts
    2

    Re: =IIf(IsNull(Sum([DollarAmount])),0, NZ(Sum([DollarAmount])))????

    I think you should use "on no data" event and don't display report when datasource is empty.

    DungTran



    Originally posted by Wango
    Using Access 2000,
    I am creating a report that get info from a Query (QryOneContractFundDocs
    ) whose statement is as shown below:

    SELECT FundingDocs.FundingDocTitle, FundingDocs.DollarAmount, FundingDocs.ContractNumber
    FROM FundingDocs
    WHERE (((FundingDocs.ContractNumber)='NMIPR029209245 DD448-2'));

    That report has “dollar amount” as one the columns and get info from

    [FundingDocs].[DollarAmount]

    as well as a summary which is as shown below and is put in the control’s control source;

    =IIf(IsNull(Sum([DollarAmount])),0, NZ(Sum([DollarAmount])))


    When there is no entty at all (when the query is empty, I get

    “#Error”

    in the summary control.

    Can some help me find a way of getting a zero instead.

    Wango

  12. #12
    Join Date
    Nov 2003
    Location
    Netherlands
    Posts
    85
    Originally posted by izyrider
    a possible explanation (i did not check it):

    ?? i guess Sum(null) = error
    and your isnull is looking at Sum([DollarAmount])
    but Sum([DollarAmount]) is never null, it is either error or a value.


    does this work?
    iif(isnull(DSum("[FundingDocs].[DollarAmount]","QryOneContractFundDocs")),0,DSum("[FundingDocs].[DollarAmount]","QryOneContractFundDocs"))

    yes it runs the query two more times, but speed might not be essential in a report.

    izy
    I think that your conclusion never null, is either error or a value is correct. But I tried your solutions with Dsum but still #error if there are no records.
    If I try to count records in this report and there are no records I expect to get the return value 0.
    Do you have an other solutions?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •