A very simple way to do this is to add another field called (say) Multipler,
type Number, size Long Integer, Required, Validation Rule:
1 or -1
Add a text box to both forms. On one form, set the text box's Default Value
to 1, and on the other form -1. You can set the Visible property to No on
both forms, but the Default Value ensures that right value is assigned when
new records are entered into the form.
The RecordSource for both forms is a query statement:
SELECT * FROM MyTable WHERE Multipler = 1;
SELECT * FROM MyTable WHERE Multipler = -1;
and that loads the desired records into each form.
You can now sum the Amount field like this:
Sum([Multipler] * [Amount])
to get the desired result.
A side-effect of this design is that it is actually possible to put a
reversal entry through, i.e. you can use a negative Amount in either form.
In some designs that's very desirable; if you don't want that to occur, use
a Validation Rule on the Amount field of:
There are other ways to approach this issue. For example, you can make a
text box default to negative by examining its Text property in its
AfterUpdate event, and if the first character is not a sign ("+" or "-")
then negate its value.
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Reply to group, rather than allenbrowne at mvps dot org.
Rose said:
I have a transactions table with a field named Transaction. I want Transaction
to be positive for input and negative for outflow. How do I set up the form for
entering outflow so for data entry, the value is entered as a positive number,
is stored as a negative number and when the form is opened to the same record it
displays a a positive number. Is the form based on a query and if so what does
the Transaction field in the query look like?
Thanks!
Rose
