Form calculation based on the date - How to????

I

iez44

Access 2003 issue......

My current form has a field that makes a calculation based on the value of
another field, multiplies it by a fixed number and displays the results in
currency. Basically - # of hours times an hourly rate = total dollar amount.
To accomplish this, I have the field run a macro which performs the calc.
This works just fine.

Now the hourly rate changed in 2008 and by changing my macro to reflect this
new value, I believe the change has recalculated all previous 2007 entries.
(which I don't want!)

So I thought that a possible solution would be to create separate yearly
macros reflecting the correct costing rates, write code to look at the form's
date field and then run the correct macro calculation based on that date.
I'm having a problem with the code. My VB skills are obviously lacking.

Any help would be appreciated!!!!
 
F

Frank

You might like to try (test) this.

Create a Form with these controls -

Text Box: txtNumberOfHours

Combo Box: cboEffectiveDate

Text Box: txtTotalDollarAmount

Create a Table tblHourlyRates with two fields (dtmDateApplicable,
curHourlyRate).

Set RowSource for cboEffectiveDate -
SELECT Format(dtmDateApplicable,"ddd/mm/yy") FROM tblHourlyRates ORDER BY
dtmDateApplicable DESC

Set ControlSource for txtTotalDollarAmount -
=[txtNumberOfHours]*DLookUp("curHourlyRate","tblHourlyRates","dtmDateApplicable = #" & Format(CDate([cboEffectiveDate]),"mm/dd/yy\#"))

Enter two records in tblHourlyRates -
18/1/08 (or American 1/18/08), $20
18/1/09 (or American 1/18/09), $30

Save the form.
Open the form.
Type 10 in txtNumberOfHours
Pick the 2008 date in cboEffectiveDate.
You should see 200 appear in txtTotalDollarAmount.
Change the date in cboEffectiveDate to the 2009 date.
You should see 300 appear in txtTotalDollarAmount.

Note that only NumberOfHours and EffectiveDate should be stored in your
table - TotalDollarAmount will be calculated whenever you display the form
(or a similar Report).

Good Luck!
 
F

Frank

Sorry ...

Set RowSource for cboEffectiveDate -
SELECT Format(dtmDateApplicable,"dd/mm/yy") FROM tblHourlyRates ORDER BY
dtmDateApplicable DESC

dd/mm/yy NOT ddd/mm/yy

Frank said:
You might like to try (test) this.

Create a Form with these controls -

Text Box: txtNumberOfHours

Combo Box: cboEffectiveDate

Text Box: txtTotalDollarAmount

Create a Table tblHourlyRates with two fields (dtmDateApplicable,
curHourlyRate).

Set RowSource for cboEffectiveDate -
SELECT Format(dtmDateApplicable,"ddd/mm/yy") FROM tblHourlyRates ORDER BY
dtmDateApplicable DESC

Set ControlSource for txtTotalDollarAmount -
=[txtNumberOfHours]*DLookUp("curHourlyRate","tblHourlyRates","dtmDateApplicable = #" & Format(CDate([cboEffectiveDate]),"mm/dd/yy\#"))

Enter two records in tblHourlyRates -
18/1/08 (or American 1/18/08), $20
18/1/09 (or American 1/18/09), $30

Save the form.
Open the form.
Type 10 in txtNumberOfHours
Pick the 2008 date in cboEffectiveDate.
You should see 200 appear in txtTotalDollarAmount.
Change the date in cboEffectiveDate to the 2009 date.
You should see 300 appear in txtTotalDollarAmount.

Note that only NumberOfHours and EffectiveDate should be stored in your
table - TotalDollarAmount will be calculated whenever you display the form
(or a similar Report).

Good Luck!

iez44 said:
Access 2003 issue......

My current form has a field that makes a calculation based on the value of
another field, multiplies it by a fixed number and displays the results in
currency. Basically - # of hours times an hourly rate = total dollar amount.
To accomplish this, I have the field run a macro which performs the calc.
This works just fine.

Now the hourly rate changed in 2008 and by changing my macro to reflect this
new value, I believe the change has recalculated all previous 2007 entries.
(which I don't want!)

So I thought that a possible solution would be to create separate yearly
macros reflecting the correct costing rates, write code to look at the form's
date field and then run the correct macro calculation based on that date.
I'm having a problem with the code. My VB skills are obviously lacking.

Any help would be appreciated!!!!
Click to expand...
Click to expand...
 
I

iez44

Frank,

While this solution is viable, I cannot allow the user to select a date from
a list. Management wants the form to be idiot proof. Basically, the user
enters the date and the # of hours and that's it. The calculation is then
done automatically in the background without any further user input.

So, I'm thinking I would have to write code to check the form's date field.
If the date falls between 1/1/07 and 12/31/07, then the calculation with the
2007 cost rate would be performed. If between 1/1/2008 and 12/31/2008... etc
etc.

....or is this not a good solution? If so, can you help with that code?
Thanks,
iez44







Frank said:
Sorry ...

Set RowSource for cboEffectiveDate -
SELECT Format(dtmDateApplicable,"dd/mm/yy") FROM tblHourlyRates ORDER BY
dtmDateApplicable DESC

dd/mm/yy NOT ddd/mm/yy

Frank said:
You might like to try (test) this.

Create a Form with these controls -

Text Box: txtNumberOfHours

Combo Box: cboEffectiveDate

Text Box: txtTotalDollarAmount

Create a Table tblHourlyRates with two fields (dtmDateApplicable,
curHourlyRate).

Set RowSource for cboEffectiveDate -
SELECT Format(dtmDateApplicable,"ddd/mm/yy") FROM tblHourlyRates ORDER BY
dtmDateApplicable DESC

Set ControlSource for txtTotalDollarAmount -
=[txtNumberOfHours]*DLookUp("curHourlyRate","tblHourlyRates","dtmDateApplicable = #" & Format(CDate([cboEffectiveDate]),"mm/dd/yy\#"))

Enter two records in tblHourlyRates -
18/1/08 (or American 1/18/08), $20
18/1/09 (or American 1/18/09), $30

Save the form.
Open the form.
Type 10 in txtNumberOfHours
Pick the 2008 date in cboEffectiveDate.
You should see 200 appear in txtTotalDollarAmount.
Change the date in cboEffectiveDate to the 2009 date.
You should see 300 appear in txtTotalDollarAmount.

Note that only NumberOfHours and EffectiveDate should be stored in your
table - TotalDollarAmount will be calculated whenever you display the form
(or a similar Report).

Good Luck!

iez44 said:
Access 2003 issue......

My current form has a field that makes a calculation based on the value of
another field, multiplies it by a fixed number and displays the results in
currency. Basically - # of hours times an hourly rate = total dollar amount.
To accomplish this, I have the field run a macro which performs the calc.
This works just fine.

Now the hourly rate changed in 2008 and by changing my macro to reflect this
new value, I believe the change has recalculated all previous 2007 entries.
(which I don't want!)

So I thought that a possible solution would be to create separate yearly
macros reflecting the correct costing rates, write code to look at the form's
date field and then run the correct macro calculation based on that date.
I'm having a problem with the code. My VB skills are obviously lacking.

Any help would be appreciated!!!!
Click to expand...
Click to expand...
Click to expand...
 
F

Frank

Try this.

Change the Combo Box to a Text Box (into which the date will be typed) and
rename it txtDate.

In tblHourlyRates change dtmDateApplicable to dtmEffectiveFrom (ie the date
a new Hourly Rate applies from).

Create a Query named qryHourlyRates with this SQL -
SELECT dtmEffectiveFrom, curHourlyRate
FROM tblHourlyRates
ORDER BY dtmEffectiveFrom DESC

Change ControlSource for txtTotalDollarAmount to this -
=IIf(Not (IsNull([txtNumberOfHours]) Or
IsNull([txtDate])),[txtNumberOfHours]*DLookUp("curHourlyRate","qryHourlyRates","dtmEffectiveFrom <= #" & Format(CDate([txtDate]),"mm/dd/yy\#")))

Cheers ...

Frank said:
You might like to try (test) this.

Create a Form with these controls -

Text Box: txtNumberOfHours

Combo Box: cboEffectiveDate

Text Box: txtTotalDollarAmount

Create a Table tblHourlyRates with two fields (dtmDateApplicable,
curHourlyRate).

Set RowSource for cboEffectiveDate -
SELECT Format(dtmDateApplicable,"ddd/mm/yy") FROM tblHourlyRates ORDER BY
dtmDateApplicable DESC

Set ControlSource for txtTotalDollarAmount -
=[txtNumberOfHours]*DLookUp("curHourlyRate","tblHourlyRates","dtmDateApplicable = #" & Format(CDate([cboEffectiveDate]),"mm/dd/yy\#"))

Enter two records in tblHourlyRates -
18/1/08 (or American 1/18/08), $20
18/1/09 (or American 1/18/09), $30

Save the form.
Open the form.
Type 10 in txtNumberOfHours
Pick the 2008 date in cboEffectiveDate.
You should see 200 appear in txtTotalDollarAmount.
Change the date in cboEffectiveDate to the 2009 date.
You should see 300 appear in txtTotalDollarAmount.

Note that only NumberOfHours and EffectiveDate should be stored in your
table - TotalDollarAmount will be calculated whenever you display the form
(or a similar Report).

Good Luck!

iez44 said:
Access 2003 issue......

My current form has a field that makes a calculation based on the value of
another field, multiplies it by a fixed number and displays the results in
currency. Basically - # of hours times an hourly rate = total dollar amount.
To accomplish this, I have the field run a macro which performs the calc.
This works just fine.

Now the hourly rate changed in 2008 and by changing my macro to reflect this
new value, I believe the change has recalculated all previous 2007 entries.
(which I don't want!)

So I thought that a possible solution would be to create separate yearly
macros reflecting the correct costing rates, write code to look at the form's
date field and then run the correct macro calculation based on that date.
I'm having a problem with the code. My VB skills are obviously lacking.

Any help would be appreciated!!!!
Click to expand...
Click to expand...
 
I

iez44

This solution worked perfectly to calculate and display the correct value
(based on the date) on the form. (THANK YOU!)

However, I now have a problem where this calculated field is never written
to the record in the table where this information is stored. Could it be
that the control source for the form's field is now no longer the same name
as the field name in the table? Regardless of the reason, how do I save this
calculated total in the table?





Frank said:
Try this.

Change the Combo Box to a Text Box (into which the date will be typed) and
rename it txtDate.

In tblHourlyRates change dtmDateApplicable to dtmEffectiveFrom (ie the date
a new Hourly Rate applies from).

Create a Query named qryHourlyRates with this SQL -
SELECT dtmEffectiveFrom, curHourlyRate
FROM tblHourlyRates
ORDER BY dtmEffectiveFrom DESC

Change ControlSource for txtTotalDollarAmount to this -
=IIf(Not (IsNull([txtNumberOfHours]) Or
IsNull([txtDate])),[txtNumberOfHours]*DLookUp("curHourlyRate","qryHourlyRates","dtmEffectiveFrom <= #" & Format(CDate([txtDate]),"mm/dd/yy\#")))

Cheers ...

Frank said:
You might like to try (test) this.

Create a Form with these controls -

Text Box: txtNumberOfHours

Combo Box: cboEffectiveDate

Text Box: txtTotalDollarAmount

Create a Table tblHourlyRates with two fields (dtmDateApplicable,
curHourlyRate).

Set RowSource for cboEffectiveDate -
SELECT Format(dtmDateApplicable,"ddd/mm/yy") FROM tblHourlyRates ORDER BY
dtmDateApplicable DESC

Set ControlSource for txtTotalDollarAmount -
=[txtNumberOfHours]*DLookUp("curHourlyRate","tblHourlyRates","dtmDateApplicable = #" & Format(CDate([cboEffectiveDate]),"mm/dd/yy\#"))

Enter two records in tblHourlyRates -
18/1/08 (or American 1/18/08), $20
18/1/09 (or American 1/18/09), $30

Save the form.
Open the form.
Type 10 in txtNumberOfHours
Pick the 2008 date in cboEffectiveDate.
You should see 200 appear in txtTotalDollarAmount.
Change the date in cboEffectiveDate to the 2009 date.
You should see 300 appear in txtTotalDollarAmount.

Note that only NumberOfHours and EffectiveDate should be stored in your
table - TotalDollarAmount will be calculated whenever you display the form
(or a similar Report).

Good Luck!

iez44 said:
Access 2003 issue......

My current form has a field that makes a calculation based on the value of
another field, multiplies it by a fixed number and displays the results in
currency. Basically - # of hours times an hourly rate = total dollar amount.
To accomplish this, I have the field run a macro which performs the calc.
This works just fine.

Now the hourly rate changed in 2008 and by changing my macro to reflect this
new value, I believe the change has recalculated all previous 2007 entries.
(which I don't want!)

So I thought that a possible solution would be to create separate yearly
macros reflecting the correct costing rates, write code to look at the form's
date field and then run the correct macro calculation based on that date.
I'm having a problem with the code. My VB skills are obviously lacking.

Any help would be appreciated!!!!
Click to expand...
Click to expand...
Click to expand...
 
F

Frank

You said ...

"Could it be that the control source for the form's field is now no longer
the same name as the field name in the table?"

I say ...

Exactly ... the text box is no longer "bound" to the field in the underlying
table.

So, create a new text box and call it "txtTotalDollarAmount_Bound" ... set
its ControSource property to the name of the field in your table (so it is
now "bound" to that field).

Now you have to put a value into the new field whenever the value in one of
txtNumberOfHours or txtDate changes do this by putting the following line
into the AfterUpdate event of BOTH these fields -

txtTotalDollarAmount_Bound = Nz(txtTotalDollarAmount,0)

You can't allow an operator to directly enter a value into either
txtTotalDollarAmount or txtTotalDollarAmount_Bound ... for each of these
fields set the Enabled property to "No" and the Locked property to "Yes".
When you have it working properly, set the Visible property to "No" for ONE
of these fields.

Cheers ...

iez44 said:
This solution worked perfectly to calculate and display the correct value
(based on the date) on the form. (THANK YOU!)

However, I now have a problem where this calculated field is never written
to the record in the table where this information is stored. Could it be
that the control source for the form's field is now no longer the same name
as the field name in the table? Regardless of the reason, how do I save this
calculated total in the table?





Frank said:
Try this.

Change the Combo Box to a Text Box (into which the date will be typed) and
rename it txtDate.

In tblHourlyRates change dtmDateApplicable to dtmEffectiveFrom (ie the date
a new Hourly Rate applies from).

Create a Query named qryHourlyRates with this SQL -
SELECT dtmEffectiveFrom, curHourlyRate
FROM tblHourlyRates
ORDER BY dtmEffectiveFrom DESC

Change ControlSource for txtTotalDollarAmount to this -
=IIf(Not (IsNull([txtNumberOfHours]) Or
IsNull([txtDate])),[txtNumberOfHours]*DLookUp("curHourlyRate","qryHourlyRates","dtmEffectiveFrom <= #" & Format(CDate([txtDate]),"mm/dd/yy\#")))

Cheers ...

Frank said:
You might like to try (test) this.

Create a Form with these controls -

Text Box: txtNumberOfHours

Combo Box: cboEffectiveDate

Text Box: txtTotalDollarAmount

Create a Table tblHourlyRates with two fields (dtmDateApplicable,
curHourlyRate).

Set RowSource for cboEffectiveDate -
SELECT Format(dtmDateApplicable,"ddd/mm/yy") FROM tblHourlyRates ORDER BY
dtmDateApplicable DESC

Set ControlSource for txtTotalDollarAmount -
=[txtNumberOfHours]*DLookUp("curHourlyRate","tblHourlyRates","dtmDateApplicable = #" & Format(CDate([cboEffectiveDate]),"mm/dd/yy\#"))

Enter two records in tblHourlyRates -
18/1/08 (or American 1/18/08), $20
18/1/09 (or American 1/18/09), $30

Save the form.
Open the form.
Type 10 in txtNumberOfHours
Pick the 2008 date in cboEffectiveDate.
You should see 200 appear in txtTotalDollarAmount.
Change the date in cboEffectiveDate to the 2009 date.
You should see 300 appear in txtTotalDollarAmount.

Note that only NumberOfHours and EffectiveDate should be stored in your
table - TotalDollarAmount will be calculated whenever you display the form
(or a similar Report).

Good Luck!

:

Access 2003 issue......

My current form has a field that makes a calculation based on the value of
another field, multiplies it by a fixed number and displays the results in
currency. Basically - # of hours times an hourly rate = total dollar amount.
To accomplish this, I have the field run a macro which performs the calc.
This works just fine.

Now the hourly rate changed in 2008 and by changing my macro to reflect this
new value, I believe the change has recalculated all previous 2007 entries.
(which I don't want!)

So I thought that a possible solution would be to create separate yearly
macros reflecting the correct costing rates, write code to look at the form's
date field and then run the correct macro calculation based on that date.
I'm having a problem with the code. My VB skills are obviously lacking.

Any help would be appreciated!!!!
Click to expand...
Click to expand...
Click to expand...
 
I

iez44

Frank,

I completely understand what you've suggested. I've added the new "_Bound"
text box, named it's control source property as you stated and added the
lines to the "After Update" of the other two text boxes. However, the
"txtTotalDollarAmount_Bound" text box never gets the value from the
"txtTotalDollarAmount" calculated text box.

The calculated box still updates accordingly if I change the # of hours or
date and if I manually type in a value to the "_Bound" box, it will update
the underlying table, so I know it's properly linked.

Please note I had to use this syntax because Access would give me an error
message saying the macro couldn't be found if it typed it in exactly as you
said.

=[Forms]![frmReworkCost]![txtTotalDollarAmount_Bound]=Nz([Forms]![frmReworkCost]![txtTotalDollarAmount],0)

BTW, what is the purpose of using the Nz() function in the line of code?

Thanks,
iez44


Frank said:
You said ...

"Could it be that the control source for the form's field is now no longer
the same name as the field name in the table?"

I say ...

Exactly ... the text box is no longer "bound" to the field in the underlying
table.

So, create a new text box and call it "txtTotalDollarAmount_Bound" ... set
its ControSource property to the name of the field in your table (so it is
now "bound" to that field).

Now you have to put a value into the new field whenever the value in one of
txtNumberOfHours or txtDate changes do this by putting the following line
into the AfterUpdate event of BOTH these fields -

txtTotalDollarAmount_Bound = Nz(txtTotalDollarAmount,0)

You can't allow an operator to directly enter a value into either
txtTotalDollarAmount or txtTotalDollarAmount_Bound ... for each of these
fields set the Enabled property to "No" and the Locked property to "Yes".
When you have it working properly, set the Visible property to "No" for ONE
of these fields.

Cheers ...

iez44 said:
This solution worked perfectly to calculate and display the correct value
(based on the date) on the form. (THANK YOU!)

However, I now have a problem where this calculated field is never written
to the record in the table where this information is stored. Could it be
that the control source for the form's field is now no longer the same name
as the field name in the table? Regardless of the reason, how do I save this
calculated total in the table?





Frank said:
Try this.

Change the Combo Box to a Text Box (into which the date will be typed) and
rename it txtDate.

In tblHourlyRates change dtmDateApplicable to dtmEffectiveFrom (ie the date
a new Hourly Rate applies from).

Create a Query named qryHourlyRates with this SQL -
SELECT dtmEffectiveFrom, curHourlyRate
FROM tblHourlyRates
ORDER BY dtmEffectiveFrom DESC

Change ControlSource for txtTotalDollarAmount to this -
=IIf(Not (IsNull([txtNumberOfHours]) Or
IsNull([txtDate])),[txtNumberOfHours]*DLookUp("curHourlyRate","qryHourlyRates","dtmEffectiveFrom <= #" & Format(CDate([txtDate]),"mm/dd/yy\#")))

Cheers ...

:

You might like to try (test) this.

Create a Form with these controls -

Text Box: txtNumberOfHours

Combo Box: cboEffectiveDate

Text Box: txtTotalDollarAmount

Create a Table tblHourlyRates with two fields (dtmDateApplicable,
curHourlyRate).

Set RowSource for cboEffectiveDate -
SELECT Format(dtmDateApplicable,"ddd/mm/yy") FROM tblHourlyRates ORDER BY
dtmDateApplicable DESC

Set ControlSource for txtTotalDollarAmount -
=[txtNumberOfHours]*DLookUp("curHourlyRate","tblHourlyRates","dtmDateApplicable = #" & Format(CDate([cboEffectiveDate]),"mm/dd/yy\#"))

Enter two records in tblHourlyRates -
18/1/08 (or American 1/18/08), $20
18/1/09 (or American 1/18/09), $30

Save the form.
Open the form.
Type 10 in txtNumberOfHours
Pick the 2008 date in cboEffectiveDate.
You should see 200 appear in txtTotalDollarAmount.
Change the date in cboEffectiveDate to the 2009 date.
You should see 300 appear in txtTotalDollarAmount.

Note that only NumberOfHours and EffectiveDate should be stored in your
table - TotalDollarAmount will be calculated whenever you display the form
(or a similar Report).

Good Luck!

:

Access 2003 issue......

My current form has a field that makes a calculation based on the value of
another field, multiplies it by a fixed number and displays the results in
currency. Basically - # of hours times an hourly rate = total dollar amount.
To accomplish this, I have the field run a macro which performs the calc.
This works just fine.

Now the hourly rate changed in 2008 and by changing my macro to reflect this
new value, I believe the change has recalculated all previous 2007 entries.
(which I don't want!)

So I thought that a possible solution would be to create separate yearly
macros reflecting the correct costing rates, write code to look at the form's
date field and then run the correct macro calculation based on that date.
I'm having a problem with the code. My VB skills are obviously lacking.

Any help would be appreciated!!!!
Click to expand...
Click to expand...
Click to expand...
 
F

Frank

BTW If you type EXACTLY what I suggested (without your extra "=") you can
probably simplify your line back to what I suggested.





iez44 said:
Frank,

I completely understand what you've suggested. I've added the new "_Bound"
text box, named it's control source property as you stated and added the
lines to the "After Update" of the other two text boxes. However, the
"txtTotalDollarAmount_Bound" text box never gets the value from the
"txtTotalDollarAmount" calculated text box.

The calculated box still updates accordingly if I change the # of hours or
date and if I manually type in a value to the "_Bound" box, it will update
the underlying table, so I know it's properly linked.

Please note I had to use this syntax because Access would give me an error
message saying the macro couldn't be found if it typed it in exactly as you
said.

=[Forms]![frmReworkCost]![txtTotalDollarAmount_Bound]=Nz([Forms]![frmReworkCost]![txtTotalDollarAmount],0)

BTW, what is the purpose of using the Nz() function in the line of code?

Thanks,
iez44


Frank said:
You said ...

"Could it be that the control source for the form's field is now no longer
the same name as the field name in the table?"

I say ...

Exactly ... the text box is no longer "bound" to the field in the underlying
table.

So, create a new text box and call it "txtTotalDollarAmount_Bound" ... set
its ControSource property to the name of the field in your table (so it is
now "bound" to that field).

Now you have to put a value into the new field whenever the value in one of
txtNumberOfHours or txtDate changes do this by putting the following line
into the AfterUpdate event of BOTH these fields -

txtTotalDollarAmount_Bound = Nz(txtTotalDollarAmount,0)

You can't allow an operator to directly enter a value into either
txtTotalDollarAmount or txtTotalDollarAmount_Bound ... for each of these
fields set the Enabled property to "No" and the Locked property to "Yes".
When you have it working properly, set the Visible property to "No" for ONE
of these fields.

Cheers ...

iez44 said:
This solution worked perfectly to calculate and display the correct value
(based on the date) on the form. (THANK YOU!)

However, I now have a problem where this calculated field is never written
to the record in the table where this information is stored. Could it be
that the control source for the form's field is now no longer the same name
as the field name in the table? Regardless of the reason, how do I save this
calculated total in the table?





:

Try this.

Change the Combo Box to a Text Box (into which the date will be typed) and
rename it txtDate.

In tblHourlyRates change dtmDateApplicable to dtmEffectiveFrom (ie the date
a new Hourly Rate applies from).

Create a Query named qryHourlyRates with this SQL -
SELECT dtmEffectiveFrom, curHourlyRate
FROM tblHourlyRates
ORDER BY dtmEffectiveFrom DESC

Change ControlSource for txtTotalDollarAmount to this -
=IIf(Not (IsNull([txtNumberOfHours]) Or
IsNull([txtDate])),[txtNumberOfHours]*DLookUp("curHourlyRate","qryHourlyRates","dtmEffectiveFrom <= #" & Format(CDate([txtDate]),"mm/dd/yy\#")))

Cheers ...

:

You might like to try (test) this.

Create a Form with these controls -

Text Box: txtNumberOfHours

Combo Box: cboEffectiveDate

Text Box: txtTotalDollarAmount

Create a Table tblHourlyRates with two fields (dtmDateApplicable,
curHourlyRate).

Set RowSource for cboEffectiveDate -
SELECT Format(dtmDateApplicable,"ddd/mm/yy") FROM tblHourlyRates ORDER BY
dtmDateApplicable DESC

Set ControlSource for txtTotalDollarAmount -
=[txtNumberOfHours]*DLookUp("curHourlyRate","tblHourlyRates","dtmDateApplicable = #" & Format(CDate([cboEffectiveDate]),"mm/dd/yy\#"))

Enter two records in tblHourlyRates -
18/1/08 (or American 1/18/08), $20
18/1/09 (or American 1/18/09), $30

Save the form.
Open the form.
Type 10 in txtNumberOfHours
Pick the 2008 date in cboEffectiveDate.
You should see 200 appear in txtTotalDollarAmount.
Change the date in cboEffectiveDate to the 2009 date.
You should see 300 appear in txtTotalDollarAmount.

Note that only NumberOfHours and EffectiveDate should be stored in your
table - TotalDollarAmount will be calculated whenever you display the form
(or a similar Report).

Good Luck!

:

Access 2003 issue......

My current form has a field that makes a calculation based on the value of
another field, multiplies it by a fixed number and displays the results in
currency. Basically - # of hours times an hourly rate = total dollar amount.
To accomplish this, I have the field run a macro which performs the calc.
This works just fine.

Now the hourly rate changed in 2008 and by changing my macro to reflect this
new value, I believe the change has recalculated all previous 2007 entries.
(which I don't want!)

So I thought that a possible solution would be to create separate yearly
macros reflecting the correct costing rates, write code to look at the form's
date field and then run the correct macro calculation based on that date.
I'm having a problem with the code. My VB skills are obviously lacking.

Any help would be appreciated!!!!
Click to expand...
Click to expand...
Click to expand...
 
I

iez44

I originally typed in the code exactly as you said. When I tried it out, the
total would update correctly, but then I would get this error message, MS
Access can't find the macro 'txtTotalDollarAmount_Bound =
Nz(txtTotalDollarAmount,0)'

After some trial and error, I discovered this......

Typing the code directly into the AFTER UPDATE properties window without the
beginning '=' causes Access to think the line of code is a macro and produces
the error message.

If I use the Expression Builder to enter the code without the '=', Access
automatically adds the extra '=' after closing EB.

With the beginning '=' Access likes it. Without it, Access has problems.

Regardless, the "_Bound" field never gets the total written to it.

BTW, this was done with the simplified code. I took out the extra [Forms]!
etc etc...
And, thanks again for all of your help!!!


Frank said:
BTW If you type EXACTLY what I suggested (without your extra "=") you can
probably simplify your line back to what I suggested.





iez44 said:
Frank,

I completely understand what you've suggested. I've added the new "_Bound"
text box, named it's control source property as you stated and added the
lines to the "After Update" of the other two text boxes. However, the
"txtTotalDollarAmount_Bound" text box never gets the value from the
"txtTotalDollarAmount" calculated text box.

The calculated box still updates accordingly if I change the # of hours or
date and if I manually type in a value to the "_Bound" box, it will update
the underlying table, so I know it's properly linked.

Please note I had to use this syntax because Access would give me an error
message saying the macro couldn't be found if it typed it in exactly as you
said.

=[Forms]![frmReworkCost]![txtTotalDollarAmount_Bound]=Nz([Forms]![frmReworkCost]![txtTotalDollarAmount],0)

BTW, what is the purpose of using the Nz() function in the line of code?

Thanks,
iez44


Frank said:
You said ...

"Could it be that the control source for the form's field is now no longer
the same name as the field name in the table?"

I say ...

Exactly ... the text box is no longer "bound" to the field in the underlying
table.

So, create a new text box and call it "txtTotalDollarAmount_Bound" ... set
its ControSource property to the name of the field in your table (so it is
now "bound" to that field).

Now you have to put a value into the new field whenever the value in one of
txtNumberOfHours or txtDate changes do this by putting the following line
into the AfterUpdate event of BOTH these fields -

txtTotalDollarAmount_Bound = Nz(txtTotalDollarAmount,0)

You can't allow an operator to directly enter a value into either
txtTotalDollarAmount or txtTotalDollarAmount_Bound ... for each of these
fields set the Enabled property to "No" and the Locked property to "Yes".
When you have it working properly, set the Visible property to "No" for ONE
of these fields.

Cheers ...

:


This solution worked perfectly to calculate and display the correct value
(based on the date) on the form. (THANK YOU!)

However, I now have a problem where this calculated field is never written
to the record in the table where this information is stored. Could it be
that the control source for the form's field is now no longer the same name
as the field name in the table? Regardless of the reason, how do I save this
calculated total in the table?





:

Try this.

Change the Combo Box to a Text Box (into which the date will be typed) and
rename it txtDate.

In tblHourlyRates change dtmDateApplicable to dtmEffectiveFrom (ie the date
a new Hourly Rate applies from).

Create a Query named qryHourlyRates with this SQL -
SELECT dtmEffectiveFrom, curHourlyRate
FROM tblHourlyRates
ORDER BY dtmEffectiveFrom DESC

Change ControlSource for txtTotalDollarAmount to this -
=IIf(Not (IsNull([txtNumberOfHours]) Or
IsNull([txtDate])),[txtNumberOfHours]*DLookUp("curHourlyRate","qryHourlyRates","dtmEffectiveFrom <= #" & Format(CDate([txtDate]),"mm/dd/yy\#")))

Cheers ...

:

You might like to try (test) this.

Create a Form with these controls -

Text Box: txtNumberOfHours

Combo Box: cboEffectiveDate

Text Box: txtTotalDollarAmount

Create a Table tblHourlyRates with two fields (dtmDateApplicable,
curHourlyRate).

Set RowSource for cboEffectiveDate -
SELECT Format(dtmDateApplicable,"ddd/mm/yy") FROM tblHourlyRates ORDER BY
dtmDateApplicable DESC

Set ControlSource for txtTotalDollarAmount -
=[txtNumberOfHours]*DLookUp("curHourlyRate","tblHourlyRates","dtmDateApplicable = #" & Format(CDate([cboEffectiveDate]),"mm/dd/yy\#"))

Enter two records in tblHourlyRates -
18/1/08 (or American 1/18/08), $20
18/1/09 (or American 1/18/09), $30

Save the form.
Open the form.
Type 10 in txtNumberOfHours
Pick the 2008 date in cboEffectiveDate.
You should see 200 appear in txtTotalDollarAmount.
Change the date in cboEffectiveDate to the 2009 date.
You should see 300 appear in txtTotalDollarAmount.

Note that only NumberOfHours and EffectiveDate should be stored in your
table - TotalDollarAmount will be calculated whenever you display the form
(or a similar Report).

Good Luck!

:

Access 2003 issue......

My current form has a field that makes a calculation based on the value of
another field, multiplies it by a fixed number and displays the results in
currency. Basically - # of hours times an hourly rate = total dollar amount.
To accomplish this, I have the field run a macro which performs the calc.
This works just fine.

Now the hourly rate changed in 2008 and by changing my macro to reflect this
new value, I believe the change has recalculated all previous 2007 entries.
(which I don't want!)

So I thought that a possible solution would be to create separate yearly
macros reflecting the correct costing rates, write code to look at the form's
date field and then run the correct macro calculation based on that date.
I'm having a problem with the code. My VB skills are obviously lacking.

Any help would be appreciated!!!!
Click to expand...
Click to expand...
Click to expand...
 
F

Frank

I think I see the problem ... you're typing it into a macro field ... I
seldom use macros ...

1 select the txtTotalDollarAmount field
2 open the Properties window (Alt+Enter)
3 click the ellipses (three dots) at the end of the AfterUpdate field
4 the Visual Basic editor will open with this code visible -

Private Sub txtNumberOfHours_AfterUpdate()

End Sub

5 between the lines enter the code I suggested so it looks like this -

Private Sub txtNumberOfHours_AfterUpdate()
txtTotalDollarAmount_Bound = Nz(txtTotalDollarAmount,0)
End Sub

6 do the same for the txtDate field
7 close the editor
8 save your form
9 test again


iez44 said:
I originally typed in the code exactly as you said. When I tried it out, the
total would update correctly, but then I would get this error message, MS
Access can't find the macro 'txtTotalDollarAmount_Bound =
Nz(txtTotalDollarAmount,0)'

After some trial and error, I discovered this......

Typing the code directly into the AFTER UPDATE properties window without the
beginning '=' causes Access to think the line of code is a macro and produces
the error message.

If I use the Expression Builder to enter the code without the '=', Access
automatically adds the extra '=' after closing EB.

With the beginning '=' Access likes it. Without it, Access has problems.

Regardless, the "_Bound" field never gets the total written to it.

BTW, this was done with the simplified code. I took out the extra [Forms]!
etc etc...
And, thanks again for all of your help!!!


Frank said:
BTW If you type EXACTLY what I suggested (without your extra "=") you can
probably simplify your line back to what I suggested.





iez44 said:
Frank,

I completely understand what you've suggested. I've added the new "_Bound"
text box, named it's control source property as you stated and added the
lines to the "After Update" of the other two text boxes. However, the
"txtTotalDollarAmount_Bound" text box never gets the value from the
"txtTotalDollarAmount" calculated text box.

The calculated box still updates accordingly if I change the # of hours or
date and if I manually type in a value to the "_Bound" box, it will update
the underlying table, so I know it's properly linked.

Please note I had to use this syntax because Access would give me an error
message saying the macro couldn't be found if it typed it in exactly as you
said.

=[Forms]![frmReworkCost]![txtTotalDollarAmount_Bound]=Nz([Forms]![frmReworkCost]![txtTotalDollarAmount],0)

BTW, what is the purpose of using the Nz() function in the line of code?

Thanks,
iez44


:

You said ...

"Could it be that the control source for the form's field is now no longer
the same name as the field name in the table?"

I say ...

Exactly ... the text box is no longer "bound" to the field in the underlying
table.

So, create a new text box and call it "txtTotalDollarAmount_Bound" ... set
its ControSource property to the name of the field in your table (so it is
now "bound" to that field).

Now you have to put a value into the new field whenever the value in one of
txtNumberOfHours or txtDate changes do this by putting the following line
into the AfterUpdate event of BOTH these fields -

txtTotalDollarAmount_Bound = Nz(txtTotalDollarAmount,0)

You can't allow an operator to directly enter a value into either
txtTotalDollarAmount or txtTotalDollarAmount_Bound ... for each of these
fields set the Enabled property to "No" and the Locked property to "Yes".
When you have it working properly, set the Visible property to "No" for ONE
of these fields.

Cheers ...

:


This solution worked perfectly to calculate and display the correct value
(based on the date) on the form. (THANK YOU!)

However, I now have a problem where this calculated field is never written
to the record in the table where this information is stored. Could it be
that the control source for the form's field is now no longer the same name
as the field name in the table? Regardless of the reason, how do I save this
calculated total in the table?





:

Try this.

Change the Combo Box to a Text Box (into which the date will be typed) and
rename it txtDate.

In tblHourlyRates change dtmDateApplicable to dtmEffectiveFrom (ie the date
a new Hourly Rate applies from).

Create a Query named qryHourlyRates with this SQL -
SELECT dtmEffectiveFrom, curHourlyRate
FROM tblHourlyRates
ORDER BY dtmEffectiveFrom DESC

Change ControlSource for txtTotalDollarAmount to this -
=IIf(Not (IsNull([txtNumberOfHours]) Or
IsNull([txtDate])),[txtNumberOfHours]*DLookUp("curHourlyRate","qryHourlyRates","dtmEffectiveFrom <= #" & Format(CDate([txtDate]),"mm/dd/yy\#")))

Cheers ...

:

You might like to try (test) this.

Create a Form with these controls -

Text Box: txtNumberOfHours

Combo Box: cboEffectiveDate

Text Box: txtTotalDollarAmount

Create a Table tblHourlyRates with two fields (dtmDateApplicable,
curHourlyRate).

Set RowSource for cboEffectiveDate -
SELECT Format(dtmDateApplicable,"ddd/mm/yy") FROM tblHourlyRates ORDER BY
dtmDateApplicable DESC

Set ControlSource for txtTotalDollarAmount -
=[txtNumberOfHours]*DLookUp("curHourlyRate","tblHourlyRates","dtmDateApplicable = #" & Format(CDate([cboEffectiveDate]),"mm/dd/yy\#"))

Enter two records in tblHourlyRates -
18/1/08 (or American 1/18/08), $20
18/1/09 (or American 1/18/09), $30

Save the form.
Open the form.
Type 10 in txtNumberOfHours
Pick the 2008 date in cboEffectiveDate.
You should see 200 appear in txtTotalDollarAmount.
Change the date in cboEffectiveDate to the 2009 date.
You should see 300 appear in txtTotalDollarAmount.

Note that only NumberOfHours and EffectiveDate should be stored in your
table - TotalDollarAmount will be calculated whenever you display the form
(or a similar Report).

Good Luck!

:

Access 2003 issue......

My current form has a field that makes a calculation based on the value of
another field, multiplies it by a fixed number and displays the results in
currency. Basically - # of hours times an hourly rate = total dollar amount.
To accomplish this, I have the field run a macro which performs the calc.
This works just fine.

Now the hourly rate changed in 2008 and by changing my macro to reflect this
new value, I believe the change has recalculated all previous 2007 entries.
(which I don't want!)

So I thought that a possible solution would be to create separate yearly
macros reflecting the correct costing rates, write code to look at the form's
date field and then run the correct macro calculation based on that date.
I'm having a problem with the code. My VB skills are obviously lacking.

Any help would be appreciated!!!!
Click to expand...
Click to expand...
Click to expand...
 
F

Frank

I missed a step ... between 3 and 4 you need to select "Code Builder" in the
"Choose Builder" window which pops up when you click the ellipses.



iez44 said:
I originally typed in the code exactly as you said. When I tried it out, the
total would update correctly, but then I would get this error message, MS
Access can't find the macro 'txtTotalDollarAmount_Bound =
Nz(txtTotalDollarAmount,0)'

After some trial and error, I discovered this......

Typing the code directly into the AFTER UPDATE properties window without the
beginning '=' causes Access to think the line of code is a macro and produces
the error message.

If I use the Expression Builder to enter the code without the '=', Access
automatically adds the extra '=' after closing EB.

With the beginning '=' Access likes it. Without it, Access has problems.

Regardless, the "_Bound" field never gets the total written to it.

BTW, this was done with the simplified code. I took out the extra [Forms]!
etc etc...
And, thanks again for all of your help!!!


Frank said:
BTW If you type EXACTLY what I suggested (without your extra "=") you can
probably simplify your line back to what I suggested.





iez44 said:
Frank,

I completely understand what you've suggested. I've added the new "_Bound"
text box, named it's control source property as you stated and added the
lines to the "After Update" of the other two text boxes. However, the
"txtTotalDollarAmount_Bound" text box never gets the value from the
"txtTotalDollarAmount" calculated text box.

The calculated box still updates accordingly if I change the # of hours or
date and if I manually type in a value to the "_Bound" box, it will update
the underlying table, so I know it's properly linked.

Please note I had to use this syntax because Access would give me an error
message saying the macro couldn't be found if it typed it in exactly as you
said.

=[Forms]![frmReworkCost]![txtTotalDollarAmount_Bound]=Nz([Forms]![frmReworkCost]![txtTotalDollarAmount],0)

BTW, what is the purpose of using the Nz() function in the line of code?

Thanks,
iez44


:

You said ...

"Could it be that the control source for the form's field is now no longer
the same name as the field name in the table?"

I say ...

Exactly ... the text box is no longer "bound" to the field in the underlying
table.

So, create a new text box and call it "txtTotalDollarAmount_Bound" ... set
its ControSource property to the name of the field in your table (so it is
now "bound" to that field).

Now you have to put a value into the new field whenever the value in one of
txtNumberOfHours or txtDate changes do this by putting the following line
into the AfterUpdate event of BOTH these fields -

txtTotalDollarAmount_Bound = Nz(txtTotalDollarAmount,0)

You can't allow an operator to directly enter a value into either
txtTotalDollarAmount or txtTotalDollarAmount_Bound ... for each of these
fields set the Enabled property to "No" and the Locked property to "Yes".
When you have it working properly, set the Visible property to "No" for ONE
of these fields.

Cheers ...

:


This solution worked perfectly to calculate and display the correct value
(based on the date) on the form. (THANK YOU!)

However, I now have a problem where this calculated field is never written
to the record in the table where this information is stored. Could it be
that the control source for the form's field is now no longer the same name
as the field name in the table? Regardless of the reason, how do I save this
calculated total in the table?





:

Try this.

Change the Combo Box to a Text Box (into which the date will be typed) and
rename it txtDate.

In tblHourlyRates change dtmDateApplicable to dtmEffectiveFrom (ie the date
a new Hourly Rate applies from).

Create a Query named qryHourlyRates with this SQL -
SELECT dtmEffectiveFrom, curHourlyRate
FROM tblHourlyRates
ORDER BY dtmEffectiveFrom DESC

Change ControlSource for txtTotalDollarAmount to this -
=IIf(Not (IsNull([txtNumberOfHours]) Or
IsNull([txtDate])),[txtNumberOfHours]*DLookUp("curHourlyRate","qryHourlyRates","dtmEffectiveFrom <= #" & Format(CDate([txtDate]),"mm/dd/yy\#")))

Cheers ...

:

You might like to try (test) this.

Create a Form with these controls -

Text Box: txtNumberOfHours

Combo Box: cboEffectiveDate

Text Box: txtTotalDollarAmount

Create a Table tblHourlyRates with two fields (dtmDateApplicable,
curHourlyRate).

Set RowSource for cboEffectiveDate -
SELECT Format(dtmDateApplicable,"ddd/mm/yy") FROM tblHourlyRates ORDER BY
dtmDateApplicable DESC

Set ControlSource for txtTotalDollarAmount -
=[txtNumberOfHours]*DLookUp("curHourlyRate","tblHourlyRates","dtmDateApplicable = #" & Format(CDate([cboEffectiveDate]),"mm/dd/yy\#"))

Enter two records in tblHourlyRates -
18/1/08 (or American 1/18/08), $20
18/1/09 (or American 1/18/09), $30

Save the form.
Open the form.
Type 10 in txtNumberOfHours
Pick the 2008 date in cboEffectiveDate.
You should see 200 appear in txtTotalDollarAmount.
Change the date in cboEffectiveDate to the 2009 date.
You should see 300 appear in txtTotalDollarAmount.

Note that only NumberOfHours and EffectiveDate should be stored in your
table - TotalDollarAmount will be calculated whenever you display the form
(or a similar Report).

Good Luck!

:

Access 2003 issue......

My current form has a field that makes a calculation based on the value of
another field, multiplies it by a fixed number and displays the results in
currency. Basically - # of hours times an hourly rate = total dollar amount.
To accomplish this, I have the field run a macro which performs the calc.
This works just fine.

Now the hourly rate changed in 2008 and by changing my macro to reflect this
new value, I believe the change has recalculated all previous 2007 entries.
(which I don't want!)

So I thought that a possible solution would be to create separate yearly
macros reflecting the correct costing rates, write code to look at the form's
date field and then run the correct macro calculation based on that date.
I'm having a problem with the code. My VB skills are obviously lacking.

Any help would be appreciated!!!!
Click to expand...
Click to expand...
Click to expand...
 
F

Frank

Step 1 should be -

1 select the txtNumberOfHours field

Frank said:
I think I see the problem ... you're typing it into a macro field ... I
seldom use macros ...

1 select the txtTotalDollarAmount field
2 open the Properties window (Alt+Enter)
3 click the ellipses (three dots) at the end of the AfterUpdate field
4 the Visual Basic editor will open with this code visible -

Private Sub txtNumberOfHours_AfterUpdate()

End Sub

5 between the lines enter the code I suggested so it looks like this -

Private Sub txtNumberOfHours_AfterUpdate()
txtTotalDollarAmount_Bound = Nz(txtTotalDollarAmount,0)
End Sub

6 do the same for the txtDate field
7 close the editor
8 save your form
9 test again


iez44 said:
I originally typed in the code exactly as you said. When I tried it out, the
total would update correctly, but then I would get this error message, MS
Access can't find the macro 'txtTotalDollarAmount_Bound =
Nz(txtTotalDollarAmount,0)'

After some trial and error, I discovered this......

Typing the code directly into the AFTER UPDATE properties window without the
beginning '=' causes Access to think the line of code is a macro and produces
the error message.

If I use the Expression Builder to enter the code without the '=', Access
automatically adds the extra '=' after closing EB.

With the beginning '=' Access likes it. Without it, Access has problems.

Regardless, the "_Bound" field never gets the total written to it.

BTW, this was done with the simplified code. I took out the extra [Forms]!
etc etc...
And, thanks again for all of your help!!!


Frank said:
BTW If you type EXACTLY what I suggested (without your extra "=") you can
probably simplify your line back to what I suggested.





:

Frank,

I completely understand what you've suggested. I've added the new "_Bound"
text box, named it's control source property as you stated and added the
lines to the "After Update" of the other two text boxes. However, the
"txtTotalDollarAmount_Bound" text box never gets the value from the
"txtTotalDollarAmount" calculated text box.

The calculated box still updates accordingly if I change the # of hours or
date and if I manually type in a value to the "_Bound" box, it will update
the underlying table, so I know it's properly linked.

Please note I had to use this syntax because Access would give me an error
message saying the macro couldn't be found if it typed it in exactly as you
said.

=[Forms]![frmReworkCost]![txtTotalDollarAmount_Bound]=Nz([Forms]![frmReworkCost]![txtTotalDollarAmount],0)

BTW, what is the purpose of using the Nz() function in the line of code?

Thanks,
iez44


:

You said ...

"Could it be that the control source for the form's field is now no longer
the same name as the field name in the table?"

I say ...

Exactly ... the text box is no longer "bound" to the field in the underlying
table.

So, create a new text box and call it "txtTotalDollarAmount_Bound" ... set
its ControSource property to the name of the field in your table (so it is
now "bound" to that field).

Now you have to put a value into the new field whenever the value in one of
txtNumberOfHours or txtDate changes do this by putting the following line
into the AfterUpdate event of BOTH these fields -

txtTotalDollarAmount_Bound = Nz(txtTotalDollarAmount,0)

You can't allow an operator to directly enter a value into either
txtTotalDollarAmount or txtTotalDollarAmount_Bound ... for each of these
fields set the Enabled property to "No" and the Locked property to "Yes".
When you have it working properly, set the Visible property to "No" for ONE
of these fields.

Cheers ...

:


This solution worked perfectly to calculate and display the correct value
(based on the date) on the form. (THANK YOU!)

However, I now have a problem where this calculated field is never written
to the record in the table where this information is stored. Could it be
that the control source for the form's field is now no longer the same name
as the field name in the table? Regardless of the reason, how do I save this
calculated total in the table?





:

Try this.

Change the Combo Box to a Text Box (into which the date will be typed) and
rename it txtDate.

In tblHourlyRates change dtmDateApplicable to dtmEffectiveFrom (ie the date
a new Hourly Rate applies from).

Create a Query named qryHourlyRates with this SQL -
SELECT dtmEffectiveFrom, curHourlyRate
FROM tblHourlyRates
ORDER BY dtmEffectiveFrom DESC

Change ControlSource for txtTotalDollarAmount to this -
=IIf(Not (IsNull([txtNumberOfHours]) Or
IsNull([txtDate])),[txtNumberOfHours]*DLookUp("curHourlyRate","qryHourlyRates","dtmEffectiveFrom <= #" & Format(CDate([txtDate]),"mm/dd/yy\#")))

Cheers ...

:

You might like to try (test) this.

Create a Form with these controls -

Text Box: txtNumberOfHours

Combo Box: cboEffectiveDate

Text Box: txtTotalDollarAmount

Create a Table tblHourlyRates with two fields (dtmDateApplicable,
curHourlyRate).

Set RowSource for cboEffectiveDate -
SELECT Format(dtmDateApplicable,"ddd/mm/yy") FROM tblHourlyRates ORDER BY
dtmDateApplicable DESC

Set ControlSource for txtTotalDollarAmount -
=[txtNumberOfHours]*DLookUp("curHourlyRate","tblHourlyRates","dtmDateApplicable = #" & Format(CDate([cboEffectiveDate]),"mm/dd/yy\#"))

Enter two records in tblHourlyRates -
18/1/08 (or American 1/18/08), $20
18/1/09 (or American 1/18/09), $30

Save the form.
Open the form.
Type 10 in txtNumberOfHours
Pick the 2008 date in cboEffectiveDate.
You should see 200 appear in txtTotalDollarAmount.
Change the date in cboEffectiveDate to the 2009 date.
You should see 300 appear in txtTotalDollarAmount.

Note that only NumberOfHours and EffectiveDate should be stored in your
table - TotalDollarAmount will be calculated whenever you display the form
(or a similar Report).

Good Luck!

:

Access 2003 issue......

My current form has a field that makes a calculation based on the value of
another field, multiplies it by a fixed number and displays the results in
currency. Basically - # of hours times an hourly rate = total dollar amount.
To accomplish this, I have the field run a macro which performs the calc.
This works just fine.

Now the hourly rate changed in 2008 and by changing my macro to reflect this
new value, I believe the change has recalculated all previous 2007 entries.
(which I don't want!)

So I thought that a possible solution would be to create separate yearly
macros reflecting the correct costing rates, write code to look at the form's
date field and then run the correct macro calculation based on that date.
I'm having a problem with the code. My VB skills are obviously lacking.

Any help would be appreciated!!!!
Click to expand...
Click to expand...
Click to expand...
 
I

iez44

Well..........we almost have it with the new code!!!!

The new code allows the txtTotalDollarAmount box to be updated if the number
of hours changes. (yeah!)

The new code allows the _bound text box to be written to and the value
ultimately stored in the underlying table. (yeah!)

The new code does not allow the txtTotalDollarAmount box to be recalculated
if the date is changed to a different year. Even if I make the
txtTotalDollarAmount box the active box, it never seems to rerun that line of
code that performs the calculation. (IIf(Not (IsNull....etc etc)

Frank said:
I missed a step ... between 3 and 4 you need to select "Code Builder" in the
"Choose Builder" window which pops up when you click the ellipses.



iez44 said:
I originally typed in the code exactly as you said. When I tried it out, the
total would update correctly, but then I would get this error message, MS
Access can't find the macro 'txtTotalDollarAmount_Bound =
Nz(txtTotalDollarAmount,0)'

After some trial and error, I discovered this......

Typing the code directly into the AFTER UPDATE properties window without the
beginning '=' causes Access to think the line of code is a macro and produces
the error message.

If I use the Expression Builder to enter the code without the '=', Access
automatically adds the extra '=' after closing EB.

With the beginning '=' Access likes it. Without it, Access has problems.

Regardless, the "_Bound" field never gets the total written to it.

BTW, this was done with the simplified code. I took out the extra [Forms]!
etc etc...
And, thanks again for all of your help!!!


Frank said:
BTW If you type EXACTLY what I suggested (without your extra "=") you can
probably simplify your line back to what I suggested.





:

Frank,

I completely understand what you've suggested. I've added the new "_Bound"
text box, named it's control source property as you stated and added the
lines to the "After Update" of the other two text boxes. However, the
"txtTotalDollarAmount_Bound" text box never gets the value from the
"txtTotalDollarAmount" calculated text box.

The calculated box still updates accordingly if I change the # of hours or
date and if I manually type in a value to the "_Bound" box, it will update
the underlying table, so I know it's properly linked.

Please note I had to use this syntax because Access would give me an error
message saying the macro couldn't be found if it typed it in exactly as you
said.

=[Forms]![frmReworkCost]![txtTotalDollarAmount_Bound]=Nz([Forms]![frmReworkCost]![txtTotalDollarAmount],0)

BTW, what is the purpose of using the Nz() function in the line of code?

Thanks,
iez44


:

You said ...

"Could it be that the control source for the form's field is now no longer
the same name as the field name in the table?"

I say ...

Exactly ... the text box is no longer "bound" to the field in the underlying
table.

So, create a new text box and call it "txtTotalDollarAmount_Bound" ... set
its ControSource property to the name of the field in your table (so it is
now "bound" to that field).

Now you have to put a value into the new field whenever the value in one of
txtNumberOfHours or txtDate changes do this by putting the following line
into the AfterUpdate event of BOTH these fields -

txtTotalDollarAmount_Bound = Nz(txtTotalDollarAmount,0)

You can't allow an operator to directly enter a value into either
txtTotalDollarAmount or txtTotalDollarAmount_Bound ... for each of these
fields set the Enabled property to "No" and the Locked property to "Yes".
When you have it working properly, set the Visible property to "No" for ONE
of these fields.

Cheers ...

:


This solution worked perfectly to calculate and display the correct value
(based on the date) on the form. (THANK YOU!)

However, I now have a problem where this calculated field is never written
to the record in the table where this information is stored. Could it be
that the control source for the form's field is now no longer the same name
as the field name in the table? Regardless of the reason, how do I save this
calculated total in the table?





:

Try this.

Change the Combo Box to a Text Box (into which the date will be typed) and
rename it txtDate.

In tblHourlyRates change dtmDateApplicable to dtmEffectiveFrom (ie the date
a new Hourly Rate applies from).

Create a Query named qryHourlyRates with this SQL -
SELECT dtmEffectiveFrom, curHourlyRate
FROM tblHourlyRates
ORDER BY dtmEffectiveFrom DESC

Change ControlSource for txtTotalDollarAmount to this -
=IIf(Not (IsNull([txtNumberOfHours]) Or
IsNull([txtDate])),[txtNumberOfHours]*DLookUp("curHourlyRate","qryHourlyRates","dtmEffectiveFrom <= #" & Format(CDate([txtDate]),"mm/dd/yy\#")))

Cheers ...

:

You might like to try (test) this.

Create a Form with these controls -

Text Box: txtNumberOfHours

Combo Box: cboEffectiveDate

Text Box: txtTotalDollarAmount

Create a Table tblHourlyRates with two fields (dtmDateApplicable,
curHourlyRate).

Set RowSource for cboEffectiveDate -
SELECT Format(dtmDateApplicable,"ddd/mm/yy") FROM tblHourlyRates ORDER BY
dtmDateApplicable DESC

Set ControlSource for txtTotalDollarAmount -
=[txtNumberOfHours]*DLookUp("curHourlyRate","tblHourlyRates","dtmDateApplicable = #" & Format(CDate([cboEffectiveDate]),"mm/dd/yy\#"))

Enter two records in tblHourlyRates -
18/1/08 (or American 1/18/08), $20
18/1/09 (or American 1/18/09), $30

Save the form.
Open the form.
Type 10 in txtNumberOfHours
Pick the 2008 date in cboEffectiveDate.
You should see 200 appear in txtTotalDollarAmount.
Change the date in cboEffectiveDate to the 2009 date.
You should see 300 appear in txtTotalDollarAmount.

Note that only NumberOfHours and EffectiveDate should be stored in your
table - TotalDollarAmount will be calculated whenever you display the form
(or a similar Report).

Good Luck!

:

Access 2003 issue......

My current form has a field that makes a calculation based on the value of
another field, multiplies it by a fixed number and displays the results in
currency. Basically - # of hours times an hourly rate = total dollar amount.
To accomplish this, I have the field run a macro which performs the calc.
This works just fine.

Now the hourly rate changed in 2008 and by changing my macro to reflect this
new value, I believe the change has recalculated all previous 2007 entries.
(which I don't want!)

So I thought that a possible solution would be to create separate yearly
macros reflecting the correct costing rates, write code to look at the form's
date field and then run the correct macro calculation based on that date.
I'm having a problem with the code. My VB skills are obviously lacking.

Any help would be appreciated!!!!
Click to expand...
Click to expand...
Click to expand...
 
I

iez44

BTW, can you explain the calculation line of code? (IIf(Not (IsNull....etc
etc) I'm just not getting it! Thanks.

iez44 said:
Well..........we almost have it with the new code!!!!

The new code allows the txtTotalDollarAmount box to be updated if the number
of hours changes. (yeah!)

The new code allows the _bound text box to be written to and the value
ultimately stored in the underlying table. (yeah!)

The new code does not allow the txtTotalDollarAmount box to be recalculated
if the date is changed to a different year. Even if I make the
txtTotalDollarAmount box the active box, it never seems to rerun that line of
code that performs the calculation. (IIf(Not (IsNull....etc etc)

Frank said:
I missed a step ... between 3 and 4 you need to select "Code Builder" in the
"Choose Builder" window which pops up when you click the ellipses.



iez44 said:
I originally typed in the code exactly as you said. When I tried it out, the
total would update correctly, but then I would get this error message, MS
Access can't find the macro 'txtTotalDollarAmount_Bound =
Nz(txtTotalDollarAmount,0)'

After some trial and error, I discovered this......

Typing the code directly into the AFTER UPDATE properties window without the
beginning '=' causes Access to think the line of code is a macro and produces
the error message.

If I use the Expression Builder to enter the code without the '=', Access
automatically adds the extra '=' after closing EB.

With the beginning '=' Access likes it. Without it, Access has problems.

Regardless, the "_Bound" field never gets the total written to it.

BTW, this was done with the simplified code. I took out the extra [Forms]!
etc etc...
And, thanks again for all of your help!!!


:

BTW If you type EXACTLY what I suggested (without your extra "=") you can
probably simplify your line back to what I suggested.





:

Frank,

I completely understand what you've suggested. I've added the new "_Bound"
text box, named it's control source property as you stated and added the
lines to the "After Update" of the other two text boxes. However, the
"txtTotalDollarAmount_Bound" text box never gets the value from the
"txtTotalDollarAmount" calculated text box.

The calculated box still updates accordingly if I change the # of hours or
date and if I manually type in a value to the "_Bound" box, it will update
the underlying table, so I know it's properly linked.

Please note I had to use this syntax because Access would give me an error
message saying the macro couldn't be found if it typed it in exactly as you
said.

=[Forms]![frmReworkCost]![txtTotalDollarAmount_Bound]=Nz([Forms]![frmReworkCost]![txtTotalDollarAmount],0)

BTW, what is the purpose of using the Nz() function in the line of code?

Thanks,
iez44


:

You said ...

"Could it be that the control source for the form's field is now no longer
the same name as the field name in the table?"

I say ...

Exactly ... the text box is no longer "bound" to the field in the underlying
table.

So, create a new text box and call it "txtTotalDollarAmount_Bound" ... set
its ControSource property to the name of the field in your table (so it is
now "bound" to that field).

Now you have to put a value into the new field whenever the value in one of
txtNumberOfHours or txtDate changes do this by putting the following line
into the AfterUpdate event of BOTH these fields -

txtTotalDollarAmount_Bound = Nz(txtTotalDollarAmount,0)

You can't allow an operator to directly enter a value into either
txtTotalDollarAmount or txtTotalDollarAmount_Bound ... for each of these
fields set the Enabled property to "No" and the Locked property to "Yes".
When you have it working properly, set the Visible property to "No" for ONE
of these fields.

Cheers ...

:


This solution worked perfectly to calculate and display the correct value
(based on the date) on the form. (THANK YOU!)

However, I now have a problem where this calculated field is never written
to the record in the table where this information is stored. Could it be
that the control source for the form's field is now no longer the same name
as the field name in the table? Regardless of the reason, how do I save this
calculated total in the table?





:

Try this.

Change the Combo Box to a Text Box (into which the date will be typed) and
rename it txtDate.

In tblHourlyRates change dtmDateApplicable to dtmEffectiveFrom (ie the date
a new Hourly Rate applies from).

Create a Query named qryHourlyRates with this SQL -
SELECT dtmEffectiveFrom, curHourlyRate
FROM tblHourlyRates
ORDER BY dtmEffectiveFrom DESC

Change ControlSource for txtTotalDollarAmount to this -
=IIf(Not (IsNull([txtNumberOfHours]) Or
IsNull([txtDate])),[txtNumberOfHours]*DLookUp("curHourlyRate","qryHourlyRates","dtmEffectiveFrom <= #" & Format(CDate([txtDate]),"mm/dd/yy\#")))

Cheers ...

:

You might like to try (test) this.

Create a Form with these controls -

Text Box: txtNumberOfHours

Combo Box: cboEffectiveDate

Text Box: txtTotalDollarAmount

Create a Table tblHourlyRates with two fields (dtmDateApplicable,
curHourlyRate).

Set RowSource for cboEffectiveDate -
SELECT Format(dtmDateApplicable,"ddd/mm/yy") FROM tblHourlyRates ORDER BY
dtmDateApplicable DESC

Set ControlSource for txtTotalDollarAmount -
=[txtNumberOfHours]*DLookUp("curHourlyRate","tblHourlyRates","dtmDateApplicable = #" & Format(CDate([cboEffectiveDate]),"mm/dd/yy\#"))

Enter two records in tblHourlyRates -
18/1/08 (or American 1/18/08), $20
18/1/09 (or American 1/18/09), $30

Save the form.
Open the form.
Type 10 in txtNumberOfHours
Pick the 2008 date in cboEffectiveDate.
You should see 200 appear in txtTotalDollarAmount.
Change the date in cboEffectiveDate to the 2009 date.
You should see 300 appear in txtTotalDollarAmount.

Note that only NumberOfHours and EffectiveDate should be stored in your
table - TotalDollarAmount will be calculated whenever you display the form
(or a similar Report).

Good Luck!

:

Access 2003 issue......

My current form has a field that makes a calculation based on the value of
another field, multiplies it by a fixed number and displays the results in
currency. Basically - # of hours times an hourly rate = total dollar amount.
To accomplish this, I have the field run a macro which performs the calc.
This works just fine.

Now the hourly rate changed in 2008 and by changing my macro to reflect this
new value, I believe the change has recalculated all previous 2007 entries.
(which I don't want!)

So I thought that a possible solution would be to create separate yearly
macros reflecting the correct costing rates, write code to look at the form's
date field and then run the correct macro calculation based on that date.
I'm having a problem with the code. My VB skills are obviously lacking.

Any help would be appreciated!!!!
Click to expand...
Click to expand...
Click to expand...
 
I

iez44

I AM SUCH A MORON!! I found my mistake and the code works perfectly!!!
....but I'd still like to know about the calc line.

....and thank you again for your time, your patience and your undying loyalty
to provide Access help to bloomin' idiots like me!!! :--)

iez44 said:
BTW, can you explain the calculation line of code? (IIf(Not (IsNull....etc
etc) I'm just not getting it! Thanks.

iez44 said:
Well..........we almost have it with the new code!!!!

The new code allows the txtTotalDollarAmount box to be updated if the number
of hours changes. (yeah!)

The new code allows the _bound text box to be written to and the value
ultimately stored in the underlying table. (yeah!)

The new code does not allow the txtTotalDollarAmount box to be recalculated
if the date is changed to a different year. Even if I make the
txtTotalDollarAmount box the active box, it never seems to rerun that line of
code that performs the calculation. (IIf(Not (IsNull....etc etc)

Frank said:
I missed a step ... between 3 and 4 you need to select "Code Builder" in the
"Choose Builder" window which pops up when you click the ellipses.



:

I originally typed in the code exactly as you said. When I tried it out, the
total would update correctly, but then I would get this error message, MS
Access can't find the macro 'txtTotalDollarAmount_Bound =
Nz(txtTotalDollarAmount,0)'

After some trial and error, I discovered this......

Typing the code directly into the AFTER UPDATE properties window without the
beginning '=' causes Access to think the line of code is a macro and produces
the error message.

If I use the Expression Builder to enter the code without the '=', Access
automatically adds the extra '=' after closing EB.

With the beginning '=' Access likes it. Without it, Access has problems.

Regardless, the "_Bound" field never gets the total written to it.

BTW, this was done with the simplified code. I took out the extra [Forms]!
etc etc...
And, thanks again for all of your help!!!


:

BTW If you type EXACTLY what I suggested (without your extra "=") you can
probably simplify your line back to what I suggested.





:

Frank,

I completely understand what you've suggested. I've added the new "_Bound"
text box, named it's control source property as you stated and added the
lines to the "After Update" of the other two text boxes. However, the
"txtTotalDollarAmount_Bound" text box never gets the value from the
"txtTotalDollarAmount" calculated text box.

The calculated box still updates accordingly if I change the # of hours or
date and if I manually type in a value to the "_Bound" box, it will update
the underlying table, so I know it's properly linked.

Please note I had to use this syntax because Access would give me an error
message saying the macro couldn't be found if it typed it in exactly as you
said.

=[Forms]![frmReworkCost]![txtTotalDollarAmount_Bound]=Nz([Forms]![frmReworkCost]![txtTotalDollarAmount],0)

BTW, what is the purpose of using the Nz() function in the line of code?

Thanks,
iez44


:

You said ...

"Could it be that the control source for the form's field is now no longer
the same name as the field name in the table?"

I say ...

Exactly ... the text box is no longer "bound" to the field in the underlying
table.

So, create a new text box and call it "txtTotalDollarAmount_Bound" ... set
its ControSource property to the name of the field in your table (so it is
now "bound" to that field).

Now you have to put a value into the new field whenever the value in one of
txtNumberOfHours or txtDate changes do this by putting the following line
into the AfterUpdate event of BOTH these fields -

txtTotalDollarAmount_Bound = Nz(txtTotalDollarAmount,0)

You can't allow an operator to directly enter a value into either
txtTotalDollarAmount or txtTotalDollarAmount_Bound ... for each of these
fields set the Enabled property to "No" and the Locked property to "Yes".
When you have it working properly, set the Visible property to "No" for ONE
of these fields.

Cheers ...

:


This solution worked perfectly to calculate and display the correct value
(based on the date) on the form. (THANK YOU!)

However, I now have a problem where this calculated field is never written
to the record in the table where this information is stored. Could it be
that the control source for the form's field is now no longer the same name
as the field name in the table? Regardless of the reason, how do I save this
calculated total in the table?





:

Try this.

Change the Combo Box to a Text Box (into which the date will be typed) and
rename it txtDate.

In tblHourlyRates change dtmDateApplicable to dtmEffectiveFrom (ie the date
a new Hourly Rate applies from).

Create a Query named qryHourlyRates with this SQL -
SELECT dtmEffectiveFrom, curHourlyRate
FROM tblHourlyRates
ORDER BY dtmEffectiveFrom DESC

Change ControlSource for txtTotalDollarAmount to this -
=IIf(Not (IsNull([txtNumberOfHours]) Or
IsNull([txtDate])),[txtNumberOfHours]*DLookUp("curHourlyRate","qryHourlyRates","dtmEffectiveFrom <= #" & Format(CDate([txtDate]),"mm/dd/yy\#")))

Cheers ...

:

You might like to try (test) this.

Create a Form with these controls -

Text Box: txtNumberOfHours

Combo Box: cboEffectiveDate

Text Box: txtTotalDollarAmount

Create a Table tblHourlyRates with two fields (dtmDateApplicable,
curHourlyRate).

Set RowSource for cboEffectiveDate -
SELECT Format(dtmDateApplicable,"ddd/mm/yy") FROM tblHourlyRates ORDER BY
dtmDateApplicable DESC

Set ControlSource for txtTotalDollarAmount -
=[txtNumberOfHours]*DLookUp("curHourlyRate","tblHourlyRates","dtmDateApplicable = #" & Format(CDate([cboEffectiveDate]),"mm/dd/yy\#"))

Enter two records in tblHourlyRates -
18/1/08 (or American 1/18/08), $20
18/1/09 (or American 1/18/09), $30

Save the form.
Open the form.
Type 10 in txtNumberOfHours
Pick the 2008 date in cboEffectiveDate.
You should see 200 appear in txtTotalDollarAmount.
Change the date in cboEffectiveDate to the 2009 date.
You should see 300 appear in txtTotalDollarAmount.

Note that only NumberOfHours and EffectiveDate should be stored in your
table - TotalDollarAmount will be calculated whenever you display the form
(or a similar Report).

Good Luck!

:

Access 2003 issue......

My current form has a field that makes a calculation based on the value of
another field, multiplies it by a fixed number and displays the results in
currency. Basically - # of hours times an hourly rate = total dollar amount.
To accomplish this, I have the field run a macro which performs the calc.
This works just fine.

Now the hourly rate changed in 2008 and by changing my macro to reflect this
new value, I believe the change has recalculated all previous 2007 entries.
(which I don't want!)

So I thought that a possible solution would be to create separate yearly
macros reflecting the correct costing rates, write code to look at the form's
date field and then run the correct macro calculation based on that date.
I'm having a problem with the code. My VB skills are obviously lacking.

Any help would be appreciated!!!!
Click to expand...
Click to expand...
Click to expand...
 
F

Frank

We're all morons at times.

To understand the calculation, you might use Help in the VB editor to learn
about the various functions/operators in it -
IIf()
Not
IsNull()
Or
DLookUp()
Format()
CDate()

Glad I could help.




iez44 said:
I AM SUCH A MORON!! I found my mistake and the code works perfectly!!!
...but I'd still like to know about the calc line.

...and thank you again for your time, your patience and your undying loyalty
to provide Access help to bloomin' idiots like me!!! :--)

iez44 said:
BTW, can you explain the calculation line of code? (IIf(Not (IsNull....etc
etc) I'm just not getting it! Thanks.

iez44 said:
Well..........we almost have it with the new code!!!!

The new code allows the txtTotalDollarAmount box to be updated if the number
of hours changes. (yeah!)

The new code allows the _bound text box to be written to and the value
ultimately stored in the underlying table. (yeah!)

The new code does not allow the txtTotalDollarAmount box to be recalculated
if the date is changed to a different year. Even if I make the
txtTotalDollarAmount box the active box, it never seems to rerun that line of
code that performs the calculation. (IIf(Not (IsNull....etc etc)

:

I missed a step ... between 3 and 4 you need to select "Code Builder" in the
"Choose Builder" window which pops up when you click the ellipses.



:

I originally typed in the code exactly as you said. When I tried it out, the
total would update correctly, but then I would get this error message, MS
Access can't find the macro 'txtTotalDollarAmount_Bound =
Nz(txtTotalDollarAmount,0)'

After some trial and error, I discovered this......

Typing the code directly into the AFTER UPDATE properties window without the
beginning '=' causes Access to think the line of code is a macro and produces
the error message.

If I use the Expression Builder to enter the code without the '=', Access
automatically adds the extra '=' after closing EB.

With the beginning '=' Access likes it. Without it, Access has problems.

Regardless, the "_Bound" field never gets the total written to it.

BTW, this was done with the simplified code. I took out the extra [Forms]!
etc etc...
And, thanks again for all of your help!!!


:

BTW If you type EXACTLY what I suggested (without your extra "=") you can
probably simplify your line back to what I suggested.





:

Frank,

I completely understand what you've suggested. I've added the new "_Bound"
text box, named it's control source property as you stated and added the
lines to the "After Update" of the other two text boxes. However, the
"txtTotalDollarAmount_Bound" text box never gets the value from the
"txtTotalDollarAmount" calculated text box.

The calculated box still updates accordingly if I change the # of hours or
date and if I manually type in a value to the "_Bound" box, it will update
the underlying table, so I know it's properly linked.

Please note I had to use this syntax because Access would give me an error
message saying the macro couldn't be found if it typed it in exactly as you
said.

=[Forms]![frmReworkCost]![txtTotalDollarAmount_Bound]=Nz([Forms]![frmReworkCost]![txtTotalDollarAmount],0)

BTW, what is the purpose of using the Nz() function in the line of code?

Thanks,
iez44


:

You said ...

"Could it be that the control source for the form's field is now no longer
the same name as the field name in the table?"

I say ...

Exactly ... the text box is no longer "bound" to the field in the underlying
table.

So, create a new text box and call it "txtTotalDollarAmount_Bound" ... set
its ControSource property to the name of the field in your table (so it is
now "bound" to that field).

Now you have to put a value into the new field whenever the value in one of
txtNumberOfHours or txtDate changes do this by putting the following line
into the AfterUpdate event of BOTH these fields -

txtTotalDollarAmount_Bound = Nz(txtTotalDollarAmount,0)

You can't allow an operator to directly enter a value into either
txtTotalDollarAmount or txtTotalDollarAmount_Bound ... for each of these
fields set the Enabled property to "No" and the Locked property to "Yes".
When you have it working properly, set the Visible property to "No" for ONE
of these fields.

Cheers ...

:


This solution worked perfectly to calculate and display the correct value
(based on the date) on the form. (THANK YOU!)

However, I now have a problem where this calculated field is never written
to the record in the table where this information is stored. Could it be
that the control source for the form's field is now no longer the same name
as the field name in the table? Regardless of the reason, how do I save this
calculated total in the table?





:

Try this.

Change the Combo Box to a Text Box (into which the date will be typed) and
rename it txtDate.

In tblHourlyRates change dtmDateApplicable to dtmEffectiveFrom (ie the date
a new Hourly Rate applies from).

Create a Query named qryHourlyRates with this SQL -
SELECT dtmEffectiveFrom, curHourlyRate
FROM tblHourlyRates
ORDER BY dtmEffectiveFrom DESC

Change ControlSource for txtTotalDollarAmount to this -
=IIf(Not (IsNull([txtNumberOfHours]) Or
IsNull([txtDate])),[txtNumberOfHours]*DLookUp("curHourlyRate","qryHourlyRates","dtmEffectiveFrom <= #" & Format(CDate([txtDate]),"mm/dd/yy\#")))

Cheers ...

:

You might like to try (test) this.

Create a Form with these controls -

Text Box: txtNumberOfHours

Combo Box: cboEffectiveDate

Text Box: txtTotalDollarAmount

Create a Table tblHourlyRates with two fields (dtmDateApplicable,
curHourlyRate).

Set RowSource for cboEffectiveDate -
SELECT Format(dtmDateApplicable,"ddd/mm/yy") FROM tblHourlyRates ORDER BY
dtmDateApplicable DESC

Set ControlSource for txtTotalDollarAmount -
=[txtNumberOfHours]*DLookUp("curHourlyRate","tblHourlyRates","dtmDateApplicable = #" & Format(CDate([cboEffectiveDate]),"mm/dd/yy\#"))

Enter two records in tblHourlyRates -
18/1/08 (or American 1/18/08), $20
18/1/09 (or American 1/18/09), $30

Save the form.
Open the form.
Type 10 in txtNumberOfHours
Pick the 2008 date in cboEffectiveDate.
You should see 200 appear in txtTotalDollarAmount.
Change the date in cboEffectiveDate to the 2009 date.
You should see 300 appear in txtTotalDollarAmount.

Note that only NumberOfHours and EffectiveDate should be stored in your
table - TotalDollarAmount will be calculated whenever you display the form
(or a similar Report).

Good Luck!

:

Access 2003 issue......

My current form has a field that makes a calculation based on the value of
another field, multiplies it by a fixed number and displays the results in
currency. Basically - # of hours times an hourly rate = total dollar amount.
To accomplish this, I have the field run a macro which performs the calc.
This works just fine.

Now the hourly rate changed in 2008 and by changing my macro to reflect this
new value, I believe the change has recalculated all previous 2007 entries.
(which I don't want!)

So I thought that a possible solution would be to create separate yearly
macros reflecting the correct costing rates, write code to look at the form's
date field and then run the correct macro calculation based on that date.
I'm having a problem with the code. My VB skills are obviously lacking.

Any help would be appreciated!!!!
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

DSum 1
calculate total hours based on dates 5
Macro to calculate next rate increase in weeks 1
Time calculations for Scheduled vs Actual Employee Time and Wages 4
Time calculations for Scheduled vs Actual Employee Time and Wages 2
Time calculations for Scheduled Time vs. Actual Time Worked 1
Using field values from a table 3
Calculated Fields not Working 5

Top