Mercy! MERCY! I BEG YOU, MERCY! Time expression problem

  • Thread starter Thread starter Billiam
  • Start date Start date
B

Billiam

Ok, it has been 5 sleepless days...please help me.

I have a timesheet table (t_TimeSheet) which a form is based on (f_TimeSheet).

First I have a cboEmployee based on q_Employee which is a concatenated
Employee number and name.

Next, I have a cboPayPeriodFrom based on a lookup/reference table
(lt_PayPeriod) and a cboPayPeriodTo based on the same lookup/reference table.
(Long date Format).

Next I have cboWorkDate (Longdate Format).

Next is cboStartTime based on the lookup/refernce table lt_Time (Medium
Date) , then cboFinishTime same lookup table (Medium Date) , then cboLunch
based on lt_TimeInterval (Short Time format), and finally cboOtherTime based
on lt_TimeInterval (Short Time).

The final part of the form has cboWorkCode and an unbound textbox containing
the expression which is causing me this boundless hell that is my life. Ok
Drama over, promise.

Please understand i work for a non-profit agency, with a technophobe boss.
She CANNOT and WILL NOT EVER, EVER, EVER, nor does she want to, understand
the decimal clock. She REFUSES to convert from a decimal format to a "time
format" even when I have printed her a comparison table.

So, I HAVE to display time as 8:30 pm (Dear me, don't even start on the 24
hour clock...it's good ol' AM and PM ONLY for her!) and not 8.5, which we all
know is wonderfully summable.

This is, and *always* will be a 9-5, Monday to Friday (except for poor ol me
trying to figure this out on weekends, so I do have hours then, too) so no
need to worry about exceeding a 24 hour day or work spanning a 2 day period.

I think I understand that time is converted to a decimal format regardless
of how it looks in my combobox, but for the life of me, I cannot, even with
help, seem to get the darn expression to calculate properly...

Here is what I need to do:

1. Calculate the difference between StartTime and FinishTime and LunchTime,
and display that value in the SplitForm I am using, as well as the
TotalDailyHours Field in t_TimeSheet.

2. HOWEVER, I also need to add, on occassion, an amount of time from
cboOtherTime (which is based on the lt_TimeInterval (short time format). AND
sometimes all that I will have is Othertime (and of course the employee,
payperiod, the workdate and the workcode, which I also need to display in my
textbox expression and store in t_TimeSheet in the OtherTime field.

Okay, here is the unbound textbox expression (cannot seem to get the
epression calculated time to enter into the t_timesheet TotalDailyHours
field, even though it shows in the datasheet view of the splitform...

=IIf([OtherTime]>0,DateDiff("n",0,[OtherTime])/60,(DateDiff("n",[StartTime],[FinishTime])-Int([LunchTime])*1440)/60)

The expression will not subtract LunchTime to give the total daily hours.

It will give me ONLY the Othertime total when it is the only value chosen
(ie no starttime, lunchtime or finishtime is chosen)...this is good.

It will not, however, add the Othertime value to the StartTime LunchTime
FinishTime portion of the equation.

Finally, I need to add these values of time to produce a bi-weekly timesheet
with a subtotaled week one by workcode.

If there are any angels out there, HERE I AM, Please help me before I loose
my tiny, little mind!!!

Thank you for reading my first Novella, I hope you at least got a chuckle ot
of it...

Billiam
 
Try this expression (as one line):
=(DateDiff("n",[StartTime],[FinishTime]) +
IIf([OtherTime] Is Null, 0 DateDiff("n",#00:00:00#,[OtherTime]) -
IIf([LunchTime] Is Null, 0, DateDiff("n", #00:00:00#, [LunchTime])

Assumes all fields are of type Date/Time. Bracket the entire expression
(after =) and divide by 60 if you want hours instead of minutes.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.


Billiam said:
Ok, it has been 5 sleepless days...please help me.

I have a timesheet table (t_TimeSheet) which a form is based on
(f_TimeSheet).

First I have a cboEmployee based on q_Employee which is a concatenated
Employee number and name.

Next, I have a cboPayPeriodFrom based on a lookup/reference table
(lt_PayPeriod) and a cboPayPeriodTo based on the same lookup/reference
table.
(Long date Format).

Next I have cboWorkDate (Longdate Format).

Next is cboStartTime based on the lookup/refernce table lt_Time (Medium
Date) , then cboFinishTime same lookup table (Medium Date) , then cboLunch
based on lt_TimeInterval (Short Time format), and finally cboOtherTime
based
on lt_TimeInterval (Short Time).

The final part of the form has cboWorkCode and an unbound textbox
containing
the expression which is causing me this boundless hell that is my life. Ok
Drama over, promise.

Please understand i work for a non-profit agency, with a technophobe boss.
She CANNOT and WILL NOT EVER, EVER, EVER, nor does she want to, understand
the decimal clock. She REFUSES to convert from a decimal format to a "time
format" even when I have printed her a comparison table.

So, I HAVE to display time as 8:30 pm (Dear me, don't even start on the 24
hour clock...it's good ol' AM and PM ONLY for her!) and not 8.5, which we
all
know is wonderfully summable.

This is, and *always* will be a 9-5, Monday to Friday (except for poor ol
me
trying to figure this out on weekends, so I do have hours then, too) so no
need to worry about exceeding a 24 hour day or work spanning a 2 day
period.

I think I understand that time is converted to a decimal format regardless
of how it looks in my combobox, but for the life of me, I cannot, even
with
help, seem to get the darn expression to calculate properly...

Here is what I need to do:

1. Calculate the difference between StartTime and FinishTime and
LunchTime,
and display that value in the SplitForm I am using, as well as the
TotalDailyHours Field in t_TimeSheet.

2. HOWEVER, I also need to add, on occassion, an amount of time from
cboOtherTime (which is based on the lt_TimeInterval (short time format).
AND
sometimes all that I will have is Othertime (and of course the employee,
payperiod, the workdate and the workcode, which I also need to display in
my
textbox expression and store in t_TimeSheet in the OtherTime field.

Okay, here is the unbound textbox expression (cannot seem to get the
epression calculated time to enter into the t_timesheet TotalDailyHours
field, even though it shows in the datasheet view of the splitform...

=IIf([OtherTime]>0,DateDiff("n",0,[OtherTime])/60,(DateDiff("n",[StartTime],[FinishTime])-Int([LunchTime])*1440)/60)

The expression will not subtract LunchTime to give the total daily hours.

It will give me ONLY the Othertime total when it is the only value chosen
(ie no starttime, lunchtime or finishtime is chosen)...this is good.

It will not, however, add the Othertime value to the StartTime LunchTime
FinishTime portion of the equation.

Finally, I need to add these values of time to produce a bi-weekly
timesheet
with a subtotaled week one by workcode.

If there are any angels out there, HERE I AM, Please help me before I
loose
my tiny, little mind!!!

Thank you for reading my first Novella, I hope you at least got a chuckle
ot
of it...

Billiam
Click to expand...
 
WOW! The KING of the angels...

Allen, At first I was getting the error invalid syntax. I noticed there
wasn't a comma after the 0 in the OtherTime Immeadiate IF, so I added one
there...do not know if that is wrong, but when I did, it said I was missing a
closing parenthesis, bracket or vertical bar.

Here is the copy I have used:

=((DateDiff("n",[StartTime],[FinishTime]) +IIf([OtherTime] Is Null, 0,
DateDiff("n",#00:00:00#,[OtherTime]) -IIf([LunchTime] Is Null, 0,
DateDiff("n", #00:00:00#, [LunchTime]))/60

I can't figure out where to put the other bracket or parenthesis.

Thank you for your help!

Billiam

Allen Browne said:
Try this expression (as one line):
=(DateDiff("n",[StartTime],[FinishTime]) +
IIf([OtherTime] Is Null, 0 DateDiff("n",#00:00:00#,[OtherTime]) -
IIf([LunchTime] Is Null, 0, DateDiff("n", #00:00:00#, [LunchTime])

Assumes all fields are of type Date/Time. Bracket the entire expression
(after =) and divide by 60 if you want hours instead of minutes.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.


Billiam said:
Ok, it has been 5 sleepless days...please help me.

I have a timesheet table (t_TimeSheet) which a form is based on
(f_TimeSheet).

First I have a cboEmployee based on q_Employee which is a concatenated
Employee number and name.

Next, I have a cboPayPeriodFrom based on a lookup/reference table
(lt_PayPeriod) and a cboPayPeriodTo based on the same lookup/reference
table.
(Long date Format).

Next I have cboWorkDate (Longdate Format).

Next is cboStartTime based on the lookup/refernce table lt_Time (Medium
Date) , then cboFinishTime same lookup table (Medium Date) , then cboLunch
based on lt_TimeInterval (Short Time format), and finally cboOtherTime
based
on lt_TimeInterval (Short Time).

The final part of the form has cboWorkCode and an unbound textbox
containing
the expression which is causing me this boundless hell that is my life. Ok
Drama over, promise.

Please understand i work for a non-profit agency, with a technophobe boss.
She CANNOT and WILL NOT EVER, EVER, EVER, nor does she want to, understand
the decimal clock. She REFUSES to convert from a decimal format to a "time
format" even when I have printed her a comparison table.

So, I HAVE to display time as 8:30 pm (Dear me, don't even start on the 24
hour clock...it's good ol' AM and PM ONLY for her!) and not 8.5, which we
all
know is wonderfully summable.

This is, and *always* will be a 9-5, Monday to Friday (except for poor ol
me
trying to figure this out on weekends, so I do have hours then, too) so no
need to worry about exceeding a 24 hour day or work spanning a 2 day
period.

I think I understand that time is converted to a decimal format regardless
of how it looks in my combobox, but for the life of me, I cannot, even
with
help, seem to get the darn expression to calculate properly...

Here is what I need to do:

1. Calculate the difference between StartTime and FinishTime and
LunchTime,
and display that value in the SplitForm I am using, as well as the
TotalDailyHours Field in t_TimeSheet.

2. HOWEVER, I also need to add, on occassion, an amount of time from
cboOtherTime (which is based on the lt_TimeInterval (short time format).
AND
sometimes all that I will have is Othertime (and of course the employee,
payperiod, the workdate and the workcode, which I also need to display in
my
textbox expression and store in t_TimeSheet in the OtherTime field.

Okay, here is the unbound textbox expression (cannot seem to get the
epression calculated time to enter into the t_timesheet TotalDailyHours
field, even though it shows in the datasheet view of the splitform...

=IIf([OtherTime]>0,DateDiff("n",0,[OtherTime])/60,(DateDiff("n",[StartTime],[FinishTime])-Int([LunchTime])*1440)/60)

The expression will not subtract LunchTime to give the total daily hours.

It will give me ONLY the Othertime total when it is the only value chosen
(ie no starttime, lunchtime or finishtime is chosen)...this is good.

It will not, however, add the Othertime value to the StartTime LunchTime
FinishTime portion of the equation.

Finally, I need to add these values of time to produce a bi-weekly
timesheet
with a subtotaled week one by workcode.

If there are any angels out there, HERE I AM, Please help me before I
loose
my tiny, little mind!!!

Thank you for reading my first Novella, I hope you at least got a chuckle
ot
of it...

Billiam
Click to expand...
Click to expand...
 
Hi Billiam

The problem is that there are more opening parentheses that closing ones.
Sometimes it can get a bit confusing, but a good quick check is to count,
adding one for each opening and subtracting one for each closing. You
should end up with zero.

In your case, the count goes: 1,2,3,2,3,4,3,4,5,4,3 - which ain't zero!

It also helps to split complex expressions up on multiple lines, inserting
spaces and indenting new levels, so you can really see what is happening:

=(
DateDiff( "n", [StartTime], [FinishTime] )
+ IIf( [OtherTime] Is Null, 0, DateDiff("n", #00:00:00#, [OtherTime] ) )
- IIf( [LunchTime] Is Null, 0, DateDiff("n", #00:00:00#, [LunchTime] ) )
) / 60

Note that the parentheses are balanced on each of the three middle lines,
then we simply bracket the whole thing and divide by 60.

--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand


Billiam said:
WOW! The KING of the angels...

Allen, At first I was getting the error invalid syntax. I noticed there
wasn't a comma after the 0 in the OtherTime Immeadiate IF, so I added one
there...do not know if that is wrong, but when I did, it said I was
missing a
closing parenthesis, bracket or vertical bar.

Here is the copy I have used:

=((DateDiff("n",[StartTime],[FinishTime]) +IIf([OtherTime] Is Null, 0,
DateDiff("n",#00:00:00#,[OtherTime]) -IIf([LunchTime] Is Null, 0,
DateDiff("n", #00:00:00#, [LunchTime]))/60

I can't figure out where to put the other bracket or parenthesis.

Thank you for your help!

Billiam

Allen Browne said:
Try this expression (as one line):
=(DateDiff("n",[StartTime],[FinishTime]) +
IIf([OtherTime] Is Null, 0 DateDiff("n",#00:00:00#,[OtherTime]) -
IIf([LunchTime] Is Null, 0, DateDiff("n", #00:00:00#, [LunchTime])

Assumes all fields are of type Date/Time. Bracket the entire expression
(after =) and divide by 60 if you want hours instead of minutes.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.


Billiam said:
Ok, it has been 5 sleepless days...please help me.

I have a timesheet table (t_TimeSheet) which a form is based on
(f_TimeSheet).

First I have a cboEmployee based on q_Employee which is a concatenated
Employee number and name.

Next, I have a cboPayPeriodFrom based on a lookup/reference table
(lt_PayPeriod) and a cboPayPeriodTo based on the same lookup/reference
table.
(Long date Format).

Next I have cboWorkDate (Longdate Format).

Next is cboStartTime based on the lookup/refernce table lt_Time (Medium
Date) , then cboFinishTime same lookup table (Medium Date) , then
cboLunch
based on lt_TimeInterval (Short Time format), and finally cboOtherTime
based
on lt_TimeInterval (Short Time).

The final part of the form has cboWorkCode and an unbound textbox
containing
the expression which is causing me this boundless hell that is my life.
Ok
Drama over, promise.

Please understand i work for a non-profit agency, with a technophobe
boss.
She CANNOT and WILL NOT EVER, EVER, EVER, nor does she want to,
understand
the decimal clock. She REFUSES to convert from a decimal format to a
"time
format" even when I have printed her a comparison table.

So, I HAVE to display time as 8:30 pm (Dear me, don't even start on the
24
hour clock...it's good ol' AM and PM ONLY for her!) and not 8.5, which
we
all
know is wonderfully summable.

This is, and *always* will be a 9-5, Monday to Friday (except for poor
ol
me
trying to figure this out on weekends, so I do have hours then, too) so
no
need to worry about exceeding a 24 hour day or work spanning a 2 day
period.

I think I understand that time is converted to a decimal format
regardless
of how it looks in my combobox, but for the life of me, I cannot, even
with
help, seem to get the darn expression to calculate properly...

Here is what I need to do:

1. Calculate the difference between StartTime and FinishTime and
LunchTime,
and display that value in the SplitForm I am using, as well as the
TotalDailyHours Field in t_TimeSheet.

2. HOWEVER, I also need to add, on occassion, an amount of time from
cboOtherTime (which is based on the lt_TimeInterval (short time
format).
AND
sometimes all that I will have is Othertime (and of course the
employee,
payperiod, the workdate and the workcode, which I also need to display
in
my
textbox expression and store in t_TimeSheet in the OtherTime field.

Okay, here is the unbound textbox expression (cannot seem to get the
epression calculated time to enter into the t_timesheet TotalDailyHours
field, even though it shows in the datasheet view of the splitform...

=IIf([OtherTime]>0,DateDiff("n",0,[OtherTime])/60,(DateDiff("n",[StartTime],[FinishTime])-Int([LunchTime])*1440)/60)

The expression will not subtract LunchTime to give the total daily
hours.

It will give me ONLY the Othertime total when it is the only value
chosen
(ie no starttime, lunchtime or finishtime is chosen)...this is good.

It will not, however, add the Othertime value to the StartTime
LunchTime
FinishTime portion of the equation.

Finally, I need to add these values of time to produce a bi-weekly
timesheet
with a subtotaled week one by workcode.

If there are any angels out there, HERE I AM, Please help me before I
loose
my tiny, little mind!!!

Thank you for reading my first Novella, I hope you at least got a
chuckle
ot
of it...

Billiam
Click to expand...
Click to expand...
Click to expand...
 
Thank you, Graham, for your help. Indeed that does solve the missing bracket
problem. I wonder if you can help me further.

As i tried to explain before, and not very well, there are 3 possible
situations I need the expression to handle:

1. The employee enters in a starttime, finishtime and a lunchtime.

2. The employee enters in a starttime, finishtime, lunchtime AND othertime.

3. The employee ONLY enters in othertime. There is nothing, literally
nothing, in starttime, finishtime or lunchtime.

The expression handles #1 and #2 scenarios, but it does not return a result
for #3. (this siyuation occurs when an employee has a sick day or vacation
day, so they did not have a start or finish time, just a block of time,
WORKCODE would be Sick time in this case, and not Regular Time as it would if
there was a start and finishtime).

I think i need to include an Immeadiate IIf, but am not sure where to put
it, as i suspect the fact that there are no values in startTime and
FinishTime are what is causing the expression not to produce a resiult for
just an OtherTime entry.


=IIf([StartTime] Is Null,0) +IIF([FinishTime] Is Null,0) IIF([LunchTime]) Is
Null,0)+(DateDiff("n",[StartTime],[FinishTime])+IIf([OtherTime] Is
Null,0,DateDiff("n",#12:00:00 AM#,[OtherTime]))-IIf([LunchTime] Is
Null,0,DateDiff("n",#12:00:00 AM#,[LunchTime])))/60

I hope this makes it clearer what i am trying to do.
Fianl thing is how do I now put that calculated value in the t_Timesheet
table since the expression is unbound...or, how do you create a bound
expression?

Thanks for any help!

Billiam
Graham Mandeno said:
Hi Billiam

The problem is that there are more opening parentheses that closing ones.
Sometimes it can get a bit confusing, but a good quick check is to count,
adding one for each opening and subtracting one for each closing. You
should end up with zero.

In your case, the count goes: 1,2,3,2,3,4,3,4,5,4,3 - which ain't zero!

It also helps to split complex expressions up on multiple lines, inserting
spaces and indenting new levels, so you can really see what is happening:

=(
DateDiff( "n", [StartTime], [FinishTime] )
+ IIf( [OtherTime] Is Null, 0, DateDiff("n", #00:00:00#, [OtherTime] ) )
- IIf( [LunchTime] Is Null, 0, DateDiff("n", #00:00:00#, [LunchTime] ) )
) / 60

Note that the parentheses are balanced on each of the three middle lines,
then we simply bracket the whole thing and divide by 60.

--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand


Billiam said:
WOW! The KING of the angels...

Allen, At first I was getting the error invalid syntax. I noticed there
wasn't a comma after the 0 in the OtherTime Immeadiate IF, so I added one
there...do not know if that is wrong, but when I did, it said I was
missing a
closing parenthesis, bracket or vertical bar.

Here is the copy I have used:

=((DateDiff("n",[StartTime],[FinishTime]) +IIf([OtherTime] Is Null, 0,
DateDiff("n",#00:00:00#,[OtherTime]) -IIf([LunchTime] Is Null, 0,
DateDiff("n", #00:00:00#, [LunchTime]))/60

I can't figure out where to put the other bracket or parenthesis.

Thank you for your help!

Billiam

Allen Browne said:
Try this expression (as one line):
=(DateDiff("n",[StartTime],[FinishTime]) +
IIf([OtherTime] Is Null, 0 DateDiff("n",#00:00:00#,[OtherTime]) -
IIf([LunchTime] Is Null, 0, DateDiff("n", #00:00:00#, [LunchTime])

Assumes all fields are of type Date/Time. Bracket the entire expression
(after =) and divide by 60 if you want hours instead of minutes.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.


Ok, it has been 5 sleepless days...please help me.

I have a timesheet table (t_TimeSheet) which a form is based on
(f_TimeSheet).

First I have a cboEmployee based on q_Employee which is a concatenated
Employee number and name.

Next, I have a cboPayPeriodFrom based on a lookup/reference table
(lt_PayPeriod) and a cboPayPeriodTo based on the same lookup/reference
table.
(Long date Format).

Next I have cboWorkDate (Longdate Format).

Next is cboStartTime based on the lookup/refernce table lt_Time (Medium
Date) , then cboFinishTime same lookup table (Medium Date) , then
cboLunch
based on lt_TimeInterval (Short Time format), and finally cboOtherTime
based
on lt_TimeInterval (Short Time).

The final part of the form has cboWorkCode and an unbound textbox
containing
the expression which is causing me this boundless hell that is my life.
Ok
Drama over, promise.

Please understand i work for a non-profit agency, with a technophobe
boss.
She CANNOT and WILL NOT EVER, EVER, EVER, nor does she want to,
understand
the decimal clock. She REFUSES to convert from a decimal format to a
"time
format" even when I have printed her a comparison table.

So, I HAVE to display time as 8:30 pm (Dear me, don't even start on the
24
hour clock...it's good ol' AM and PM ONLY for her!) and not 8.5, which
we
all
know is wonderfully summable.

This is, and *always* will be a 9-5, Monday to Friday (except for poor
ol
me
trying to figure this out on weekends, so I do have hours then, too) so
no
need to worry about exceeding a 24 hour day or work spanning a 2 day
period.

I think I understand that time is converted to a decimal format
regardless
of how it looks in my combobox, but for the life of me, I cannot, even
with
help, seem to get the darn expression to calculate properly...

Here is what I need to do:

1. Calculate the difference between StartTime and FinishTime and
LunchTime,
and display that value in the SplitForm I am using, as well as the
TotalDailyHours Field in t_TimeSheet.

2. HOWEVER, I also need to add, on occassion, an amount of time from
cboOtherTime (which is based on the lt_TimeInterval (short time
format).
AND
sometimes all that I will have is Othertime (and of course the
employee,
payperiod, the workdate and the workcode, which I also need to display
in
my
textbox expression and store in t_TimeSheet in the OtherTime field.

Okay, here is the unbound textbox expression (cannot seem to get the
epression calculated time to enter into the t_timesheet TotalDailyHours
field, even though it shows in the datasheet view of the splitform...

=IIf([OtherTime]>0,DateDiff("n",0,[OtherTime])/60,(DateDiff("n",[StartTime],[FinishTime])-Int([LunchTime])*1440)/60)

The expression will not subtract LunchTime to give the total daily
hours.

It will give me ONLY the Othertime total when it is the only value
chosen
(ie no starttime, lunchtime or finishtime is chosen)...this is good.

It will not, however, add the Othertime value to the StartTime
LunchTime
FinishTime portion of the equation.

Finally, I need to add these values of time to produce a bi-weekly
timesheet
with a subtotaled week one by workcode.

If there are any angels out there, HERE I AM, Please help me before I
loose
my tiny, little mind!!!

Thank you for reading my first Novella, I hope you at least got a
chuckle
ot
of it...

Billiam
Click to expand...
Click to expand...
Click to expand...
 
Hi Billiam

The problem is that this expression:
DateDiff( "n", [StartTime], [FinishTime] )
will return Null if *either* [StartTime] or [FinishTime] is Null.

Then Null + anything gives Null.

The trick, then, is to check for nulls in either of those two fields as
well:

=(
IIf( [StartTime] Is Null or [FinishTime Is Null, 0,
DateDiff( "n", [StartTime], [FinishTime] ) )
+ IIf( [OtherTime] Is Null, 0, DateDiff("n", #00:00:00#, [OtherTime] ) )
- IIf( [LunchTime] Is Null, 0, DateDiff("n", #00:00:00#, [LunchTime] ) )
) / 60


--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

Billiam said:
Thank you, Graham, for your help. Indeed that does solve the missing
bracket
problem. I wonder if you can help me further.

As i tried to explain before, and not very well, there are 3 possible
situations I need the expression to handle:

1. The employee enters in a starttime, finishtime and a lunchtime.

2. The employee enters in a starttime, finishtime, lunchtime AND
othertime.

3. The employee ONLY enters in othertime. There is nothing, literally
nothing, in starttime, finishtime or lunchtime.

The expression handles #1 and #2 scenarios, but it does not return a
result
for #3. (this siyuation occurs when an employee has a sick day or vacation
day, so they did not have a start or finish time, just a block of time,
WORKCODE would be Sick time in this case, and not Regular Time as it would
if
there was a start and finishtime).

I think i need to include an Immeadiate IIf, but am not sure where to put
it, as i suspect the fact that there are no values in startTime and
FinishTime are what is causing the expression not to produce a resiult for
just an OtherTime entry.


=IIf([StartTime] Is Null,0) +IIF([FinishTime] Is Null,0) IIF([LunchTime])
Is
Null,0)+(DateDiff("n",[StartTime],[FinishTime])+IIf([OtherTime] Is
Null,0,DateDiff("n",#12:00:00 AM#,[OtherTime]))-IIf([LunchTime] Is
Null,0,DateDiff("n",#12:00:00 AM#,[LunchTime])))/60

I hope this makes it clearer what i am trying to do.
Fianl thing is how do I now put that calculated value in the t_Timesheet
table since the expression is unbound...or, how do you create a bound
expression?

Thanks for any help!

Billiam
Graham Mandeno said:
Hi Billiam

The problem is that there are more opening parentheses that closing ones.
Sometimes it can get a bit confusing, but a good quick check is to count,
adding one for each opening and subtracting one for each closing. You
should end up with zero.

In your case, the count goes: 1,2,3,2,3,4,3,4,5,4,3 - which ain't zero!

It also helps to split complex expressions up on multiple lines,
inserting
spaces and indenting new levels, so you can really see what is happening:

=(
DateDiff( "n", [StartTime], [FinishTime] )
+ IIf( [OtherTime] Is Null, 0, DateDiff("n", #00:00:00#, [OtherTime] ) )
- IIf( [LunchTime] Is Null, 0, DateDiff("n", #00:00:00#,
[LunchTime] ) )
) / 60

Note that the parentheses are balanced on each of the three middle lines,
then we simply bracket the whole thing and divide by 60.

--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand


Billiam said:
WOW! The KING of the angels...

Allen, At first I was getting the error invalid syntax. I noticed there
wasn't a comma after the 0 in the OtherTime Immeadiate IF, so I added
one
there...do not know if that is wrong, but when I did, it said I was
missing a
closing parenthesis, bracket or vertical bar.

Here is the copy I have used:

=((DateDiff("n",[StartTime],[FinishTime]) +IIf([OtherTime] Is Null, 0,
DateDiff("n",#00:00:00#,[OtherTime]) -IIf([LunchTime] Is Null, 0,
DateDiff("n", #00:00:00#, [LunchTime]))/60

I can't figure out where to put the other bracket or parenthesis.

Thank you for your help!

Billiam

:

Try this expression (as one line):
=(DateDiff("n",[StartTime],[FinishTime]) +
IIf([OtherTime] Is Null, 0 DateDiff("n",#00:00:00#,[OtherTime]) -
IIf([LunchTime] Is Null, 0, DateDiff("n", #00:00:00#, [LunchTime])

Assumes all fields are of type Date/Time. Bracket the entire
expression
(after =) and divide by 60 if you want hours instead of minutes.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.


Ok, it has been 5 sleepless days...please help me.

I have a timesheet table (t_TimeSheet) which a form is based on
(f_TimeSheet).

First I have a cboEmployee based on q_Employee which is a
concatenated
Employee number and name.

Next, I have a cboPayPeriodFrom based on a lookup/reference table
(lt_PayPeriod) and a cboPayPeriodTo based on the same
lookup/reference
table.
(Long date Format).

Next I have cboWorkDate (Longdate Format).

Next is cboStartTime based on the lookup/refernce table lt_Time
(Medium
Date) , then cboFinishTime same lookup table (Medium Date) , then
cboLunch
based on lt_TimeInterval (Short Time format), and finally
cboOtherTime
based
on lt_TimeInterval (Short Time).

The final part of the form has cboWorkCode and an unbound textbox
containing
the expression which is causing me this boundless hell that is my
life.
Ok
Drama over, promise.

Please understand i work for a non-profit agency, with a technophobe
boss.
She CANNOT and WILL NOT EVER, EVER, EVER, nor does she want to,
understand
the decimal clock. She REFUSES to convert from a decimal format to a
"time
format" even when I have printed her a comparison table.

So, I HAVE to display time as 8:30 pm (Dear me, don't even start on
the
24
hour clock...it's good ol' AM and PM ONLY for her!) and not 8.5,
which
we
all
know is wonderfully summable.

This is, and *always* will be a 9-5, Monday to Friday (except for
poor
ol
me
trying to figure this out on weekends, so I do have hours then, too)
so
no
need to worry about exceeding a 24 hour day or work spanning a 2 day
period.

I think I understand that time is converted to a decimal format
regardless
of how it looks in my combobox, but for the life of me, I cannot,
even
with
help, seem to get the darn expression to calculate properly...

Here is what I need to do:

1. Calculate the difference between StartTime and FinishTime and
LunchTime,
and display that value in the SplitForm I am using, as well as the
TotalDailyHours Field in t_TimeSheet.

2. HOWEVER, I also need to add, on occassion, an amount of time from
cboOtherTime (which is based on the lt_TimeInterval (short time
format).
AND
sometimes all that I will have is Othertime (and of course the
employee,
payperiod, the workdate and the workcode, which I also need to
display
in
my
textbox expression and store in t_TimeSheet in the OtherTime field.

Okay, here is the unbound textbox expression (cannot seem to get the
epression calculated time to enter into the t_timesheet
TotalDailyHours
field, even though it shows in the datasheet view of the
splitform...

=IIf([OtherTime]>0,DateDiff("n",0,[OtherTime])/60,(DateDiff("n",[StartTime],[FinishTime])-Int([LunchTime])*1440)/60)

The expression will not subtract LunchTime to give the total daily
hours.

It will give me ONLY the Othertime total when it is the only value
chosen
(ie no starttime, lunchtime or finishtime is chosen)...this is good.

It will not, however, add the Othertime value to the StartTime
LunchTime
FinishTime portion of the equation.

Finally, I need to add these values of time to produce a bi-weekly
timesheet
with a subtotaled week one by workcode.

If there are any angels out there, HERE I AM, Please help me before
I
loose
my tiny, little mind!!!

Thank you for reading my first Novella, I hope you at least got a
chuckle
ot
of it...

Billiam
Click to expand...
Click to expand...
Click to expand...
 
Graham, you solved it!!! I cannot thank you enough, but i am praying
something REALY GREAT happens to you...thanks for taking the time to explain
this and solve this. I will never forget this lesson!

Have an awesome week, and THANK YOU, THANK YOU, THANK YOU!!!


Billiam

Graham Mandeno said:
Hi Billiam

The problem is that this expression:
DateDiff( "n", [StartTime], [FinishTime] )
will return Null if *either* [StartTime] or [FinishTime] is Null.

Then Null + anything gives Null.

The trick, then, is to check for nulls in either of those two fields as
well:

=(
IIf( [StartTime] Is Null or [FinishTime Is Null, 0,
DateDiff( "n", [StartTime], [FinishTime] ) )
+ IIf( [OtherTime] Is Null, 0, DateDiff("n", #00:00:00#, [OtherTime] ) )
- IIf( [LunchTime] Is Null, 0, DateDiff("n", #00:00:00#, [LunchTime] ) )
) / 60


--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

Billiam said:
Thank you, Graham, for your help. Indeed that does solve the missing
bracket
problem. I wonder if you can help me further.

As i tried to explain before, and not very well, there are 3 possible
situations I need the expression to handle:

1. The employee enters in a starttime, finishtime and a lunchtime.

2. The employee enters in a starttime, finishtime, lunchtime AND
othertime.

3. The employee ONLY enters in othertime. There is nothing, literally
nothing, in starttime, finishtime or lunchtime.

The expression handles #1 and #2 scenarios, but it does not return a
result
for #3. (this siyuation occurs when an employee has a sick day or vacation
day, so they did not have a start or finish time, just a block of time,
WORKCODE would be Sick time in this case, and not Regular Time as it would
if
there was a start and finishtime).

I think i need to include an Immeadiate IIf, but am not sure where to put
it, as i suspect the fact that there are no values in startTime and
FinishTime are what is causing the expression not to produce a resiult for
just an OtherTime entry.


=IIf([StartTime] Is Null,0) +IIF([FinishTime] Is Null,0) IIF([LunchTime])
Is
Null,0)+(DateDiff("n",[StartTime],[FinishTime])+IIf([OtherTime] Is
Null,0,DateDiff("n",#12:00:00 AM#,[OtherTime]))-IIf([LunchTime] Is
Null,0,DateDiff("n",#12:00:00 AM#,[LunchTime])))/60

I hope this makes it clearer what i am trying to do.
Fianl thing is how do I now put that calculated value in the t_Timesheet
table since the expression is unbound...or, how do you create a bound
expression?

Thanks for any help!

Billiam
Graham Mandeno said:
Hi Billiam

The problem is that there are more opening parentheses that closing ones.
Sometimes it can get a bit confusing, but a good quick check is to count,
adding one for each opening and subtracting one for each closing. You
should end up with zero.

In your case, the count goes: 1,2,3,2,3,4,3,4,5,4,3 - which ain't zero!

It also helps to split complex expressions up on multiple lines,
inserting
spaces and indenting new levels, so you can really see what is happening:

=(
DateDiff( "n", [StartTime], [FinishTime] )
+ IIf( [OtherTime] Is Null, 0, DateDiff("n", #00:00:00#, [OtherTime] ) )
- IIf( [LunchTime] Is Null, 0, DateDiff("n", #00:00:00#,
[LunchTime] ) )
) / 60

Note that the parentheses are balanced on each of the three middle lines,
then we simply bracket the whole thing and divide by 60.

--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand


WOW! The KING of the angels...

Allen, At first I was getting the error invalid syntax. I noticed there
wasn't a comma after the 0 in the OtherTime Immeadiate IF, so I added
one
there...do not know if that is wrong, but when I did, it said I was
missing a
closing parenthesis, bracket or vertical bar.

Here is the copy I have used:

=((DateDiff("n",[StartTime],[FinishTime]) +IIf([OtherTime] Is Null, 0,
DateDiff("n",#00:00:00#,[OtherTime]) -IIf([LunchTime] Is Null, 0,
DateDiff("n", #00:00:00#, [LunchTime]))/60

I can't figure out where to put the other bracket or parenthesis.

Thank you for your help!

Billiam

:

Try this expression (as one line):
=(DateDiff("n",[StartTime],[FinishTime]) +
IIf([OtherTime] Is Null, 0 DateDiff("n",#00:00:00#,[OtherTime]) -
IIf([LunchTime] Is Null, 0, DateDiff("n", #00:00:00#, [LunchTime])

Assumes all fields are of type Date/Time. Bracket the entire
expression
(after =) and divide by 60 if you want hours instead of minutes.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.


Ok, it has been 5 sleepless days...please help me.

I have a timesheet table (t_TimeSheet) which a form is based on
(f_TimeSheet).

First I have a cboEmployee based on q_Employee which is a
concatenated
Employee number and name.

Next, I have a cboPayPeriodFrom based on a lookup/reference table
(lt_PayPeriod) and a cboPayPeriodTo based on the same
lookup/reference
table.
(Long date Format).

Next I have cboWorkDate (Longdate Format).

Next is cboStartTime based on the lookup/refernce table lt_Time
(Medium
Date) , then cboFinishTime same lookup table (Medium Date) , then
cboLunch
based on lt_TimeInterval (Short Time format), and finally
cboOtherTime
based
on lt_TimeInterval (Short Time).

The final part of the form has cboWorkCode and an unbound textbox
containing
the expression which is causing me this boundless hell that is my
life.
Ok
Drama over, promise.

Please understand i work for a non-profit agency, with a technophobe
boss.
She CANNOT and WILL NOT EVER, EVER, EVER, nor does she want to,
understand
the decimal clock. She REFUSES to convert from a decimal format to a
"time
format" even when I have printed her a comparison table.

So, I HAVE to display time as 8:30 pm (Dear me, don't even start on
the
24
hour clock...it's good ol' AM and PM ONLY for her!) and not 8.5,
which
we
all
know is wonderfully summable.

This is, and *always* will be a 9-5, Monday to Friday (except for
poor
ol
me
trying to figure this out on weekends, so I do have hours then, too)
so
no
need to worry about exceeding a 24 hour day or work spanning a 2 day
period.

I think I understand that time is converted to a decimal format
regardless
of how it looks in my combobox, but for the life of me, I cannot,
even
with
help, seem to get the darn expression to calculate properly...

Here is what I need to do:

1. Calculate the difference between StartTime and FinishTime and
LunchTime,
and display that value in the SplitForm I am using, as well as the
TotalDailyHours Field in t_TimeSheet.

2. HOWEVER, I also need to add, on occassion, an amount of time from
cboOtherTime (which is based on the lt_TimeInterval (short time
format).
AND
sometimes all that I will have is Othertime (and of course the
employee,
payperiod, the workdate and the workcode, which I also need to
display
in
my
textbox expression and store in t_TimeSheet in the OtherTime field.

Okay, here is the unbound textbox expression (cannot seem to get the
epression calculated time to enter into the t_timesheet
TotalDailyHours
field, even though it shows in the datasheet view of the
splitform...

=IIf([OtherTime]>0,DateDiff("n",0,[OtherTime])/60,(DateDiff("n",[StartTime],[FinishTime])-Int([LunchTime])*1440)/60)

The expression will not subtract LunchTime to give the total daily
hours.

It will give me ONLY the Othertime total when it is the only value
chosen
(ie no starttime, lunchtime or finishtime is chosen)...this is good.

It will not, however, add the Othertime value to the StartTime
LunchTime
FinishTime portion of the equation.

Finally, I need to add these values of time to produce a bi-weekly
timesheet
with a subtotaled week one by workcode.

If there are any angels out there, HERE I AM, Please help me before
I
loose
my tiny, little mind!!!

Thank you for reading my first Novella, I hope you at least got a
chuckle
ot
of it...

Billiam
Click to expand...
Click to expand...
Click to expand...
 
Back
Top