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,423
    Provided Answers: 8
    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

    See clear as mud


    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 based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  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
  •