Another calculated field question.

[Microsoft]

Greetings,

It seems that every time I solve one problem I creae a new one.

I have a calculated field on a subform. The data is a result of multiplying
the number of nights a guest stays times the rate per night. The info is
then used in several other fields to calculate other values. I want to add
the ability to force a specific amont into the calculated field. I charge
$1,000 for a 7 night stay. This works out to $142.85714285714285714 . . . a
night. Rounding this down to 2 decimal points gives me a slightly off tax
amount.

My solution was to add a Y/N field to the table to indicate a weekly stay.
Then I changed the formula for the calculated field from =([StayLength] *
[Rate]) to =IIf([WeeklyStay], 1000, ([StayLength] * [Rate])). This works to
make the field show $1,000, but it does not work in the other calculated
fields that use that field result for their data. Specifical;ly, it does
not create the right tax amount. What other info do you need to help on
this? I greatly appreciate all the help I get from this group.

Best regards,
Scott B

 

[tina]

-----Original Message-----
Greetings,

It seems that every time I solve one problem I creae a new one.

I have a calculated field on a subform. The data is a result of multiplying
the number of nights a guest stays times the rate per night. The info is
then used in several other fields to calculate other values. I want to add
the ability to force a specific amont into the calculated field. I charge
$1,000 for a 7 night stay. This works out to $142.85714285714285714 . . . a
night. Rounding this down to 2 decimal points gives me a slightly off tax
amount.

My solution was to add a Y/N field to the table to indicate a weekly stay.
Then I changed the formula for the calculated field from = ([StayLength] *
[Rate]) to =IIf([WeeklyStay], 1000, ([StayLength] * [Rate])). This works to
make the field show $1,000, but it does not work in the other calculated
fields that use that field result for their data. Specifical;ly, it does
not create the right tax amount. What other info do you need to help on
this? I greatly appreciate all the help I get from this group.

Best regards,
Scott B


.
instead of writing the formula directly into the unbound

control's control source property, have you tried writing
a procedure to assign the calculated value to the control?
you'd have to decide where to run the procedure, such as
in the afterupdate event of appropriate data entry
controls, and/or the form's OnCurrent event. and you'd
probably want to include a requery action for each control
that depends on this calculated value, and make that part
of the procedure, such as:

Sub GetValue()

Me!FirstCalculatedField = MathFormula
Me!SecondCalculatedField.Requery
Me!ThirdCalculatedField.Requery

End Sub

i tried a simple version of this, running the sub from a
command button, on an unbound form, and it worked.