Calculations on forms

  • Thread starter Thread starter plh
  • Start date Start date
P

plh

I have a form that has a calculated field which relies on
a value from another table based on the input of a field
on the form. I created a dlookup to look up the table and
the field, then plug in the associated number in the
calculated field. This works.

Now I need to go another step and don't know how. I have
a table listed by year. Next to each year is a value. On
another table's form, when I use the dlookup to go to this
table and the year, I need to get a total of the value for
the current year, multipled by the value of the next year,
the next year, etc. until it hits the current year. This
total is then put into the form field. Each year is a
separate record.

Is there an easy way to do this other than
value*value*value*value*value*value, etc.

Thanks
 
plh said:
I have a form that has a calculated field which relies on
a value from another table based on the input of a field
on the form. I created a dlookup to look up the table and
the field, then plug in the associated number in the
calculated field. This works.

Now I need to go another step and don't know how. I have
a table listed by year. Next to each year is a value. On
another table's form, when I use the dlookup to go to this
table and the year, I need to get a total of the value for
the current year, multipled by the value of the next year,
the next year, etc. until it hits the current year. This
total is then put into the form field. Each year is a
separate record.

Is there an easy way to do this other than
value*value*value*value*value*value, etc.
Click to expand...


Easy is in the eye of the beholder ;-)

Assuming you made a typo and you actually have text boxes
for the start year and current year, then you could create a
query to do the calculation for you:

SELECT Exp(Sum(Log(valuefield))) As Prod
FROM yeartable
WHERE yearfield Between Forms!theform.startyear
And Forms!theform.currentyear

Then the AfterUpdate event of both the startyear and
currentyear text boxes could set the product text box using
the code:

Me.txtProduct = DLookup("Prod", "theQuery")
 
Back
Top