Change sign in amount field if record is a receipt? 1

[gusbrunston]

I've looked, I really have! But keyword searches are pretty tough for me.

Query is select from the table "tblMasterJournal", which contains records of receipts and expenditures, including a "TransactionAmount" field.

I have a yes/no field to indicate if the record is a receipt or an expenditure ("ReceiptOrPayment&quot

In forms and reports, how can I change the sign (from $1,000.00 to -$1,000.00) if the record is a payment?

I've tried several versions of an expression such as:

=IIF(IsNull([ReceiptOrPayment]),[TransactionAmount],[TransactionAmount]* -1)

Could use some help in the morning...I'm headed for bed.
Thanks in advance,
Gus Brunston
An old PICKer
padregus@home.com

 

[LonnieJohnson]

Hey gusbrunston,

You have a couple of ways to approach this.

1. You can determine the value in the query by creating a new column in the query grid. I called the field "Actual". The expression for the field would be...

Actual: IIf([ReceiptOrPayment]= True,-[Amount],[Amount])

This statement is based on the assumption that True or Yes in your Yes/No ReceiptOrPayment field means a Receipt. If it is true it sets the Amount field to a negitive value and if not. It displays the positive value.

2. If you wish to take care of this at the report or form level. You will need to put your Amount and ReceiptOrPayment fields on your form/report and set the visible property to No. Now add a new field and set it's control source equal to our expression...

=IIf([ReceiptOrPayment]= True,-[Amount],[Amount])

Good luck...

B-) ljprodev@yahoo.com
ProDev
MS Access Applications

 

[gusbrunston]

Thank you Lonnie!
If I live long enough, I'll get this Access2000. Thanks to your response I think I've caught on how to write a simple condtional "If...then...else" algorithym:
Change the "If" to "IIf", drop the "then" and the "else", put the values in brackets and surround the whole expression in parenthesis. It probably says this somewhere in "help"...oh well.
I worked around my problem earlier by using separate fields for receipts and disbursements and making the "+/-" assignment on entry.
Thanks again. I do appreciate the help that is available on Tek-Tips, and your contributions are the tops.
Sincerely,
Gus Brunston
An old PICKer
padregus@home.com