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

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

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

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

Wayne,

Thanks a lot. it did work.

sunil