By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,516 Members | 1,178 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,516 IT Pros & Developers. It's quick & easy.

Entering A Transaction

P: n/a
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
Nov 13 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
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:
=0
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.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Rose" <ro*******@earyhlonk.net> wrote in message
news:Mw*****************@newsread2.news.atl.earthl ink.net... 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

Nov 13 '05 #2

P: n/a
Allen,

Thanks for the prompt response! Works perfect!

Rose
"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message
news:40**********************@per-qv1-newsreader-01.iinet.net.au...
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:
>=0


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.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Rose" <ro*******@earyhlonk.net> wrote in message
news:Mw*****************@newsread2.news.atl.earthl ink.net...
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


Nov 13 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.