Positive form values to Negative table values?

[Ian Chappel]

If I have a Receipts form and Payments form based on the same table, say
NomPayments, I don't want the user to have to enter negative numbers in one
case, e.g. if Payments are stored as positive figures, then Receipts would
be negative. Is there a neat way at form level to display and input positive
figures but store them as negative? I feel it should be possible, maybe by
hiding the bound control and triggering events to multiply by -1 and display
in a calculated control, but this seems a little convoluted. Or is it better
to simply store all values as positive and have a "negative flag"?

 

[Allen Browne]

If the receipts and payments are both stored in one transaction table, then
having a sign field as a multiplier (1 for positive, -1 for negative) is a
workable solution.

If they are in different tables, and you actually want to negate the value
that the user types and show it as a negative, you can use the AfterUpdate
event of the text box to do that. The following example provides a way for
the value to default to negative, but respects any sign that the user
actually types. It does that by checking the Text property of the box to see
if the user actually typed the sign. To use it, set the AfterUpdate property
of a text box named Amount to:
=MakeNegative([Amount])

Function MakeNegative(txt As TextBox)
If Not IsNull(txt.Value) Then
Select Case Asc(txt.Text)
Case 43, 45 'Plus or minus
'do nothing
Case Else
txt.Value = -txt.Value
End Select
End If
End Function

 

[Ian Chappel]

Thanks - they will be in the same table.

I was going to use a Yes/No flag, but I guess that a 1 / -1 multiplier field
would make manipulating the data simpler later.

If the receipts and payments are both stored in one transaction table,
then having a sign field as a multiplier (1 for positive, -1 for negative)
is a workable solution.

If they are in different tables, and you actually want to negate the value
that the user types and show it as a negative, you can use the AfterUpdate
event of the text box to do that. The following example provides a way for
the value to default to negative, but respects any sign that the user
actually types. It does that by checking the Text property of the box to
see if the user actually typed the sign. To use it, set the AfterUpdate
property of a text box named Amount to:
=MakeNegative([Amount])

Function MakeNegative(txt As TextBox)
If Not IsNull(txt.Value) Then
Select Case Asc(txt.Text)
Case 43, 45 'Plus or minus
'do nothing
Case Else
txt.Value = -txt.Value
End Select
End If
End Function

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

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

If I have a Receipts form and Payments form based on the same table, say
NomPayments, I don't want the user to have to enter negative numbers in
one case, e.g. if Payments are stored as positive figures, then Receipts
would be negative. Is there a neat way at form level to display and input
positive figures but store them as negative? I feel it should be
possible, maybe by hiding the bound control and triggering events to
multiply by -1 and display in a calculated control, but this seems a
little convoluted. Or is it better to simply store all values as positive
and have a "negative flag"?