Results 1 to 7 of 7
  1. #1
    Join Date
    May 2004
    Posts
    3

    Unhappy Unanswered: Build Expression In Query - #Error

    Gurus...

    I have built a query to calculate a field called Net Value.
    In this Net Value field, I want to fit in this formula in the Expression Builder.

    Net Value: sum(iif([Equity]![Net_Value]="",0,[Equity]![Net_Value]))
    The formula that I want is
    If the Sum of Net_Value from Equity sheet from Microsoft Excel is blank,
    return "0", else
    Sum of Net_Value from Equity sheet from Microsoft Excel

    However, the system returned #Error.

    I need to get this query up and running for the reports soon. The management
    is banging on the door now..the ironic thing is I am not a bloody I.T. at all !!!

    Please help!! I am stucked!

    Thanks in advance..

  2. #2
    Join Date
    May 2004
    Posts
    3
    I attached a slice of the Db for your perusal..
    Attached Files Attached Files

  3. #3
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,482
    Provided Answers: 11
    Quote Originally Posted by tianseng
    Gurus...

    I have built a query to calculate a field called Net Value.
    In this Net Value field, I want to fit in this formula in the Expression Builder.

    Net Value: sum(iif([Equity]![Net_Value]="",0,[Equity]![Net_Value]))
    The formula that I want is
    If the Sum of Net_Value from Equity sheet from Microsoft Excel is blank,
    return "0", else
    Sum of Net_Value from Equity sheet from Microsoft Excel

    However, the system returned #Error.

    I need to get this query up and running for the reports soon. The management
    is banging on the door now..the ironic thing is I am not a bloody I.T. at all !!!

    Please help!! I am stucked!

    Thanks in advance..
    the error is you are trying to get a net_value="" but in the table its a number

    so change the
    Net Value: sum(iif([Equity]![Net_Value]="",0,[Equity]![Net_Value]))
    to
    Net Value: sum(iif([Equity]![Net_Value]=0,0,[Equity]![Net_Value]))
    hope this help

    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.
    DONT WORRY ABOUT THOSE WHO TALK BEHIND YOUR BACK
    THEY'RE BEHIND YOU FOR A REASON

  4. #4
    Join Date
    May 2004
    Posts
    3

    Angry :)

    Hi myle..

    Thanks for the prompt reply..

    I managed to get the error off the screen which is a big relief, but I am wondering if u can try to fit the formula that u have given me into the Query1.
    It will display a blank for each fields when I set criteria for the Settlement_Date field as #5/01/2004#. I know there shouldnt be any record but I really need the system to display 0 instead of Null in the Net Value field.

    I need the fields to show me :-

    Client_Code Name P/S Net Value
    OSKAM John P 0

    I need it to display it this way because I will use this query to build a report to work as a subreport in my Master report.
    In the Master report, I will total all relevant fields together to give me a monthly reporting figure.
    Hence, if this field(Net Value) is blank in this query, it will also be blank in the report. Which also means, I cant add an empty field. The monthly figure will return me #Error..

    I have tried many times already to replace null with 0, but to no avail...Please help me sir...
    Last edited by tianseng; 05-19-04 at 01:12. Reason: Incomplete Explanation

  5. #5
    Join Date
    Apr 2004
    Location
    Sydney Australia
    Posts
    369
    Net Value: sum(iif([Equity]![Net_Value]=0,0,[Equity]![Net_Value]))

    To

    Net Value: IIf(sum(iif([Equity]![Net_Value]=0,0,[Equity]![Net_Value])) Is Null,0,[Equit]![Net_Value])

    If that won't work then create a new field for your report

    NewField:IIf([Net Value] Is Null,0,[Net Value])

    Mike

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi
    A lot of the above expressions are testing for 0s and then changing them to 0s as far as I can tell.

    It looks to me like an nz is all that is needed:

    Net Value: Sum(nz([Equity]![Net_Value]))

    This is changing all null values to 0 before adding the lot up. Hope this is what you are after.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi

    Sorry - although my last post was correct, I missed the complexity of your problem. You want a record where none exists.... Hmm.

    I started writing a complex expression to try to accomodate this and then realised that really, what you want is the customer details to appear irrespective of whether or not they have a equity record for your criteria. Perhaps a Customer report with a payment subreport would be simpler?
    Last edited by pootle flump; 05-19-04 at 05:29. Reason: ooh, you fibber poots
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

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