Change positive numbers (debits) to negative numbers (credits)

[Carla Gilless]

I am creating a cash management system and one of the difficulties I am
running into is how to have positive numbers (debits) changed to negative
numbers (credits) in the reconciliation report. The user will input all
positive numbers, but when the recon report appears, any credits need to be
negative numbers. I have created a custom format for the credits to appear
in red, in parens, but their actual value in my totals calculations are still
positive.
How do I change these to negative numbers?
A related question is: 2 fields:, one is curadjstnd - this is a currency
field where user inputs numbers, another field is curadjstnd - this field has
is a drop down with 3 choices - increase, decrease, none. I need to have, in
the report, a code that determines if the curadjstnd field is decrease, then
change the number to a negative number.
I'm between a novice and intermediate in VBA, so any detailed help would be
appreciated. Thanks.

 

[Carla Gilless]

Oops, made a typo. The second field in the curradjstnd question is
adjustment action standard. This field is the dropdown list with increase,
decrease and none.

 

[Steve Schapel]

Carla,

What is the means of indicating that an entry is a credit or a debit?
Are all of the figures being entered into one field, and then another
field shows the transaction type as credit or debit? Or are the credits
and debits entered into separate fields?

Your [adjustment action standard] field could be a number data type.
The combobox could be based on a table that has 2 columns, one of which
is text to show the increase, decrease, none values, and the other a
number to show 1 for increase, -1 for decrease. The combobox can be set
up so the text values from the table are shown, but the number values
are hidden in the combobox but this is the "bound column". Then in your
calculations in your queries, reports. etc, you just use
[curadjstnd]*[adjustment action standard]

 

[Carla Gilless]

Steve,
Thanks for the reply. The figures are entered from a bank statement where
all numbers are positive. Each credit or debit is a seperate field. For
example, the layout of the form is:
___(amt) of Checks cleared
___(amt) of deposits
___(amt) of Misc. Credits (another issue here is I need to let the user
enter calculations in this field as there may be several credits that need to
be added: i.e. 355+100+50 and only the total amount showing in the Misc.
credit field
From here, the user goes to another form to enter daily draw amounts or
other adjustments.
The reconciliation report sums all of the amounts, compares that calculation
to the daily draw amount and reports any difference.
The issue is at the reconciliation report, the credits, adjustments and 1 or
two other fields need to be defined as credits (negative numbers). I thought
to just change my calculations to minus the credit fields which works, but
another section of another bank statement has only 1 field for the Misc.
Dr/Cr numbers, and adjustments can be either increases or decreases.
Sorry for the long post, but it's a complex financial reporting system. I
will try the combobox idea with the adjustments. Any other ideas are
welcome. Thanks.

Carla,

What is the means of indicating that an entry is a credit or a debit?
Are all of the figures being entered into one field, and then another
field shows the transaction type as credit or debit? Or are the credits
and debits entered into separate fields?

Your [adjustment action standard] field could be a number data type.
The combobox could be based on a table that has 2 columns, one of which
is text to show the increase, decrease, none values, and the other a
number to show 1 for increase, -1 for decrease. The combobox can be set
up so the text values from the table are shown, but the number values
are hidden in the combobox but this is the "bound column". Then in your
calculations in your queries, reports. etc, you just use
[curadjstnd]*[adjustment action standard]

--
Steve Schapel, Microsoft Access MVP

Oops, made a typo. The second field in the curradjstnd question is
adjustment action standard. This field is the dropdown list with increase,
decrease and none.

:

 

[Steve Schapel]

Carla,

Yes, if the credits and debits are in separate fields, you can simply
subract one and add the other. If they are in the same field, then
there has to be some other data to indicate which type they are, and the
principle here is to do what I've already suggested, i.e. assign a value
of 1 to the pluses and -1 to the minuses and multiply them out.

Let us know if you need any more help with this,