A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Using Forms
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Calculate Total Hours Worked For Week Based On Check Boxes.



 
 
Thread Tools Display Modes
  #1  
Old August 28th, 2008, 11:46 PM posted to microsoft.public.access.forms
Pablito
external usenet poster
 
Posts: 6
Default Calculate Total Hours Worked For Week Based On Check Boxes.

Hi,
I created a Scheduling database. On the scheduling form I have fields for
"the time the person started work", "the time the person ended work", "total
hours for the week" and check boxes for "sun", "mon", "tues", "wed", "thur",
"fri" and "sat". I would like the program to automaticly calculate the total
hours worked for the week based on the amount of check boxes checked. I have
included some of the code I have tried.

=Sum(IIf([SUN]=Yes Or [MON]=Yes Or [TUE]=Yes Or [WED]=Yes Or [THU]=Yes Or
[FRI]=Yes Or [SAT]=Yes,DateDiff("n",[START_TIME],[END_TIME])/60))

Any help with this wil be greatly appreciated!

Thank you,
Paul
  #2  
Old August 29th, 2008, 12:37 AM posted to microsoft.public.access.forms
Jeff Boyce
external usenet poster
 
Posts: 8,621
Default Calculate Total Hours Worked For Week Based On Check Boxes.

?you have fields named [SUN] and [MON] and ...?

This sounds more like a spreadsheet than a relational database.

You say you've tried the expression below. You don't say what happens when
you try it. Are you getting an error message? Are you getting incorrect
answers?

How do you enter information about a person who starts (and ends) at
different times on different days of the week?

It all starts with the (underlying) data, and I don't have a very clear
picture of that yet...

Regards

Jeff Boyce
Microsoft Office/Access MVP

"Pablito" wrote in message
...
Hi,
I created a Scheduling database. On the scheduling form I have fields for
"the time the person started work", "the time the person ended work",
"total
hours for the week" and check boxes for "sun", "mon", "tues", "wed",
"thur",
"fri" and "sat". I would like the program to automaticly calculate the
total
hours worked for the week based on the amount of check boxes checked. I
have
included some of the code I have tried.

=Sum(IIf([SUN]=Yes Or [MON]=Yes Or [TUE]=Yes Or [WED]=Yes Or [THU]=Yes Or
[FRI]=Yes Or [SAT]=Yes,DateDiff("n",[START_TIME],[END_TIME])/60))

Any help with this wil be greatly appreciated!

Thank you,
Paul



  #3  
Old August 29th, 2008, 05:02 AM posted to microsoft.public.access.forms
Pablito
external usenet poster
 
Posts: 6
Default Calculate Total Hours Worked For Week Based On Check Boxes.

Hi Jeff,

Thanks for responding! Actually [SUN], [Mon], [TUE], [Wed], [THU], [FRI]
and [Sat] are check boxes. I have seven check boxes. One for each day of
the week. The way I have the form set up is as a subform on the main form:

Start Time End Time Total Hours For Week Sun Mon Tue Wed Thu
Fri Sat
8:00 AM 2:00 PM 18 X X X
8:00 AM 4:00 PM 24
X X X

What I want the program to do is find the difference between the start time
and the end time: 8:00 AM to 2:00 PM is 6 hours than multiply that by the
number of check boxes that are checked, in this example it's: 3, that would
give us 18 hours for that part of the schedule.

When I used the Whole expression below nothing happened. The field for
"total hours worked for week was blank even after checking a check box. But
when I used this portion aloneateDiff("n",[START_TIME],[END_TIME])/60)) it
gave me the difference in start time and end time.

=Sum(IIf([SUN]=Yes Or [MON]=Yes Or [TUE]=Yes Or [WED]=Yes Or [THU]=Yes Or
[FRI]=Yes Or [SAT]=Yes,DateDiff("n",[START_TIME],[END_TIME])/60))

Please let me know if you need any more info. Again, I really appreciate
your time and effort with this!

Thanks,
Paul


"Jeff Boyce" wrote:

?you have fields named [SUN] and [MON] and ...?

This sounds more like a spreadsheet than a relational database.

You say you've tried the expression below. You don't say what happens when
you try it. Are you getting an error message? Are you getting incorrect
answers?

How do you enter information about a person who starts (and ends) at
different times on different days of the week?

It all starts with the (underlying) data, and I don't have a very clear
picture of that yet...

Regards

Jeff Boyce
Microsoft Office/Access MVP

"Pablito" wrote in message
...
Hi,
I created a Scheduling database. On the scheduling form I have fields for
"the time the person started work", "the time the person ended work",
"total
hours for the week" and check boxes for "sun", "mon", "tues", "wed",
"thur",
"fri" and "sat". I would like the program to automaticly calculate the
total
hours worked for the week based on the amount of check boxes checked. I
have
included some of the code I have tried.

=Sum(IIf([SUN]=Yes Or [MON]=Yes Or [TUE]=Yes Or [WED]=Yes Or [THU]=Yes Or
[FRI]=Yes Or [SAT]=Yes,DateDiff("n",[START_TIME],[END_TIME])/60))

Any help with this wil be greatly appreciated!

Thank you,
Paul




  #4  
Old August 29th, 2008, 04:10 PM posted to microsoft.public.access.forms
Jeff Boyce
external usenet poster
 
Posts: 8,621
Default Calculate Total Hours Worked For Week Based On Check Boxes.

Pablito

I still don't have a clear picture of your data, and it all starts with the
data...

If you want to know how many check boxes are checked, add them. You have
zeros for unchecked boxes and -1 for checked boxes in Access/JET. After you
get a total, use the Abs() function to find the positive value.

Note: if any of the checkboxes could be Null, use Nz([YourControl],0) to
convert Null checkboxes to 0.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP


"Pablito" wrote in message
...
Hi Jeff,

Thanks for responding! Actually [SUN], [Mon], [TUE], [Wed], [THU], [FRI]
and [Sat] are check boxes. I have seven check boxes. One for each day of
the week. The way I have the form set up is as a subform on the main
form:

Start Time End Time Total Hours For Week Sun Mon Tue Wed Thu
Fri Sat
8:00 AM 2:00 PM 18 X X X
8:00 AM 4:00 PM 24
X X X

What I want the program to do is find the difference between the start
time
and the end time: 8:00 AM to 2:00 PM is 6 hours than multiply that by the
number of check boxes that are checked, in this example it's: 3, that
would
give us 18 hours for that part of the schedule.

When I used the Whole expression below nothing happened. The field for
"total hours worked for week was blank even after checking a check box.
But
when I used this portion aloneateDiff("n",[START_TIME],[END_TIME])/60))
it
gave me the difference in start time and end time.

=Sum(IIf([SUN]=Yes Or [MON]=Yes Or [TUE]=Yes Or [WED]=Yes Or [THU]=Yes Or
[FRI]=Yes Or [SAT]=Yes,DateDiff("n",[START_TIME],[END_TIME])/60))

Please let me know if you need any more info. Again, I really appreciate
your time and effort with this!

Thanks,
Paul


"Jeff Boyce" wrote:

?you have fields named [SUN] and [MON] and ...?

This sounds more like a spreadsheet than a relational database.

You say you've tried the expression below. You don't say what happens
when
you try it. Are you getting an error message? Are you getting incorrect
answers?

How do you enter information about a person who starts (and ends) at
different times on different days of the week?

It all starts with the (underlying) data, and I don't have a very clear
picture of that yet...

Regards

Jeff Boyce
Microsoft Office/Access MVP

"Pablito" wrote in message
...
Hi,
I created a Scheduling database. On the scheduling form I have fields
for
"the time the person started work", "the time the person ended work",
"total
hours for the week" and check boxes for "sun", "mon", "tues", "wed",
"thur",
"fri" and "sat". I would like the program to automaticly calculate the
total
hours worked for the week based on the amount of check boxes checked. I
have
included some of the code I have tried.

=Sum(IIf([SUN]=Yes Or [MON]=Yes Or [TUE]=Yes Or [WED]=Yes Or [THU]=Yes
Or
[FRI]=Yes Or [SAT]=Yes,DateDiff("n",[START_TIME],[END_TIME])/60))

Any help with this wil be greatly appreciated!

Thank you,
Paul






  #5  
Old August 29th, 2008, 08:33 PM posted to microsoft.public.access.forms
Pablito
external usenet poster
 
Posts: 6
Default Calculate Total Hours Worked For Week Based On Check Boxes.

Hi Jeff,

We have Care Givers called Attendants that go out to our Clients homes to
provide personal care. Some of our Attendants have split working schedules.
The database I designed for our staffing department has the client name,
Attendant name and the subform part which has there schedule "start time",
"end time", "total hours for week" and seven check boxes for each day of the
week. All I'm trying to do is automate the process so that when the staffing
dept. enters the "start time", "end time" and than checks the number of check
boxes that represents the days worked for that week. I want the program to
calculate the hours worked based on the "start time" and "end time and than
multi that by the number of check boxes that are checked to give me the total
hours worked for that week. As you can imagine some of our attendant's have
some pretty weird schedules. I just want to make this part easier for our
staffing dept.

Here's what the scheduling part of the form looks like. This example shows
a split schedule:

start time end time total hrs. sun mon tue wed thu fri sat
8:00 AM 2:00 PM 18 X X X
7:00 AM 12:00 PM 15 X X X

I hope this gives you a better picture of what I'm trying to do

Thanks,
Paul



"Jeff Boyce" wrote:

Pablito

I still don't have a clear picture of your data, and it all starts with the
data...

If you want to know how many check boxes are checked, add them. You have
zeros for unchecked boxes and -1 for checked boxes in Access/JET. After you
get a total, use the Abs() function to find the positive value.

Note: if any of the checkboxes could be Null, use Nz([YourControl],0) to
convert Null checkboxes to 0.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP


"Pablito" wrote in message
...
Hi Jeff,

Thanks for responding! Actually [SUN], [Mon], [TUE], [Wed], [THU], [FRI]
and [Sat] are check boxes. I have seven check boxes. One for each day of
the week. The way I have the form set up is as a subform on the main
form:

Start Time End Time Total Hours For Week Sun Mon Tue Wed Thu
Fri Sat
8:00 AM 2:00 PM 18 X X X
8:00 AM 4:00 PM 24
X X X

What I want the program to do is find the difference between the start
time
and the end time: 8:00 AM to 2:00 PM is 6 hours than multiply that by the
number of check boxes that are checked, in this example it's: 3, that
would
give us 18 hours for that part of the schedule.

When I used the Whole expression below nothing happened. The field for
"total hours worked for week was blank even after checking a check box.
But
when I used this portion aloneateDiff("n",[START_TIME],[END_TIME])/60))
it
gave me the difference in start time and end time.

=Sum(IIf([SUN]=Yes Or [MON]=Yes Or [TUE]=Yes Or [WED]=Yes Or [THU]=Yes Or
[FRI]=Yes Or [SAT]=Yes,DateDiff("n",[START_TIME],[END_TIME])/60))

Please let me know if you need any more info. Again, I really appreciate
your time and effort with this!

Thanks,
Paul


"Jeff Boyce" wrote:

?you have fields named [SUN] and [MON] and ...?

This sounds more like a spreadsheet than a relational database.

You say you've tried the expression below. You don't say what happens
when
you try it. Are you getting an error message? Are you getting incorrect
answers?

How do you enter information about a person who starts (and ends) at
different times on different days of the week?

It all starts with the (underlying) data, and I don't have a very clear
picture of that yet...

Regards

Jeff Boyce
Microsoft Office/Access MVP

"Pablito" wrote in message
...
Hi,
I created a Scheduling database. On the scheduling form I have fields
for
"the time the person started work", "the time the person ended work",
"total
hours for the week" and check boxes for "sun", "mon", "tues", "wed",
"thur",
"fri" and "sat". I would like the program to automaticly calculate the
total
hours worked for the week based on the amount of check boxes checked. I
have
included some of the code I have tried.

=Sum(IIf([SUN]=Yes Or [MON]=Yes Or [TUE]=Yes Or [WED]=Yes Or [THU]=Yes
Or
[FRI]=Yes Or [SAT]=Yes,DateDiff("n",[START_TIME],[END_TIME])/60))

Any help with this wil be greatly appreciated!

Thank you,
Paul






  #6  
Old August 29th, 2008, 08:57 PM posted to microsoft.public.access.forms
Jeff Boyce
external usenet poster
 
Posts: 8,621
Default Calculate Total Hours Worked For Week Based On Check Boxes.

Did you try my previous suggestion about "adding" the checkboxes?

Regards

Jeff Boyce
Microsoft Office/Access MVP

"Pablito" wrote in message
...
Hi Jeff,

We have Care Givers called Attendants that go out to our Clients homes to
provide personal care. Some of our Attendants have split working
schedules.
The database I designed for our staffing department has the client name,
Attendant name and the subform part which has there schedule "start time",
"end time", "total hours for week" and seven check boxes for each day of
the
week. All I'm trying to do is automate the process so that when the
staffing
dept. enters the "start time", "end time" and than checks the number of
check
boxes that represents the days worked for that week. I want the program
to
calculate the hours worked based on the "start time" and "end time and
than
multi that by the number of check boxes that are checked to give me the
total
hours worked for that week. As you can imagine some of our attendant's
have
some pretty weird schedules. I just want to make this part easier for our
staffing dept.

Here's what the scheduling part of the form looks like. This example
shows
a split schedule:

start time end time total hrs. sun mon tue wed thu fri sat
8:00 AM 2:00 PM 18 X X X
7:00 AM 12:00 PM 15 X X
X

I hope this gives you a better picture of what I'm trying to do

Thanks,
Paul



"Jeff Boyce" wrote:

Pablito

I still don't have a clear picture of your data, and it all starts with
the
data...

If you want to know how many check boxes are checked, add them. You have
zeros for unchecked boxes and -1 for checked boxes in Access/JET. After
you
get a total, use the Abs() function to find the positive value.

Note: if any of the checkboxes could be Null, use Nz([YourControl],0) to
convert Null checkboxes to 0.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP


"Pablito" wrote in message
...
Hi Jeff,

Thanks for responding! Actually [SUN], [Mon], [TUE], [Wed], [THU],
[FRI]
and [Sat] are check boxes. I have seven check boxes. One for each day
of
the week. The way I have the form set up is as a subform on the main
form:

Start Time End Time Total Hours For Week Sun Mon Tue Wed Thu
Fri Sat
8:00 AM 2:00 PM 18 X X
X
8:00 AM 4:00 PM 24
X X X

What I want the program to do is find the difference between the start
time
and the end time: 8:00 AM to 2:00 PM is 6 hours than multiply that by
the
number of check boxes that are checked, in this example it's: 3, that
would
give us 18 hours for that part of the schedule.

When I used the Whole expression below nothing happened. The field for
"total hours worked for week was blank even after checking a check box.
But
when I used this portion
aloneateDiff("n",[START_TIME],[END_TIME])/60))
it
gave me the difference in start time and end time.

=Sum(IIf([SUN]=Yes Or [MON]=Yes Or [TUE]=Yes Or [WED]=Yes Or [THU]=Yes
Or
[FRI]=Yes Or [SAT]=Yes,DateDiff("n",[START_TIME],[END_TIME])/60))

Please let me know if you need any more info. Again, I really
appreciate
your time and effort with this!

Thanks,
Paul


"Jeff Boyce" wrote:

?you have fields named [SUN] and [MON] and ...?

This sounds more like a spreadsheet than a relational database.

You say you've tried the expression below. You don't say what happens
when
you try it. Are you getting an error message? Are you getting
incorrect
answers?

How do you enter information about a person who starts (and ends) at
different times on different days of the week?

It all starts with the (underlying) data, and I don't have a very
clear
picture of that yet...

Regards

Jeff Boyce
Microsoft Office/Access MVP

"Pablito" wrote in message
...
Hi,
I created a Scheduling database. On the scheduling form I have
fields
for
"the time the person started work", "the time the person ended
work",
"total
hours for the week" and check boxes for "sun", "mon", "tues", "wed",
"thur",
"fri" and "sat". I would like the program to automaticly calculate
the
total
hours worked for the week based on the amount of check boxes
checked. I
have
included some of the code I have tried.

=Sum(IIf([SUN]=Yes Or [MON]=Yes Or [TUE]=Yes Or [WED]=Yes Or
[THU]=Yes
Or
[FRI]=Yes Or [SAT]=Yes,DateDiff("n",[START_TIME],[END_TIME])/60))

Any help with this wil be greatly appreciated!

Thank you,
Paul








  #7  
Old August 29th, 2008, 10:25 PM posted to microsoft.public.access.forms
Pablito
external usenet poster
 
Posts: 6
Default Calculate Total Hours Worked For Week Based On Check Boxes.

Hi Jeff,

Yes, I tried adding the check boxes, but I'm not sure I'm using the right
expression. I tried: sum(IIF([sun]= -1 or [mon]= -1 or [tue]= -1 or [wed]=
-1 or [thur]= -1 or [fri]= -1 or [sat]= -1)). No luck so fare.




"Jeff Boyce" wrote:

Did you try my previous suggestion about "adding" the checkboxes?

Regards

Jeff Boyce
Microsoft Office/Access MVP

"Pablito" wrote in message
...
Hi Jeff,

We have Care Givers called Attendants that go out to our Clients homes to
provide personal care. Some of our Attendants have split working
schedules.
The database I designed for our staffing department has the client name,
Attendant name and the subform part which has there schedule "start time",
"end time", "total hours for week" and seven check boxes for each day of
the
week. All I'm trying to do is automate the process so that when the
staffing
dept. enters the "start time", "end time" and than checks the number of
check
boxes that represents the days worked for that week. I want the program
to
calculate the hours worked based on the "start time" and "end time and
than
multi that by the number of check boxes that are checked to give me the
total
hours worked for that week. As you can imagine some of our attendant's
have
some pretty weird schedules. I just want to make this part easier for our
staffing dept.

Here's what the scheduling part of the form looks like. This example
shows
a split schedule:

start time end time total hrs. sun mon tue wed thu fri sat
8:00 AM 2:00 PM 18 X X X
7:00 AM 12:00 PM 15 X X
X

I hope this gives you a better picture of what I'm trying to do

Thanks,
Paul



"Jeff Boyce" wrote:

Pablito

I still don't have a clear picture of your data, and it all starts with
the
data...

If you want to know how many check boxes are checked, add them. You have
zeros for unchecked boxes and -1 for checked boxes in Access/JET. After
you
get a total, use the Abs() function to find the positive value.

Note: if any of the checkboxes could be Null, use Nz([YourControl],0) to
convert Null checkboxes to 0.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP


"Pablito" wrote in message
...
Hi Jeff,

Thanks for responding! Actually [SUN], [Mon], [TUE], [Wed], [THU],
[FRI]
and [Sat] are check boxes. I have seven check boxes. One for each day
of
the week. The way I have the form set up is as a subform on the main
form:

Start Time End Time Total Hours For Week Sun Mon Tue Wed Thu
Fri Sat
8:00 AM 2:00 PM 18 X X
X
8:00 AM 4:00 PM 24
X X X

What I want the program to do is find the difference between the start
time
and the end time: 8:00 AM to 2:00 PM is 6 hours than multiply that by
the
number of check boxes that are checked, in this example it's: 3, that
would
give us 18 hours for that part of the schedule.

When I used the Whole expression below nothing happened. The field for
"total hours worked for week was blank even after checking a check box.
But
when I used this portion
aloneateDiff("n",[START_TIME],[END_TIME])/60))
it
gave me the difference in start time and end time.

=Sum(IIf([SUN]=Yes Or [MON]=Yes Or [TUE]=Yes Or [WED]=Yes Or [THU]=Yes
Or
[FRI]=Yes Or [SAT]=Yes,DateDiff("n",[START_TIME],[END_TIME])/60))

Please let me know if you need any more info. Again, I really
appreciate
your time and effort with this!

Thanks,
Paul


"Jeff Boyce" wrote:

?you have fields named [SUN] and [MON] and ...?

This sounds more like a spreadsheet than a relational database.

You say you've tried the expression below. You don't say what happens
when
you try it. Are you getting an error message? Are you getting
incorrect
answers?

How do you enter information about a person who starts (and ends) at
different times on different days of the week?

It all starts with the (underlying) data, and I don't have a very
clear
picture of that yet...

Regards

Jeff Boyce
Microsoft Office/Access MVP

"Pablito" wrote in message
...
Hi,
I created a Scheduling database. On the scheduling form I have
fields
for
"the time the person started work", "the time the person ended
work",
"total
hours for the week" and check boxes for "sun", "mon", "tues", "wed",
"thur",
"fri" and "sat". I would like the program to automaticly calculate
the
total
hours worked for the week based on the amount of check boxes
checked. I
have
included some of the code I have tried.

=Sum(IIf([SUN]=Yes Or [MON]=Yes Or [TUE]=Yes Or [WED]=Yes Or
[THU]=Yes
Or
[FRI]=Yes Or [SAT]=Yes,DateDiff("n",[START_TIME],[END_TIME])/60))

Any help with this wil be greatly appreciated!

Thank you,
Paul









  #8  
Old August 29th, 2008, 11:51 PM posted to microsoft.public.access.forms
Bob Quintal
external usenet poster
 
Posts: 939
Default Calculate Total Hours Worked For Week Based On Check Boxes.

=?Utf-8?B?UGFibGl0bw==?= wrote
in :

Hi Jeff,

Yes, I tried adding the check boxes, but I'm not sure I'm using
the right expression. I tried: sum(IIF([sun]= -1 or [mon]= -1 or
[tue]= -1 or [wed]= -1 or [thur]= -1 or [fri]= -1 or [sat]= -1)).
No luck so fare.

Jeff told you to sum the checkboxes:
[sun]+[mon]+[tue]+[wed]+[thu]+[fri]+[sat]
and then take the abs() value of that.

He added that if the checkboxes can be null in addition to
true/false
to put nz()around each value:
abs(nz([sun],0)+nz([mon],0)+....




"Jeff Boyce" wrote:

Did you try my previous suggestion about "adding" the checkboxes?

Regards

Jeff Boyce
Microsoft Office/Access MVP

"Pablito" wrote in message
...
Hi Jeff,

We have Care Givers called Attendants that go out to our
Clients homes to provide personal care. Some of our Attendants
have split working schedules.
The database I designed for our staffing department has the
client name, Attendant name and the subform part which has
there schedule "start time", "end time", "total hours for week"
and seven check boxes for each day of the
week. All I'm trying to do is automate the process so that
when the staffing
dept. enters the "start time", "end time" and than checks the
number of check
boxes that represents the days worked for that week. I want
the program to
calculate the hours worked based on the "start time" and "end
time and than
multi that by the number of check boxes that are checked to
give me the total
hours worked for that week. As you can imagine some of our
attendant's have
some pretty weird schedules. I just want to make this part
easier for our staffing dept.

Here's what the scheduling part of the form looks like. This
example shows
a split schedule:

start time end time total hrs. sun mon tue wed thu fri
sat 8:00 AM 2:00 PM 18 X X X
7:00 AM 12:00 PM 15
X X X

I hope this gives you a better picture of what I'm trying to do

Thanks,
Paul



"Jeff Boyce" wrote:

Pablito

I still don't have a clear picture of your data, and it all
starts with the
data...

If you want to know how many check boxes are checked, add
them. You have zeros for unchecked boxes and -1 for checked
boxes in Access/JET. After you
get a total, use the Abs() function to find the positive
value.

Note: if any of the checkboxes could be Null, use
Nz([YourControl],0) to convert Null checkboxes to 0.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP


"Pablito" wrote in message
...
Hi Jeff,

Thanks for responding! Actually [SUN], [Mon], [TUE], [Wed],
[THU], [FRI]
and [Sat] are check boxes. I have seven check boxes. One
for each day of
the week. The way I have the form set up is as a subform on
the main form:

Start Time End Time Total Hours For Week Sun Mon Tue
Wed Thu Fri Sat
8:00 AM 2:00 PM 18 X
X X
8:00 AM 4:00 PM 24
X X X

What I want the program to do is find the difference between
the start time
and the end time: 8:00 AM to 2:00 PM is 6 hours than
multiply that by the
number of check boxes that are checked, in this example
it's: 3, that would
give us 18 hours for that part of the schedule.

When I used the Whole expression below nothing happened.
The field for "total hours worked for week was blank even
after checking a check box. But
when I used this portion
aloneateDiff("n",[START_TIME],[END_TIME])/60))
it
gave me the difference in start time and end time.

=Sum(IIf([SUN]=Yes Or [MON]=Yes Or [TUE]=Yes Or [WED]=Yes Or
[THU]=Yes Or
[FRI]=Yes Or
[SAT]=Yes,DateDiff("n",[START_TIME],[END_TIME])/60))

Please let me know if you need any more info. Again, I
really appreciate
your time and effort with this!

Thanks,
Paul


"Jeff Boyce" wrote:

?you have fields named [SUN] and [MON] and ...?

This sounds more like a spreadsheet than a relational
database.

You say you've tried the expression below. You don't say
what happens when
you try it. Are you getting an error message? Are you
getting incorrect
answers?

How do you enter information about a person who starts (and
ends) at different times on different days of the week?

It all starts with the (underlying) data, and I don't have
a very clear
picture of that yet...

Regards

Jeff Boyce
Microsoft Office/Access MVP

"Pablito" wrote in
message
...
Hi,
I created a Scheduling database. On the scheduling form
I have fields
for
"the time the person started work", "the time the person
ended work",
"total
hours for the week" and check boxes for "sun", "mon",
"tues", "wed", "thur",
"fri" and "sat". I would like the program to automaticly
calculate the
total
hours worked for the week based on the amount of check
boxes checked. I
have
included some of the code I have tried.

=Sum(IIf([SUN]=Yes Or [MON]=Yes Or [TUE]=Yes Or [WED]=Yes
Or [THU]=Yes
Or
[FRI]=Yes Or
[SAT]=Yes,DateDiff("n",[START_TIME],[END_TIME])/60))

Any help with this wil be greatly appreciated!

Thank you,
Paul













--
Bob Quintal

PA is y I've altered my email address.
** Posted from http://www.teranews.com **
  #9  
Old August 29th, 2008, 11:59 PM posted to microsoft.public.access.forms
John W. Vinson
external usenet poster
 
Posts: 18,261
Default Calculate Total Hours Worked For Week Based On Check Boxes.

On Fri, 29 Aug 2008 14:25:00 -0700, Pablito
wrote:

Hi Jeff,

Yes, I tried adding the check boxes, but I'm not sure I'm using the right
expression. I tried: sum(IIF([sun]= -1 or [mon]= -1 or [tue]= -1 or [wed]=
-1 or [thur]= -1 or [fri]= -1 or [sat]= -1)). No luck so fare.


It's not that hard. Try

-Sum([Sun] + [Mon] + [Tue] + [Wed] + [Thu] + [Fri] + [Sat])
--

John W. Vinson [MVP]
 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 01:20 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.