Hi All,

i need some help with the following,
I am drawing data through access from an SQl datasource. Due to this i am unable to change existing data. I am running a query to create a total field from a qty * Saleprice. However the data stored in sql is (eg) qty = -1 and Saleprice = -$100. This is creating a problem for me as when i return a total my result = $100, where as it should be -$100, I need to know if i can somehow trim/remove the (-) character where it exists, from the Saleprice field, using a new field i have create in the query. My Query data look like so
Field = Qty (From SQL Source)
Field = Saleprice (From SQL Source)
Field = TotalSell:[qty]*[Saleprice] My created Field

Thanks in Advance

Hi darrenb

Try:
TotalSell: [qty]*IIf([qty]<0 AND [Saleprice]<0,0-[Saleprice],[Saleprice])

This will turn the Sale price into a positive number if both values are negative, otherwise it will use whatever value is in the data.

I hope this helps.

Regards,
Rob

Thx Rob,

That worked a treat, i now have vaild data again

Cheers
Darren