Results 1 to 8 of 8
  1. #1
    Join Date
    Mar 2009
    Posts
    9

    Unanswered: Calculated Field in Report

    I need help with a calculated field in a report that sometimes populate in the report and sometimes it doesn't. I created a query and based the calculated field in my report on it. The calculated field is very simple: =([SumOfVASLabor$s]+[SumOfVASTravel$s]+[SumOfVASMaterial]). I double check my source data as well as my query to see if the report should have returned results for my sample sales orders.

  2. #2
    Join Date
    May 2005
    Posts
    1,191
    Welcome to the forums

    Are you checking for Null values?
    Me.Geek = True

  3. #3
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Try:

    =Nz([SumOfVASLabor$s],0)+Nz([SumOfVASTravel$s],0)+Nz([SumOfVASMaterial],0)

    It should calculate ok once you deal with Nulls, which is what NZ is doing.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  4. #4
    Join Date
    May 2005
    Posts
    1,191
    you may want to be careful, you may not want to substitute 0 if just one field is null. Usually I'll do something like

    =Nz(([SumOfVASLabor$s]+[SumOfVASTravel$s]+[SumOfVASMaterial]),0)

    or

    =Nz(([SumOfVASLabor$s]+[SumOfVASTravel$s]+[SumOfVASMaterial]),"NULL")

    depending on how you're using it. That way it'll return a special value if any of the values are null. Just be careful you know what it's doing and if it's ok how to substitute.
    Last edited by nckdryr; 03-24-09 at 00:44.
    Me.Geek = True

  5. #5
    Join Date
    Mar 2009
    Posts
    9

    Thumbs up Calculated Field In Report

    Thank you, that worked when I added the Null to the calculation.

  6. #6
    Join Date
    Mar 2009
    Posts
    9

    Question Calculated Field

    I need help with the following formula that should in many cases give me a negative number but isn't calculating correctly. Here is the formula:
    =(([Equip$]+[VAS$])*0.0528)-(Nz(([SumOfInstallLabor$s]+[SumOfInstallTravel$s]+[SumOfCost]+[SumOfVASLabor$s]+[SumOfVASTravel$s]+[SumOfVASMaterial]),0)).

    sample:
    (340,379 + 38,005)*0.0528 - (5760+ 1908 + 10456 + 5796 + 2997) = -7241

  7. #7
    Join Date
    May 2005
    Posts
    1,191
    First off, are those dollar signs in the name of the fields? Typically I try to only use the 26 upper case, 26 lower case and 10 numbers in the field name with the optional under score (_). Special characters like that tend to cause troubles later on, but it's your call.

    I have a question for you: Is it OK if any of the fields in the Nz statement are null? The way you have it setup now is that if any of those 6 fields are null, it returns a zero (remember how Access handles null values in calculations?). So for example if your first two fields are positive, and any of the last 6 are null, it'll return a positive number ([positive] * [positive] * [positive constant] - 0 = [positive]). That's what I was getting at with:
    Quote Originally Posted by nckdryr
    Just be careful you know what it's doing and if it's ok how to substitute.
    Basically, if a field is null, what is the appropriate response? The answer to that is specific to your database and you need to answer that before you can really lay this problem to bed. Cheers!
    Me.Geek = True

  8. #8
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    [Equip$]
    [VAS$]
    [SumOfInstallLabor$s]
    [SumOfInstallTravel$s]
    [SumOfCost]
    [SumOfVASLabor$s]
    [SumOfVASTravel$s]
    [SumOfVASMaterial]

    Can any of these have nulls in them? If they could, then you will need to use many more instances of NZ(). Your NZ function is going to replace the entire right hands side of that formula with a zero if ANY of the fields give back a null.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

Posting Permissions

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