Results 1 to 3 of 3
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    632
    Thanks
    0
    Thanked 0 Times in 0 Posts

    How to handle Null records (A97)

    trying to create a new report for "trips" made via tanker trucks. The report will show cost, revenue, and profit. The cost consists of several components: truck cost, driver cost, and miscellaneous costs. The miscellaneous costs is where I am having trouble.

    The miscellaneous costs are in their own table, related one:many to the main "Trip" table. Some trips have many misc costs, while others have none. I've created a Sum query that calculates the sum of all misc costs for each trip. Currently, there are 502 trips total, but only 152 trips with misc costs. Then I created a "gross profit" query that pulls together all the individual costs -- it shows the 152 trips. I modified the query with an outer join to return all 502 trips. Obviously, the trips without misc costs have a NULL in the Misc column and any column dependent on the Misc costs.

    That's where I run into problems. I tried putting a NZ function into the Misc column (i.e., MiscCost: NZ(SumOfMiscCost,0) ), which appears to work properly if I display the query in datasheet view. It even works properly in the Detail section of the report, but it doesn't work in a GroupFooter section of the report when I try to include it in a Sum function (=Sum(MiscCost) ). Access returns an error message that states the SQL statement contains an error.

    Tried changing the ControlSource for the GroupFooter control from the field in the recordset (=Sum(MiscCost) ) to the control in the Detail section (=Sum(txtMiscCost) ), but Access then interprets it incorrectly, and asks for the value of txtMiscCost.

    I am stuck, and would appreciate any help with summing these costs in the report. Thanks.
    --------------------------------------------------
    Jack MacDonald
    Vancouver, Canada

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: How to handle Null records (A97)

    A Null is the absence of a value. You're actually dealing with the absence of a *record*, and Nz doesn't handle that. Try this: =Sum(Nz(MiscCost,0))
    Charlotte

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    632
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How to handle Null records (A97)

    Charlotte:
    Thanks for the help. The statement you provided returned the same error message: The Microsoft Jet database engine could not execute the SQL statement because it contains a field that has an invalid data type. But it got me thinking about what other function I could add to the controlSource.

    This worked as required: =Sum(CCur([MiscCost]))
    --------------------------------------------------
    Jack MacDonald
    Vancouver, Canada

Posting Permissions

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