G
Guest
I have a control on a form which holds a calculated value. How can I take this value and update a table with it
tia
jeff
tia
jeff
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
W
Wayne Morgan
How you do it will depend on what causes the calculated field to change.
However, why do you want to store it. It is bad standardization to store
calculated values except in a few specific circumstances. It is better just
to calculate the value whenever you need it.
--
Wayne Morgan
Microsoft Access MVP
However, why do you want to store it. It is bad standardization to store
calculated values except in a few specific circumstances. It is better just
to calculate the value whenever you need it.
--
Wayne Morgan
Microsoft Access MVP
this value and update a table with it?JMorrell said:I have a control on a form which holds a calculated value. How can I take
J
John Vinson
I have a control on a form which holds a calculated value. How can I take this value and update a table with it?
tia,
jeff
Well... ordinarily one would not want to do so.
Storing derived data such as this in your table accomplishes
three things: it wastes disk space; it wastes time (almost
any calculation will be MUCH faster than a disk fetch); and
most importantly, it risks data corruption. If one of the
underlying fields is subsequently edited, you will have data
in your table WHICH IS WRONG, and no automatic way to detect
that fact.
Just redo the calculation whenever you need it, either as a
calculated field in a Query or just as you're now doing it -
in the control source of a Form or a Report textbox.
If you REALLY feel that you need to store it (perhaps as a
point-in-time where the underlying values will change but you need the
current value kept), use the Form's BeforeUpdate event. Have a second
control bound to the table field and use code like
Me!txtBoundControl = Me!txtCalcControl
W
Wayne Morgan
You should be able to operate from a "base value" then go through all of the
accrued and used leave to arrive at a balance.
As John mentioned, if the calculated value changes when you change other
fields on the form, you can simply have a hidden, bound control and update
it to the calculated control's value in the form's BeforeUpdate event.
The linked worked, thanks.
accrued and used leave to arrive at a balance.
As John mentioned, if the calculated value changes when you change other
fields on the form, you can simply have a hidden, bound control and update
it to the calculated control's value in the form's BeforeUpdate event.
The linked worked, thanks.
G
Guest
----- John Vinson wrote: ----
On Mon, 1 Mar 2004 11:41:05 -0800, "JMorrell
Well... ordinarily one would not want to do so
Storing derived data such as this in your table accomplishes
three things: it wastes disk space; it wastes time (almost
any calculation will be MUCH faster than a disk fetch); and
most importantly, it risks data corruption. If one of the
underlying fields is subsequently edited, you will have data
in your table WHICH IS WRONG, and no automatic way to detect
that fact
Just redo the calculation whenever you need it, either as a
calculated field in a Query or just as you're now doing it
in the control source of a Form or a Report textbox
If you REALLY feel that you need to store it (perhaps as
point-in-time where the underlying values will change but you need th
current value kept), use the Form's BeforeUpdate event. Have a secon
control bound to the table field and use code lik
Me!txtBoundControl = Me!txtCalcContro
John W. Vinson[MVP]
Come for live chats every Tuesday and Thursday
http://go.compuserve.com/msdevapps?loc=us&access=publi
OK! You've all convinced me.
Now on to my next situation. I have a report, which is fed by a query. On the form I have a text box whos control source is =IIf([leavetype]="S","([compsickbal]=[compsickbal]-[leavehours])",[tblleavebal.sickbal]). I can rename as needed, but if leavetype = "S", how can I calculate compsickbal based on a value of itself
tia
Jeff
On Mon, 1 Mar 2004 11:41:05 -0800, "JMorrell
I have a control on a form which holds a calculated value. How can I take this value and update a table with it
jef
Well... ordinarily one would not want to do so
Storing derived data such as this in your table accomplishes
three things: it wastes disk space; it wastes time (almost
any calculation will be MUCH faster than a disk fetch); and
most importantly, it risks data corruption. If one of the
underlying fields is subsequently edited, you will have data
in your table WHICH IS WRONG, and no automatic way to detect
that fact
Just redo the calculation whenever you need it, either as a
calculated field in a Query or just as you're now doing it
in the control source of a Form or a Report textbox
If you REALLY feel that you need to store it (perhaps as
point-in-time where the underlying values will change but you need th
current value kept), use the Form's BeforeUpdate event. Have a secon
control bound to the table field and use code lik
Me!txtBoundControl = Me!txtCalcContro
John W. Vinson[MVP]
Come for live chats every Tuesday and Thursday
http://go.compuserve.com/msdevapps?loc=us&access=publi
OK! You've all convinced me.
Now on to my next situation. I have a report, which is fed by a query. On the form I have a text box whos control source is =IIf([leavetype]="S","([compsickbal]=[compsickbal]-[leavehours])",[tblleavebal.sickbal]). I can rename as needed, but if leavetype = "S", how can I calculate compsickbal based on a value of itself
tia
Jeff
J
John Vinson
Now on to my next situation. I have a report, which is fed by a query. On the form I have a text box whos control source is =IIf([leavetype]="S","([compsickbal]=[compsickbal]-[leavehours])",[tblleavebal.sickbal]). I can rename as needed, but if leavetype = "S", how can I calculate compsickbal based on a value of itself?
You don't.
Instead, put an expression in a vacant Field cell in a Query:
ShowSickbal: IIF([Leavetype] = "S", [compsickbal]-[leavehours],
[tblleavebal].[sickbal])
assuming that compsickbal is another field in the query. If it's not,
replace compsickbal with sickbal.
G
Guest
You guys don't get paid enough, you really don't. This iif statement, now that it's in the query, works exactly like it should. I have to wonder though, how am I going to "capture" a month-end balance of leave hours.
J
John Vinson
You guys don't get paid enough, you really don't.
Since we all work here on an unpaid volunteer basis, I'd have to agree
with you... said:This iif statement, now that it's in the query, works exactly like it should.
I have to wonder though, how am I going to "capture" a month-end balance of leave hours.
With a Totals query using a criterion like
= DateSerial(Year(Date()), Month(Date()) - 1, 1) AND < DateSerial(Year(Date()), Month(Date()), 1)
on the date field to capture all records during the previous month.
G
Guest
I was a little premature on my assessment of the iif statement. It isn't working quite right on my report. The problem is when there is a gap (I think) in leave occurances
Here's an example using real numbers from my tables
if I have a beginning sick balance of 316 hours
and on 1/2/01 I take 1 hour sick time, I get a balance of 315. OK so far
I then take several hours of Annual leave over the next few months
Then I take another 5 yours of sick time off on 2/4/04
I should have a balance of 310 hours. According to this iif statement I now have a balance of 311 hours. It took that 5 hours and subtracted it from the original 316 hours, not the already calculated value of 315 hours. This is one reason why I was wanting to hold on to a calculated value. I think it would also help in adding any accrued leave hours at the end of each month. Am I asking Access to do more than it's supposed to do
Jef
Here's an example using real numbers from my tables
if I have a beginning sick balance of 316 hours
and on 1/2/01 I take 1 hour sick time, I get a balance of 315. OK so far
I then take several hours of Annual leave over the next few months
Then I take another 5 yours of sick time off on 2/4/04
I should have a balance of 310 hours. According to this iif statement I now have a balance of 311 hours. It took that 5 hours and subtracted it from the original 316 hours, not the already calculated value of 315 hours. This is one reason why I was wanting to hold on to a calculated value. I think it would also help in adding any accrued leave hours at the end of each month. Am I asking Access to do more than it's supposed to do
Jef
J
John Vinson
I should have a balance of 310 hours. According to this iif statement I now have a balance of 311 hours. It took that 5 hours and subtracted it from the original 316 hours, not the already calculated value of 315 hours. This is one reason why I was wanting to hold on to a calculated value. I think it would also help in adding any accrued leave hours at the end of each month. Am I asking Access to do more than it's supposed to do?
Well, without seeing your query and perhaps delving deeper into your
table design and query than I'm up to right now (I'm fighting a nasty
cold... don't touch the screen!), I'm not sure why. A single DLookUp
will certainly get only a single leave episode; a DSum() would be
needed to add up multiple ones.
You *can* store the time in the table if you want, and it's
occasionally worthwhile to denormalize just to keep queries from
getting too snarky to work (or to understand). In order to save the
calculated value you'll need to use the BeforeUpdate event of your
Form; copy the calculated control's value into a bound control. Just
be aware that you'll be storing data WHICH MAY WELL BE WRONG - it
would be perfectly possible to have a sick leave from 3/1/04 through
3/15/04 and 2 in the Hours Of Leave field if one or other of the
fields were to be edited.
G
Guest
----- John Vinson wrote: ----
On Mon, 8 Mar 2004 12:41:05 -0800, "JMorrell
Well, without seeing your query and perhaps delving deeper into you
table design and query than I'm up to right now (I'm fighting a nast
cold... don't touch the screen!), I'm not sure why. A single DLookU
will certainly get only a single leave episode; a DSum() would b
needed to add up multiple ones
You *can* store the time in the table if you want, and it'
occasionally worthwhile to denormalize just to keep queries fro
getting too snarky to work (or to understand). In order to save th
calculated value you'll need to use the BeforeUpdate event of you
Form; copy the calculated control's value into a bound control. Jus
be aware that you'll be storing data WHICH MAY WELL BE WRONG - i
would be perfectly possible to have a sick leave from 3/1/04 throug
3/15/04 and 2 in the Hours Of Leave field if one or other of th
fields were to be edited
John W. Vinson[MVP]
Come for live chats every Tuesday and Thursday
http://go.compuserve.com/msdevapps?loc=us&access=publi
I wash my hands often when online, I've heard about those computer virus that are transmitted. I hope you're feeling better by now
Here is the sql for my report which gives erroneous data
SELECT TblEmpLeave.LeaveID, TblEmpLeave.EmpID, TblEmpLeave.LeaveDate, TblEmpLeave.LeaveType, TblEmpLeave.LeaveHours, TblEmp.SSN, TblEmp.EmployeeID, TblEmp.Lname, TblEmp.Fname, TblEmp.CC, TblEmp.Title, TblEmp.SDate, TblEmp.SAccrue, TblEmp.AAccrue, TblEmp.SupervisorID, TblEmp.Note, TblEmpLeave.LeaveText, TblEmpLeave.EntryDate, TblEmpLeave.SickBal, TblEmpLeave.AnnBal, TblEmpLeave.CompBal, TblLeaveBal.AnnBal, TblLeaveBal.SickBal,
IIf([tblEmpLeave.leavetype]="S",[tblLeaveBal.SickBal]-[tblEmpLeave.LeaveHours],"") AS ShowSickBal, IIf([tblEmpLeave.leavetype]="A",[tblLeaveBal.AnnBal]-[tblEmpLeave.LeaveHours],"") AS ShowAnnBal, IIf([tblEmpLeave.leavetype]="C",[tblLeaveBal.CompBal]-[tblEmpLeave.LeaveHours],"") AS ShowCompBa
FROM (TblEmpLeave RIGHT JOIN TblEmp ON TblEmpLeave.EmpID = TblEmp.SSN) INNER JOIN TblLeaveBal ON TblEmp.SSN = TblLeaveBal.SS
WHERE (((TblEmpLeave.LeaveType)="s")
ORDER BY TblEmpLeave.LeaveDate, TblEmpLeave.LeaveType
The LeaveType is "S" just for testing purposes. The 3 retrieved records for a single employee (abbreviated for our discussion) are
LeaveDate 1-feb-01, LeaveHours 1, SickBal 316, ShowSickBal 31
LeaveDate 19-jan-02, LeaveHours 10, SickBal 316, ShowSickBal 30
LeaveDate 5-feb-04, LeaveHours 5, SickBal 316, ShowSickBal 31
As you can see, the math in the iif statement doesn't make any sense. I should also mention that this assignment/project is for someone who is entering all the data by hand into a ledger pad, on paper, with pencil, for about 140 people, every month. Then transfered to a Word document, by hand. The balances aren't intended to be "dead on" because they eventually go to someone else for the final departmental varification (which is done by hand, I might add)
I'd like to get this "scope creep" project put to bed once and for all. Let me know what other information you need from me, and as usual, thank you in advance
JM
On Mon, 8 Mar 2004 12:41:05 -0800, "JMorrell
I should have a balance of 310 hours. According to this iif statement I now have a balance of 311 hours. It took that 5 hours and subtracted it from the original 316 hours, not the already calculated value of 315 hours. This is one reason why I was wanting to hold on to a calculated value. I think it would also help in adding any accrued leave hours at the end of each month. Am I asking Access to do more than it's supposed to do
Well, without seeing your query and perhaps delving deeper into you
table design and query than I'm up to right now (I'm fighting a nast
cold... don't touch the screen!), I'm not sure why. A single DLookU
will certainly get only a single leave episode; a DSum() would b
needed to add up multiple ones
You *can* store the time in the table if you want, and it'
occasionally worthwhile to denormalize just to keep queries fro
getting too snarky to work (or to understand). In order to save th
calculated value you'll need to use the BeforeUpdate event of you
Form; copy the calculated control's value into a bound control. Jus
be aware that you'll be storing data WHICH MAY WELL BE WRONG - i
would be perfectly possible to have a sick leave from 3/1/04 throug
3/15/04 and 2 in the Hours Of Leave field if one or other of th
fields were to be edited
John W. Vinson[MVP]
Come for live chats every Tuesday and Thursday
http://go.compuserve.com/msdevapps?loc=us&access=publi
I wash my hands often when online, I've heard about those computer virus that are transmitted. I hope you're feeling better by now
Here is the sql for my report which gives erroneous data
SELECT TblEmpLeave.LeaveID, TblEmpLeave.EmpID, TblEmpLeave.LeaveDate, TblEmpLeave.LeaveType, TblEmpLeave.LeaveHours, TblEmp.SSN, TblEmp.EmployeeID, TblEmp.Lname, TblEmp.Fname, TblEmp.CC, TblEmp.Title, TblEmp.SDate, TblEmp.SAccrue, TblEmp.AAccrue, TblEmp.SupervisorID, TblEmp.Note, TblEmpLeave.LeaveText, TblEmpLeave.EntryDate, TblEmpLeave.SickBal, TblEmpLeave.AnnBal, TblEmpLeave.CompBal, TblLeaveBal.AnnBal, TblLeaveBal.SickBal,
IIf([tblEmpLeave.leavetype]="S",[tblLeaveBal.SickBal]-[tblEmpLeave.LeaveHours],"") AS ShowSickBal, IIf([tblEmpLeave.leavetype]="A",[tblLeaveBal.AnnBal]-[tblEmpLeave.LeaveHours],"") AS ShowAnnBal, IIf([tblEmpLeave.leavetype]="C",[tblLeaveBal.CompBal]-[tblEmpLeave.LeaveHours],"") AS ShowCompBa
FROM (TblEmpLeave RIGHT JOIN TblEmp ON TblEmpLeave.EmpID = TblEmp.SSN) INNER JOIN TblLeaveBal ON TblEmp.SSN = TblLeaveBal.SS
WHERE (((TblEmpLeave.LeaveType)="s")
ORDER BY TblEmpLeave.LeaveDate, TblEmpLeave.LeaveType
The LeaveType is "S" just for testing purposes. The 3 retrieved records for a single employee (abbreviated for our discussion) are
LeaveDate 1-feb-01, LeaveHours 1, SickBal 316, ShowSickBal 31
LeaveDate 19-jan-02, LeaveHours 10, SickBal 316, ShowSickBal 30
LeaveDate 5-feb-04, LeaveHours 5, SickBal 316, ShowSickBal 31
As you can see, the math in the iif statement doesn't make any sense. I should also mention that this assignment/project is for someone who is entering all the data by hand into a ledger pad, on paper, with pencil, for about 140 people, every month. Then transfered to a Word document, by hand. The balances aren't intended to be "dead on" because they eventually go to someone else for the final departmental varification (which is done by hand, I might add)
I'd like to get this "scope creep" project put to bed once and for all. Let me know what other information you need from me, and as usual, thank you in advance
JM