Results 1 to 4 of 4
  1. #1
    Star Lounger
    Join Date
    Mar 2002
    Location
    Edinburgh, Scotland
    Posts
    80
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Help with Iif (Access 97)

    Hi, I have a query which says:

    MV: Sum(IIf([AssetCategoryID]=1,[AssetCost],0))

    What I really want to say is if the Asset Category Id is 1, and the Asset Cost is MORE than 0, return the Sum of the Asset Cost, if the Asset Cost is 0, return "0.00". Can anyone show me how? Many thanks.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Help with Iif (Access 97)

    If AssetCost is 0, it won't contribute to the sum, so there is no need to check for AssetCost being more than zero (assuming it can't be negative). You can set the Format property of the MV column to Fixed, 2 decimals, or to "0.00" (without the quotes). You may need a DSum here instead of a Sum:

    MV: DSum("AssetCost"; "tblWhatever", "AssetCategory = 1")

    where you should replace tblWhatever by the name of the table (or query) that contains AssetCategory and AssetCost.

  3. #3
    Star Lounger
    Join Date
    Mar 2002
    Location
    Edinburgh, Scotland
    Posts
    80
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Help with Iif (Access 97)

    Thanks Hans - have tried this, but unfortunately still returns an empty query with no "0.00" value. Think this may be because of other selection criteria in other fields. Will persevere.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Help with Iif (Access 97)

    Perhaps you have records with null values for AssetCost instead of 0. You can use the Nz function to replace null values by another value:

    MV: Nz(DSum("AssetCost", "tblWhatever", "AssetCategory = 1"), "0.00")

    However, if there are no records at all in your query, you'll get nothing - that's the way queries work, I'm afraid.

Posting Permissions

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