Calculated Fields

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a form setup like this.

Annual Total Jan Feb March etc.. Adjusted Total
$1000 $0 $0 $0 $1000


What I am trying to do is when I put an intial annual total in I would like
that amount to be divided by 12 and put into the "Month" fields. I then would
like to be able to adjust each month manually and then have a sum in the
"adjusted total" field. I have tried using "After Update", "Before Update"
etc... and nothing will work. Help would be greatly appreciated.

Thanks in advance
Derek
 
sonofroy said:
I have a form setup like this.

Annual Total Jan Feb March etc.. Adjusted Total
$1000 $0 $0 $0 $1000


What I am trying to do is when I put an intial annual total in I would
like
that amount to be divided by 12 and put into the "Month" fields. I then
would
like to be able to adjust each month manually and then have a sum in the
"adjusted total" field. I have tried using "After Update", "Before Update"
etc... and nothing will work. Help would be greatly appreciated.
Click to expand...

In the After Update event sub of the Annual Total field, put

Me!Jan = [AnnualTotal] / 12
Me!Feb = [AnnualTotal] / 12
Me!Mar = [AnnualTotal] / 12
: ' etc., etc.
:
Me!Dec = [AnnualTotal] / 12

and make the data Source of AdjustedTotal be:

= Me!Jan + Me!Feb + ... + Me!Dec

(Fill in the ... with the other months of course!)

Tom Lake
 
I have a form setup like this.

Annual Total Jan Feb March etc.. Adjusted Total
$1000 $0 $0 $0 $1000
Click to expand...

If you have a Table set up like this... it is incorrectly normalized.

Storing data in a fieldname is NEVER correct. You're describing a
spreadsheet design; that's fine for Excel, but is completely
inappropriate for a relational database!
What I am trying to do is when I put an intial annual total in I would like
that amount to be divided by 12 and put into the "Month" fields. I then would
like to be able to adjust each month manually and then have a sum in the
"adjusted total" field. I have tried using "After Update", "Before Update"
etc... and nothing will work. Help would be greatly appreciated.
Click to expand...

A proper table design would have one record per month (and would
include the year, perhaps by using a Date/Time field for it; as it is,
does Jan mean January 2005, January 2006, or what?)

The Adjusted Total field would NOT EXIST in your table, as it can be
calculated on demand by a Totals query. If you insist on keeping your
flawed design, you can put a textbox on the form labeled Adjusted
Total and set its Control Source property to

=NZ([Jan]) + NZ([Feb]) + NZ([Mar]) + <etc. etc.>

In the AfterUpdate event of [Annual Total] you could put code like

Private Sub txtAnnualTotal_AfterUpdate()
Me![Jan] = Me!txtAnnualTotal / 12
Me![Feb] = Me!txtAnnualTotal / 12
Me![Mar] = Me!txtAnnualTotal / 12
<etc>
End Sub

This will overwrite anything that currently exists in these fields.

John W. Vinson[MVP]
 
I never knew this. I appreciate the advice and I will try it. Thanks a bunch!

John Vinson said:
I have a form setup like this.

Annual Total Jan Feb March etc.. Adjusted Total
$1000 $0 $0 $0 $1000
Click to expand...

If you have a Table set up like this... it is incorrectly normalized.

Storing data in a fieldname is NEVER correct. You're describing a
spreadsheet design; that's fine for Excel, but is completely
inappropriate for a relational database!
What I am trying to do is when I put an intial annual total in I would like
that amount to be divided by 12 and put into the "Month" fields. I then would
like to be able to adjust each month manually and then have a sum in the
"adjusted total" field. I have tried using "After Update", "Before Update"
etc... and nothing will work. Help would be greatly appreciated.
Click to expand...

A proper table design would have one record per month (and would
include the year, perhaps by using a Date/Time field for it; as it is,
does Jan mean January 2005, January 2006, or what?)

The Adjusted Total field would NOT EXIST in your table, as it can be
calculated on demand by a Totals query. If you insist on keeping your
flawed design, you can put a textbox on the form labeled Adjusted
Total and set its Control Source property to

=NZ([Jan]) + NZ([Feb]) + NZ([Mar]) + <etc. etc.>

In the AfterUpdate event of [Annual Total] you could put code like

Private Sub txtAnnualTotal_AfterUpdate()
Me![Jan] = Me!txtAnnualTotal / 12
Me![Feb] = Me!txtAnnualTotal / 12
Me![Mar] = Me!txtAnnualTotal / 12
<etc>
End Sub

This will overwrite anything that currently exists in these fields.

John W. Vinson[MVP]
Click to expand...
 
Thanks for the help

Tom Lake said:
sonofroy said:
I have a form setup like this.

Annual Total Jan Feb March etc.. Adjusted Total
$1000 $0 $0 $0 $1000


What I am trying to do is when I put an intial annual total in I would
like
that amount to be divided by 12 and put into the "Month" fields. I then
would
like to be able to adjust each month manually and then have a sum in the
"adjusted total" field. I have tried using "After Update", "Before Update"
etc... and nothing will work. Help would be greatly appreciated.
Click to expand...

In the After Update event sub of the Annual Total field, put

Me!Jan = [AnnualTotal] / 12
Me!Feb = [AnnualTotal] / 12
Me!Mar = [AnnualTotal] / 12
: ' etc., etc.
:
Me!Dec = [AnnualTotal] / 12

and make the data Source of AdjustedTotal be:

= Me!Jan + Me!Feb + ... + Me!Dec

(Fill in the ... with the other months of course!)

Tom Lake
Click to expand...
 
I was actually using the year in a seperate field

John Vinson said:
I have a form setup like this.

Annual Total Jan Feb March etc.. Adjusted Total
$1000 $0 $0 $0 $1000
Click to expand...

If you have a Table set up like this... it is incorrectly normalized.

Storing data in a fieldname is NEVER correct. You're describing a
spreadsheet design; that's fine for Excel, but is completely
inappropriate for a relational database!
What I am trying to do is when I put an intial annual total in I would like
that amount to be divided by 12 and put into the "Month" fields. I then would
like to be able to adjust each month manually and then have a sum in the
"adjusted total" field. I have tried using "After Update", "Before Update"
etc... and nothing will work. Help would be greatly appreciated.
Click to expand...

A proper table design would have one record per month (and would
include the year, perhaps by using a Date/Time field for it; as it is,
does Jan mean January 2005, January 2006, or what?)

The Adjusted Total field would NOT EXIST in your table, as it can be
calculated on demand by a Totals query. If you insist on keeping your
flawed design, you can put a textbox on the form labeled Adjusted
Total and set its Control Source property to

=NZ([Jan]) + NZ([Feb]) + NZ([Mar]) + <etc. etc.>

In the AfterUpdate event of [Annual Total] you could put code like

Private Sub txtAnnualTotal_AfterUpdate()
Me![Jan] = Me!txtAnnualTotal / 12
Me![Feb] = Me!txtAnnualTotal / 12
Me![Mar] = Me!txtAnnualTotal / 12
<etc>
End Sub

This will overwrite anything that currently exists in these fields.

John W. Vinson[MVP]
Click to expand...
 
Back
Top