Conditional amounts in a report

[Sam B.]

I have a report that shows data from a database table with fields as
follows:

Date merchant_name debit_credit amount
1/5/05 Home Depot #2 D 150.65
2/7/05 Lowe's C 150.65

How do I program a report to show amounts as follows:

If debit_credit field value is D, the amount should show as positive
If debit_credit field value is C, the amount should show as negative

I'm using MS Access 2003. Thanks in advance.

 

[Guest]

Just a thought, but perhaps it would be easier to initially input your data
as such.

assuming those are your field names in your table...
Date merchant_name amount
1/5/05 Home Depot #2 -150.65
2/7/05 Lowes 150.65

this would eliminate the need for the credit_debit field and help facilitate
any math you may need to do in forms and reports later.

/amelia

 

[Sam B.]

Thank you for your suggestion. However, I import the data from a
financial bank that uses the debit_credit field (approx. 1,200
transactions a month). I prefer not to change the bank data. Is there a
way to manipulate a report instead?

 

[Guest]

i'm not really sure, but i would start with a conditional macro using the
SetValue action:

Condition1: [Forms]![Form_Name]![debit_credit]="D"
SetValue
Item [Forms]![Form_Name]![amount]
Expression ....this is where i get hazy
Condition2: [Forms]![Form_Name]![debit_credit]="C"
SetValue
Item [Forms]![Form_Name]![amount]
Expression ....this is where i get hazy

that's the best i got.

good luck!

 

[John Spencer (MVP)]

Set the control's ControlSource to

=IIF([debit-Credit] = "C", -1*[Amount], [Amount])

Make sure your control is not named "Amount" or Access will give you an error
since it will not be able to figure out whether you are referring to the control
named "amount" or the field named "amount".