Calculated Field in Form

  • Thread starter Thread starter Paul Fenton
  • Start date Start date
P

Paul Fenton

I have a form with a field that's calculated from two other fields.
The data source for the field is

=[txtpayment]-nz([txtcodpartial1])-nz([txtCODPartial2]

Because of the way payments are posted (intentionally) that will
result in a negative number, which I do not want.

How can I get that field to display the value if it's positive but to
display 0.00 if the value is negative?


Paul Fenton
(e-mail address removed)
 
Cant do it that way. a calculated fields value cannot be changed, it is not
updateable. You need to code the calculation in the afterupdate events of
the txtcodpartial1 and txtcodpartial2. then test the results for negative
values and set txtpayment to 0.00 if true.
 
HI Paul!!
I'm assuming that your form is connected to a table,
therefore as a navigation bar at the bottom of-it.
If its the case then you can copy this code into
the 'Current' event of you form.
Dim e1 As Double

e1 = [txtpayment]-nz([txtcodpartial1])-nz([txtCODPartial2]

If e1 < 0 Then ' if result lower then 1 then its negative.
result.Value = "0.00"
Else
result.Value = e1
endif

the 'result.value' represents a textbox somewhere on that
same form where the result is stored. You can change that
part to adapt you specific needs.... The concept will be
the same.

Just change the 'result.value' to reflect where the result
is supposed to be....

This should help, this concept might require a bit more
tweaking where the formula is affected to the e1 variable..
If it never returns negative number. Othewise, it should
do the trick..

PAtrick
 
I have a form with a field that's calculated from two other fields.
The data source for the field is

=[txtpayment]-nz([txtcodpartial1])-nz([txtCODPartial2]

Because of the way payments are posted (intentionally) that will
result in a negative number, which I do not want.

How can I get that field to display the value if it's positive but to
display 0.00 if the value is negative?


Paul Fenton
(e-mail address removed)
Click to expand...

In an unbound control:
=IIf([txtpayment]-nz([txtcodpartial1])-nz([txtCODPartial2])<0,"0.00",
[txtpayment]-nz([txtcodpartial1])-nz([txtCODPartial2]))

Or..
simply set the Format property of the control to:
#;"0.00"
 
To the responders, thank you so much for the lessons! Both of those
approaches work and I have the result I wanted.


Paul Fenton
 
Back
Top