CALCULATED VALUES FROM CURRENT VALUES IN AN ENTRY FORM

[Thorfinn]

We are trying to calculate values in an entry form, based on input values of
the current record after it is "accepted."

The entry form is based on a query that connects data from various tables.
The data from the tables is input to the form (and the appropriate
associate-table) via combo box. The entries are:

-- Weekending (a date that is always a Wednesday),
-- Resource Name (a person's name),
-- Project Name (the task they are working on),
-- Week Hours Worked Qty (the number of hours worked by that person on the
selected project for the selected week)
-- Comment: Describes what the person accomplished that week.

We have been (more or less) successful in calculating the work hours
quantity for the entire group, using the expression shown below.

CurrentWeeklyGroupTotalHours:
=DSum("[Week Hours Worked Qty]","MasterFactTable","[Weekending] between
date() and date()-7")

(There are probably better ways to do the above; but the answer to that will
be obvious once the other questions are answered.)

What we haven't been able to figure out is how to get the cummulative total
for the week for a person, or the cumulative total for the week for a
specific project.

Again, the above calculations are intended to display on the right side of
the entry form, in real time, while the user enters the basic data described
above in the user fields on the left side of the form.

Please be gently basic, as we are not VB programmers, nor do we really
understand macros, Expressions, etc.

Thanks in advance.

 

[golfinray]

I would try using Datediff.
Try hours worked:datediff("d",[start],[end]) and then divide by 8. Or if you
need it in hours try datediff("h",[start],[end])

 

[Thorfinn]

Golfinray:

Thanks for answering back quickly.

Unfortunately, this did not provide assistance on how to specify the values
in a current record (current person or current project, as well as current
date) to add hours related to that person or project for that week.

It did provide some thoughts on how to do the dates differently, but on
attempting their use, I either got an improper syntax message, or the system
insisted on reverting back to the original coding.

I'm not sure how I would use datediff as expressed -- and probably, the
ignorance is mine.

It appears that the [Start] value would be [Weekending]-7, and the [End]
value would be [Weekending]. But again, that didn't work for me/us.

Thanks again
--
Thorfinn

I would try using Datediff.
Try hours worked:datediff("d",[start],[end]) and then divide by 8. Or if you
need it in hours try datediff("h",[start],[end])

--
Milton Purdy
ACCESS
State of Arkansas

We are trying to calculate values in an entry form, based on input values of
the current record after it is "accepted."

The entry form is based on a query that connects data from various tables.
The data from the tables is input to the form (and the appropriate
associate-table) via combo box. The entries are:

-- Weekending (a date that is always a Wednesday),
-- Resource Name (a person's name),
-- Project Name (the task they are working on),
-- Week Hours Worked Qty (the number of hours worked by that person on the
selected project for the selected week)
-- Comment: Describes what the person accomplished that week.

We have been (more or less) successful in calculating the work hours
quantity for the entire group, using the expression shown below.

CurrentWeeklyGroupTotalHours:
=DSum("[Week Hours Worked Qty]","MasterFactTable","[Weekending] between
date() and date()-7")

(There are probably better ways to do the above; but the answer to that will
be obvious once the other questions are answered.)

What we haven't been able to figure out is how to get the cummulative total
for the week for a person, or the cumulative total for the week for a
specific project.

Again, the above calculations are intended to display on the right side of
the entry form, in real time, while the user enters the basic data described
above in the user fields on the left side of the form.

Please be gently basic, as we are not VB programmers, nor do we really
understand macros, Expressions, etc.

Thanks in advance.