Calculated Field

[Paul Richards]

I have a form which is used to create a new record in my application.

I need make a calculation for a field which is conditional on the status
of another field, as follows:

Field 1 (Y/N) field: if true then Field B = Field C*0.25
If false then Field B = Field C*0.10

What would be the correct expression to set Field B’s Control Source
property to?

Thanks

 

[Guest]

There is no correct way to do this. Your first mistake is storing a
calculated value. You should never do that. It wastes disk space, It wastes
time, and if any value in your database used to calculate the value changes,
it will end up wrong. Do the calculation when you want to present it to a
human, either in forms or reports, or when you export your data to an
external entity.

In addition, you can't use a calculation as a control's control source. The
correct techinque is to make Field B an unbound field. Then create a public
function in a standard module that can be referrenced anywhere in your
application to show the results of the calculation. It should look Like this:

Public Function CalcDiscount(blnDisc As Boolean, varTheNumber As Variant) _
As Variant

If IsNull(varTheNumber) Then
CalcDiscount = Null
Else
CalcDiscount = varTheNumber * IIf(blnDisc, .25, .10)
End If

Now, as to your form, there are 3 places you need to call this function:
1. The After Update event of Field 1
2. The After Update event of Field C
3. The form's Current event

You will call it like this:

Me.FieldB = CalcDiscount(Me.Field1, Me.FieldC)



End Function

 

[Powderfinger]

I have a form which is used to create a new record in my application.

I need make a calculation for a field which is conditional on the status
of another field, as follows:

Field 1 (Y/N) field: if true then Field B = Field C*0.25
If false then Field B = Field C*0.10

What would be the correct expression to set Field B’s Control Source
property to?

Thanks

=IIf([Field 1],[Field C]*0.25,[Field C]*0.10)


You would use this expression on all forms and reports whenever you wanted
to display Field B's value. Field B would not be a field in a table.

 

[Paul Richards]

Many thanks for pointing out the error of my ways!

 

[Paul Richards]

Many thanks for pointing out the error of my ways!

I have a form which is used to create a new record in my application.

I need make a calculation for a field which is conditional on the status
of another field, as follows:

Field 1 (Y/N) field: if true then Field B = Field C*0.25
If false then Field B = Field C*0.10

What would be the correct expression to set Field B’s Control Source
property to?

Thanks

=IIf([Field 1],[Field C]*0.25,[Field C]*0.10)


You would use this expression on all forms and reports whenever you wanted
to display Field B's value. Field B would not be a field in a table.