Only positive values in a field

[aurosunil]

Hi every one

this is very simple but i could not find the solution.

There are two tables: Assets and SoldAssets
I am trying to generate a report using these tables which contains two Calculating field namely Capital Gain & Capital Loss. For a particular record if CapitalGain is +ve then Capital Loss is -ve. What i want is that a particular record should contain only Capital Gain or CapitalLoss and not both. how to do it? Can any help me?
here is the sql statement:

SELECT Departments.Department, [Asset Categories].AssetCategory, Assets.AssetID, Assets.DateAcquired, Assets.AssetDescription, Assets.AcquiredQuantity, Assets.AcquiredPrice, SoldAssets.DateSold, SoldAssets.SoldQuantity, SoldAssets.SoldValue, [SoldAssets]![SoldValue]-[SoldAssets]![SoldQuantity]*[Assets]![AcquiredPrice]/[Assets]![AcquiredQuantity] AS CapitalGain, Departments.DepartmentID, [SoldAssets]![SoldQuantity]*[Assets]![AcquiredPrice]/[Assets]![AcquiredQuantity]-[SoldAssets]![SoldValue] AS CapitalLoss
FROM (Departments INNER JOIN ([Asset Categories] INNER JOIN Assets ON [Asset Categories].AssetCategoryID = Assets.AssetCategoryID) ON Departments.DepartmentID = Assets.DepartmentID) LEFT JOIN SoldAssets ON Assets.AssetID = SoldAssets.AssetID
WHERE (((Assets.DateAcquired) Between [Forms]![Report Range]![Beginning Date Range] And [Forms]![Report Range]![Ending Date Range]));

thanks
sunil

 

[WayneRyan]

sunil,

If you don't have to report "which" field contributed the
gain/loss, then you can do it in one field. The SQL below
return Capital status as either a positive gain, or a
negative loss.

It might also be that you could just sum the two.

SELECT Departments.Department, 
       [Asset Categories].AssetCategory, 
        Assets.AssetID, 
        Assets.DateAcquired, 
        Assets.AssetDescription, 
        Assets.AcquiredQuantity, 
        Assets.AcquiredPrice, 
        SoldAssets.DateSold, 
        SoldAssets.SoldQuantity, 
        SoldAssets.SoldValue, 
        Iif([SoldAssets]![SoldValue]- 
            [SoldAssets]![SoldQuantity]*[Assets]![AcquiredPrice]/[Assets]![AcquiredQuantity] > 0,
            [SoldAssets]![SoldValue]- 
            [SoldAssets]![SoldQuantity]*[Assets]![AcquiredPrice]/[Assets]![AcquiredQuantity],
            [SoldAssets]![SoldQuantity]*[Assets]![AcquiredPrice]/[Assets]![AcquiredQuantity]-[SoldAssets]![SoldValue]*-1) As CapitalStatus,
        Departments.DepartmentID
      INNER JOIN ([Asset Categories] 
      INNER JOIN Assets ON [Asset Categories].AssetCategoryID = Assets.AssetCategoryID) ON 
                           Departments.DepartmentID = Assets.DepartmentID) 
      LEFT JOIN SoldAssets ON Assets.AssetID = SoldAssets.AssetID
WHERE (((Assets.DateAcquired) Between [Forms]![Report Range]![Beginning Date Range] And [Forms]![Report Range]![Ending Date Range]));

Wayne

 

[WayneRyan]

sunil,

I just read the title "Only positive values in a field". Then you
can't tell if it's a gain or loss?!

Wayne

 

[aurosunil]

Wayne,

Report heading will indicate whether it is Capital gain or Loss.
Any way, thank you for the idea i will work on it and let you know.

sunil

 

[aurosunil]

Wayne,

Thanks a lot. it did work.

sunil