Convert Positive Number to Negative

[indyaries]

Greetings,

Searched the forum for a method to convert a Positive Number to a Negative Number based on the data in another field. Could not find anything.

I get data in Excel 97 which contains a field called DetailAmount. All of the numbers are positive.

Another field called TransactionType contains letters. The letter P indicates that the number in the DetailAmount field is a Negative number.

Q. Is there an expression in Access or Excel which will convert a Positive Number to a Negative Number so that the calculations will be accurate?

Thanks !!
(Using Office 97 at work)

 

[raskew]

From the debug window:

DetailAmount = 123.45
TransactionType = "P"
DetailAmount = DetailAmount* Iif(TransactionType="P",-1,1)
? DetailAmount
-123.45

DetailAmount = 123.45
TransactionType = "X"
DetailAmount = DetailAmount* Iif(TransactionType="P",-1,1)
? DetailAmount
123.45

 

[indyaries]

Hmmm..

Raskew,

Thank you for your reply. I've tried inserting your expression into a query grid, but it's not working.

I also tried to insert a minus sign (-) at the far left of the number I need to change to a negative, but it doesn't work either.

In the query grid:
Detail1: Format("-" & [DetailAmount],"Currency")

This seems to make the number a negative, but when I attempt to perform math on this cell, it still behaves as a positive number. It also seems to be formatted like text (left justified) rather than numeric (right justified).

Could you please show how I can use your expression in a query grid?

Thank you.

 

[Rich]

*-1 will convert to neg

 

[neileg]

Put this into the QBE grid. Don't add Detail amount as a field on its own, use this instead.

DetailAmount: DetailAmount * Iif(TransactionType="P",-1,1)

 

[indyaries]

Sorry for the delay...

Sorry for the delay in replying.

Your solution worked!

Thank you very much!

Bob in Indy