An odd =Sum() question?

Here it goes. I’ve reworded this question. I have a form (“Volunteer
Database Form�) with an attached subform (“Volunteer Hours Subform�). I
would like to add a control box to my form with an expression that will do
two things. 1.) Pull its data from a calculated field labeled “Total� on
the subform. This field’s equation {=([End Time]-[Start Time])*24)} results
in the total hours worked each day. 2.) Only add up those hours worked in
the last 365 days. If it works correctly, the equation should return the
total number of hours worked by one person in the past year. Does anyone
know an expression that I can use to do this? From the Access 2003 help
menus, I think it might be some combination of the following expressions
{=Sum()} & {Date()-365}. Thank you in advance!!!!

This kind of math can be done in a query. Only add up those hours worked in
the last 365 days. You have to have a field for dates but you did not mention any.
You talk about your forms but nothing about your tables and fields. What
are the names and datatype? Post some sample data.

I do not understand your formula =([End Time]-[Start Time])*24)
Can you tell me what is the purpose of the *24 in it? Now if [End
Time]-[Start Time] resulted in hours you would divide by 24 to get days.
--
KARL DEWEY
Build a little - Test a little


"Flopbot" wrote:

Here it goes. I’ve reworded this question. I have a form (“Volunteer
Database Form�) with an attached subform (“Volunteer Hours Subform�). I
would like to add a control box to my form with an expression that will do
two things. 1.) Pull its data from a calculated field labeled “Total� on
the subform. This field’s equation {=([End Time]-[Start Time])*24)} results
in the total hours worked each day. 2.) Only add up those hours worked in
the last 365 days. If it works correctly, the equation should return the
total number of hours worked by one person in the past year. Does anyone
know an expression that I can use to do this? From the Access 2003 help
menus, I think it might be some combination of the following expressions
{=Sum()} & {Date()-365}. Thank you in advance!!!!

On Tue, 7 Oct 2008 06:49:01 -0700, Flopbot
wrote:

Here it goes. I’ve reworded this question. I have a form (“Volunteer
Database Form”) with an attached subform (“Volunteer Hours Subform”). I
would like to add a control box to my form with an expression that will do
two things. 1.) Pull its data from a calculated field labeled “Total” on
the subform. This field’s equation {=([End Time]-[Start Time])*24)} results
in the total hours worked each day. 2.) Only add up those hours worked in
the last 365 days. If it works correctly, the equation should return the
total number of hours worked by one person in the past year. Does anyone
know an expression that I can use to do this? From the Access 2003 help
menus, I think it might be some combination of the following expressions
{=Sum()} & {Date()-365}. Thank you in advance!!!!

You cannot do this using the data on the form, at least not at all easily. I'd
suggest using the DSum() function to go directly to the table, or creating a
Totals query with a criterion on the volunteer ID such as

=[Forms]![YourFormName]![YourControlName]

and on the date field of

DateAdd("yyyy", -1, Date())

--

John W. Vinson [MVP]

Thank you so much for your help!!! Sorry, I’m really new to Access so I
don’t yet know anything about queries. I was hoping that the number could be
calculated directly on the form instead of searching for in another window.
Is that what a query would make me do? My “Volunteer Hours Subform� comes
from a “Volunteer Hours Tracking Table�. Both the table and the subform have
the following fields: “Date Worked� (ex. 9/26/2008, 9/26/2003), “Start Time�
(ex. 12:00pm), “End Time� (ex. 5:00pm). On my subform I also have one extra
field “Totals�, that calculates the number of hours worked between the start
and end times (Returns: 5 hours) each day. The number 5 results from the
formula =(([End Time]-[Start Time])*24). I’m not sure why that formula
works, but it does! I’ve also used something similar in Excel with success.
The main form is called the “Volunteer Database Form� and it comes from the
“Volunteer Info Table�. Both tables are linked by a “Volunteer ID�.
Basically, from the above example, I want the expression to add up the number
of hours worked (5) over the past year (excluding 9/26/2003). Thank you
again for your help!!!

"KARL DEWEY" wrote:

This kind of math can be done in a query. Only add up those hours worked in
the last 365 days. You have to have a field for dates but you did not mention any.
You talk about your forms but nothing about your tables and fields. What
are the names and datatype? Post some sample data.

I do not understand your formula =([End Time]-[Start Time])*24)
Can you tell me what is the purpose of the *24 in it? Now if [End
Time]-[Start Time] resulted in hours you would divide by 24 to get days.
--
KARL DEWEY
Build a little - Test a little


"Flopbot" wrote:

Here it goes. I’ve reworded this question. I have a form (“Volunteer
Database Form�) with an attached subform (“Volunteer Hours Subform�). I
would like to add a control box to my form with an expression that will do
two things. 1.) Pull its data from a calculated field labeled “Total� on
the subform. This field’s equation {=([End Time]-[Start Time])*24)} results
in the total hours worked each day. 2.) Only add up those hours worked in
the last 365 days. If it works correctly, the equation should return the
total number of hours worked by one person in the past year. Does anyone
know an expression that I can use to do this? From the Access 2003 help
menus, I think it might be some combination of the following expressions
{=Sum()} & {Date()-365}. Thank you in advance!!!!

Thanks for helping me also!!! Is my thinking completely nuts on this? The
following function didn’t work, but am I close =DSum("([End Time]-[Start
Time])*24)","Volunteer Hours Tracking Table"). I used your =DSum() and was
trying to get it to add up the total hours worked each day by using the
equation ([End Time]-[Start Time])*24) in place of an actual field because I
haven’t found a way to create calculated field in a table. . .only in
subtables. My thinking is that if I can get this equation to work, then
maybe I could somehow set limits on what it adds. Queries at the moment are
beyond me. Hopefully, I’ll play with them tonight and see what I can learn.

"John W. Vinson" wrote:

On Tue, 7 Oct 2008 06:49:01 -0700, Flopbot
wrote:

Here it goes. I’ve reworded this question. I have a form (“Volunteer
Database Form�) with an attached subform (“Volunteer Hours Subform�). I
would like to add a control box to my form with an expression that will do
two things. 1.) Pull its data from a calculated field labeled “Total� on
the subform. This field’s equation {=([End Time]-[Start Time])*24)} results
in the total hours worked each day. 2.) Only add up those hours worked in
the last 365 days. If it works correctly, the equation should return the
total number of hours worked by one person in the past year. Does anyone
know an expression that I can use to do this? From the Access 2003 help
menus, I think it might be some combination of the following expressions
{=Sum()} & {Date()-365}. Thank you in advance!!!!

You cannot do this using the data on the form, at least not at all easily. I'd
suggest using the DSum() function to go directly to the table, or creating a
Totals query with a criterion on the volunteer ID such as

=[Forms]![YourFormName]![YourControlName]

and on the date field of

DateAdd("yyyy", -1, Date())

--

John W. Vinson [MVP]

Both the table and the subform have the following fields: “Date Worked�
(ex. 9/26/2008, 9/26/2003),
What is the datatype of your [Date Worked] field? If it has two dates in it
like your example then it is text.
What is the datatype
What you need is a query between the table and forms.

--
KARL DEWEY
Build a little - Test a little


"Flopbot" wrote:

Thank you so much for your help!!! Sorry, I’m really new to Access so I
don’t yet know anything about queries. I was hoping that the number could be
calculated directly on the form instead of searching for in another window.
Is that what a query would make me do? My “Volunteer Hours Subform� comes
from a “Volunteer Hours Tracking Table�. Both the table and the subform have
the following fields: “Date Worked� (ex. 9/26/2008, 9/26/2003), “Start Time�
(ex. 12:00pm), “End Time� (ex. 5:00pm). On my subform I also have one extra
field “Totals�, that calculates the number of hours worked between the start
and end times (Returns: 5 hours) each day. The number 5 results from the
formula =(([End Time]-[Start Time])*24). I’m not sure why that formula
works, but it does! I’ve also used something similar in Excel with success.
The main form is called the “Volunteer Database Form� and it comes from the
“Volunteer Info Table�. Both tables are linked by a “Volunteer ID�.
Basically, from the above example, I want the expression to add up the number
of hours worked (5) over the past year (excluding 9/26/2003). Thank you
again for your help!!!

"KARL DEWEY" wrote:

This kind of math can be done in a query. Only add up those hours worked in
the last 365 days. You have to have a field for dates but you did not mention any.
You talk about your forms but nothing about your tables and fields. What
are the names and datatype? Post some sample data.

I do not understand your formula =([End Time]-[Start Time])*24)
Can you tell me what is the purpose of the *24 in it? Now if [End
Time]-[Start Time] resulted in hours you would divide by 24 to get days.
--
KARL DEWEY
Build a little - Test a little


"Flopbot" wrote:

Here it goes. I’ve reworded this question. I have a form (“Volunteer
Database Form�) with an attached subform (“Volunteer Hours Subform�). I
would like to add a control box to my form with an expression that will do
two things. 1.) Pull its data from a calculated field labeled “Total� on
the subform. This field’s equation {=([End Time]-[Start Time])*24)} results
in the total hours worked each day. 2.) Only add up those hours worked in
the last 365 days. If it works correctly, the equation should return the
total number of hours worked by one person in the past year. Does anyone
know an expression that I can use to do this? From the Access 2003 help
menus, I think it might be some combination of the following expressions
{=Sum()} & {Date()-365}. Thank you in advance!!!!

No, it only has one date per cell. I was just trying to show that the entire
database has dates going back many years. The datatype for that field is
Date/Time. Is a query something that will allow me to show a result on my
main form or will I have to go someplace else to get that result. I'm going
to play around with queries tonight.

"KARL DEWEY" wrote:

Both the table and the subform have the following fields: “Date Worked�
(ex. 9/26/2008, 9/26/2003),
What is the datatype of your [Date Worked] field? If it has two dates in it
like your example then it is text.
What is the datatype
What you need is a query between the table and forms.

--
KARL DEWEY
Build a little - Test a little


"Flopbot" wrote:

Thank you so much for your help!!! Sorry, I’m really new to Access so I
don’t yet know anything about queries. I was hoping that the number could be
calculated directly on the form instead of searching for in another window.
Is that what a query would make me do? My “Volunteer Hours Subform� comes
from a “Volunteer Hours Tracking Table�. Both the table and the subform have
the following fields: “Date Worked� (ex. 9/26/2008, 9/26/2003), “Start Time�
(ex. 12:00pm), “End Time� (ex. 5:00pm). On my subform I also have one extra
field “Totals�, that calculates the number of hours worked between the start
and end times (Returns: 5 hours) each day. The number 5 results from the
formula =(([End Time]-[Start Time])*24). I’m not sure why that formula
works, but it does! I’ve also used something similar in Excel with success.
The main form is called the “Volunteer Database Form� and it comes from the
“Volunteer Info Table�. Both tables are linked by a “Volunteer ID�.
Basically, from the above example, I want the expression to add up the number
of hours worked (5) over the past year (excluding 9/26/2003). Thank you
again for your help!!!

"KARL DEWEY" wrote:

This kind of math can be done in a query. Only add up those hours worked in
the last 365 days. You have to have a field for dates but you did not mention any.
You talk about your forms but nothing about your tables and fields. What
are the names and datatype? Post some sample data.

I do not understand your formula =([End Time]-[Start Time])*24)
Can you tell me what is the purpose of the *24 in it? Now if [End
Time]-[Start Time] resulted in hours you would divide by 24 to get days.
--
KARL DEWEY
Build a little - Test a little


"Flopbot" wrote:

Here it goes. I’ve reworded this question. I have a form (“Volunteer
Database Form�) with an attached subform (“Volunteer Hours Subform�). I
would like to add a control box to my form with an expression that will do
two things. 1.) Pull its data from a calculated field labeled “Total� on
the subform. This field’s equation {=([End Time]-[Start Time])*24)} results
in the total hours worked each day. 2.) Only add up those hours worked in
the last 365 days. If it works correctly, the equation should return the
total number of hours worked by one person in the past year. Does anyone
know an expression that I can use to do this? From the Access 2003 help
menus, I think it might be some combination of the following expressions
{=Sum()} & {Date()-365}. Thank you in advance!!!!

Post some sample data.
--
KARL DEWEY
Build a little - Test a little


"Flopbot" wrote:

No, it only has one date per cell. I was just trying to show that the entire
database has dates going back many years. The datatype for that field is
Date/Time. Is a query something that will allow me to show a result on my
main form or will I have to go someplace else to get that result. I'm going
to play around with queries tonight.

"KARL DEWEY" wrote:

Both the table and the subform have the following fields: “Date Worked�
(ex. 9/26/2008, 9/26/2003),
What is the datatype of your [Date Worked] field? If it has two dates in it
like your example then it is text.
What is the datatype
What you need is a query between the table and forms.

--
KARL DEWEY
Build a little - Test a little


"Flopbot" wrote:

Thank you so much for your help!!! Sorry, I’m really new to Access so I
don’t yet know anything about queries. I was hoping that the number could be
calculated directly on the form instead of searching for in another window.
Is that what a query would make me do? My “Volunteer Hours Subform� comes
from a “Volunteer Hours Tracking Table�. Both the table and the subform have
the following fields: “Date Worked� (ex. 9/26/2008, 9/26/2003), “Start Time�
(ex. 12:00pm), “End Time� (ex. 5:00pm). On my subform I also have one extra
field “Totals�, that calculates the number of hours worked between the start
and end times (Returns: 5 hours) each day. The number 5 results from the
formula =(([End Time]-[Start Time])*24). I’m not sure why that formula
works, but it does! I’ve also used something similar in Excel with success.
The main form is called the “Volunteer Database Form� and it comes from the
“Volunteer Info Table�. Both tables are linked by a “Volunteer ID�.
Basically, from the above example, I want the expression to add up the number
of hours worked (5) over the past year (excluding 9/26/2003). Thank you
again for your help!!!

"KARL DEWEY" wrote:

This kind of math can be done in a query. Only add up those hours worked in
the last 365 days. You have to have a field for dates but you did not mention any.
You talk about your forms but nothing about your tables and fields. What
are the names and datatype? Post some sample data.

I do not understand your formula =([End Time]-[Start Time])*24)
Can you tell me what is the purpose of the *24 in it? Now if [End
Time]-[Start Time] resulted in hours you would divide by 24 to get days.
--
KARL DEWEY
Build a little - Test a little


"Flopbot" wrote:

Here it goes. I’ve reworded this question. I have a form (“Volunteer
Database Form�) with an attached subform (“Volunteer Hours Subform�). I
would like to add a control box to my form with an expression that will do
two things. 1.) Pull its data from a calculated field labeled “Total� on
the subform. This field’s equation {=([End Time]-[Start Time])*24)} results
in the total hours worked each day. 2.) Only add up those hours worked in
the last 365 days. If it works correctly, the equation should return the
total number of hours worked by one person in the past year. Does anyone
know an expression that I can use to do this? From the Access 2003 help
menus, I think it might be some combination of the following expressions
{=Sum()} & {Date()-365}. Thank you in advance!!!!

On Tue, 7 Oct 2008 11:03:03 -0700, Flopbot
wrote:

Thanks for helping me also!!! Is my thinking completely nuts on this? The
following function didn’t work, but am I close =DSum("([End Time]-[Start
Time])*24)","Volunteer Hours Tracking Table"). I used your =DSum() and was
trying to get it to add up the total hours worked each day by using the
equation ([End Time]-[Start Time])*24) in place of an actual field because I
haven’t found a way to create calculated field in a table. . .only in
subtables. My thinking is that if I can get this equation to work, then
maybe I could somehow set limits on what it adds. Queries at the moment are
beyond me. Hopefully, I’ll play with them tonight and see what I can learn.

Queries are *ABSOLUTELY BASIC* to any productive use of Access. If you can't
do queries you can't use Access!

If the End Time and Start Time are Date/Time fields, then you can use the
DateDiff() function to calculate the number of hours. Assuming that you want
hours and fractions of hours (e.g. if someone worked 4 hours 15 minutes you
want 4.25) then calculate the time in minutes and divide.

Your DSum() will need a third operand to limit the sum to this particular
volunteer. Not knowing anything about your table structure I'll have to make a
wild guess: maybe something like

=DSum("DateDiff(""n"",[Start Time],[End Time])/60)","[Volunteer Hours Tracking
Table"], "VolunteerID = " & [VolunteerID] & " AND [datefield] =
DateAdd(""yyyy"", -1, Date())

--

John W. Vinson [MVP]

Not a good idea to calculate fields within a table if the raw data is
already there and can be displayed as calculated in queries and reports.

You need to filter the results you want to be displayed first which would be
the records for people that have worked within the last year. Once you have
the recordset you are looking for then you can build a form or whatever
based on the filtered results. Build the query first and go from there.

"Flopbot" wrote in message
...
Thanks for helping me also!!! Is my thinking completely nuts on this?
The
following function didn't work, but am I close =DSum("([End Time]-[Start
Time])*24)","Volunteer Hours Tracking Table"). I used your =DSum() and
was
trying to get it to add up the total hours worked each day by using the
equation ([End Time]-[Start Time])*24) in place of an actual field because
I
haven't found a way to create calculated field in a table. . .only in
subtables. My thinking is that if I can get this equation to work, then
maybe I could somehow set limits on what it adds. Queries at the moment
are
beyond me. Hopefully, I'll play with them tonight and see what I can
learn.

"John W. Vinson" wrote:

On Tue, 7 Oct 2008 06:49:01 -0700, Flopbot

wrote:

Here it goes. I've reworded this question. I have a form ("Volunteer
Database Form") with an attached subform ("Volunteer Hours Subform"). I
would like to add a control box to my form with an expression that will
do
two things. 1.) Pull its data from a calculated field labeled "Total"
on
the subform. This field's equation {=([End Time]-[Start Time])*24)}
results
in the total hours worked each day. 2.) Only add up those hours worked
in
the last 365 days. If it works correctly, the equation should return
the
total number of hours worked by one person in the past year. Does
anyone
know an expression that I can use to do this? From the Access 2003 help
menus, I think it might be some combination of the following expressions
{=Sum()} & {Date()-365}. Thank you in advance!!!!

You cannot do this using the data on the form, at least not at all
easily. I'd
suggest using the DSum() function to go directly to the table, or
creating a
Totals query with a criterion on the volunteer ID such as

=[Forms]![YourFormName]![YourControlName]

and on the date field of

DateAdd("yyyy", -1, Date())

--

John W. Vinson [MVP]