Calculation advise

[myxmaster]

I have a form with a combo box with deposit and withdrawal as the
selection. Everything works fine until I attempt to calculate the
amounts. Would it be advisable to have 2 seperate fields in a table?,
or would it be advisable to have the user enter all withdrawals as a
(- minus) figure.
Any advise is most appreciated.

 

[strive4peace]

personally, I opt for one field for transaction amount

on the form for users, you may wish to verify the sign is correct --
either by using an unbound checkbox or by having 2 unbound controls --
one for withdrawel and one for deposit -- then, on the AfterUpdate
event, you would write the correct amount to your transaction amount
field.

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

 

[Allen Browne]

It sounds like you have a field that holds either "deposit" or "withdrawal".

Could you change this field in your table like this:
Field Name Direction
Type Number
Size Integer
Required Yes
Validation Rule -1 or 1
Display Control Combo
Row Source Type Value List
Row Source -1;"Withdrawal";1;"Deposit"
Bound Column 1
Column Count 2

This gives you the same effect, but stores the value -1 for a withdrawal, or
1 for a deposit.

You can now create a query, and type this into a fresh column in the Field
row:
NetAmount: [Direction] * [Amount]
substituting your field name for Amount.

You can see how this will give the correct total when you sum the NetAmount
in a report.

 

[Allen Browne]

Forget to say to set the combo's Column Widths property to:
0
so it shows the text instead of the number.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

It sounds like you have a field that holds either "deposit" or
"withdrawal".

Could you change this field in your table like this:
Field Name Direction
Type Number
Size Integer
Required Yes
Validation Rule -1 or 1
Display Control Combo
Row Source Type Value List
Row Source -1;"Withdrawal";1;"Deposit"
Bound Column 1
Column Count 2

This gives you the same effect, but stores the value -1 for a withdrawal,
or 1 for a deposit.

You can now create a query, and type this into a fresh column in the Field
row:
NetAmount: [Direction] * [Amount]
substituting your field name for Amount.

You can see how this will give the correct total when you sum the
NetAmount in a report.

I have a form with a combo box with deposit and withdrawal as the
selection. Everything works fine until I attempt to calculate the
amounts. Would it be advisable to have 2 seperate fields in a table?,
or would it be advisable to have the user enter all withdrawals as a
(- minus) figure.
Any advise is most appreciated.

 

[strive4peace]

Allen's advice in the next post is great

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*