Variable not returning/passing value

[Guest]

I am very new to VBA (6.0). Unfortunately, I am running MS Access 97. I
have had only a brief introduction to VBA in my MS App college course. I am
trying to get a value in a calculated control from the form's query to round
up or down depending on the value. This is a timesheet database.

Form Information:

The main form is based on a query that limits the dates to the current pay
period. There are two sub forms on the main form. The first sub form is
limited by a query that shows week 1 in the current pay period. The second
form is limited by a query that shows week 2 in the current pay period. The
main form’s header houses the Employee Name, EmployeeID, Department, Team,
and CostCenter fields (all fields from tblEMpInfo). The DailyHoursWorked
field is calculated by the sub form’s query (additional info below).

I have tried several different things to get the unbound control
(DailyHoursWorked) to display the rounded value. I am not sure what I am
doing wrong. It is like everything works great until it hits the Select Case
statement. I have the code in an Access module. I am calling this Function
from the AfterUpdate Events of the TimeIn, and TimeOut Fields on my form in
order for it to recalculate the DailyHoursWorked field after the employee
changes the values in the TimeIn and TimeOut fields. There are 4 TimeIn and
4 TimeOut fields (TimeIn1, TimeOut1, Time In2, etc.) that are stored in a
table named tblTimeCard. The form is based on a query, that combines fields
from tblTimeCard and tblEmpInfo. The EmployeeID is the Primary Key in
tblEmpInfo. The primary key in tblTimeCard is EmployeeID and Date. These
tables have a one-to-many relationship based on EmployeeID, with referential
integrity enforced. The one side of the relationship is, of course, the
tblEmpInfo.

I have been trying to debug my code (submitted below). The I used a
breakpoint of:

lngDlyHrsWrked =
Forms!frmDailyTimeWTotals.Form!subfrmDailyTimeWTotalsWeek1!DailyHoursWorked

The Immediate window shows me the following at the breakpoint shown above:

Expression Value Type

lngDlyHrsWrked 0 Double
RoundDailyHrsWorked Empty Variant/Empty


Then when I Step Over the above breakpoint it shows me:

Expression Value Type

lngDlyHrsWrked 7.98333333333334 Double
RoundDailyHrsWorked Empty Variant/Empty

So, I changed my code as follows:

Public Function RoundDailyHrsWorked(dblDlyHrsWrked As Double) As Double

dblDlyHrsWrked =
Forms!frmDailyTimeWTotals.Form!subfrmDailyTimeWTotalsWeek1!DailyHoursWorked

Select Case dblDlyHrsWrked

Case Is <= 0.1167
dblDlyHrsWrked = 0#
RoundDailyHrsWorked = dblDlyHrsWrked
Case Is <= 0.35
dblDlyHrsWrked = 0.25
RoundDailyHrsWorked = dblDlyHrsWrked
Case Is <= 0.6167
dblDlyHrsWrked = 0.5
RoundDailyHrsWorked = dblDlyHrsWrked
Case Is <= 0.8667
dblDlyHrsWrked = 0.75
RoundDailyHrsWorked = dblDlyHrsWrked
Case Is <= 1.1167
dblDlyHrsWrked = 1#
RoundDailyHrsWorked = dblDlyHrsWrked
Case Is <= 1.35
dblDlyHrsWrked = 1.25
RoundDailyHrsWorked = dblDlyHrsWrked
Case Is <= 1.6167
dblDlyHrsWrked = 1.5
RoundDailyHrsWorked = dblDlyHrsWrked
Case Is <= 1.8667
dblDlyHrsWrked = 1.75
RoundDailyHrsWorked = dblDlyHrsWrked
Case Is <= 2.1167
dblDlyHrsWrked = 2#
RoundDailyHrsWorked = dblDlyHrsWrked
Case Is <= 2.35
dblDlyHrsWrked = 2.25
RoundDailyHrsWorked = dblDlyHrsWrked
Case Is <= 2.6167
dblDlyHrsWrked = 2.5
RoundDailyHrsWorked = dblDlyHrsWrked
Case Is <= 2.8667
dblDlyHrsWrked = 2.75
RoundDailyHrsWorked = dblDlyHrsWrked
Case Is <= 3.1167
dblDlyHrsWrked = 3#
RoundDailyHrsWorked = dblDlyHrsWrked
Case Is <= 3.35
dblDlyHrsWrked = 3.25
RoundDailyHrsWorked = dblDlyHrsWrked
Case Is <= 3.6167
dblDlyHrsWrked = 3.5
RoundDailyHrsWorked = dblDlyHrsWrked
Case Is <= 3.8667
dblDlyHrsWrked = 3.75
RoundDailyHrsWorked = dblDlyHrsWrked
Case Is <= 4.1167
dblDlyHrsWrked = 4#
RoundDailyHrsWorked = dblDlyHrsWrked
Case Is <= 4.35
dblDlyHrsWrked = 4.25
RoundDailyHrsWorked = dblDlyHrsWrked
Case Is <= 4.6167
dblDlyHrsWrked = 4.5
RoundDailyHrsWorked = dblDlyHrsWrked
Case Is <= 4.8667
dblDlyHrsWrked = 4.75
RoundDailyHrsWorked = dblDlyHrsWrked
Case Is <= 5.1167
dblDlyHrsWrked = 5#
RoundDailyHrsWorked = dblDlyHrsWrked
Case Is <= 5.35
dblDlyHrsWrked = 5.25
RoundDailyHrsWorked = dblDlyHrsWrked
Case Is <= 5.6167
dblDlyHrsWrked = 5.5
RoundDailyHrsWorked = dblDlyHrsWrked
Case Is <= 5.8667
dblDlyHrsWrked = 5.75
RoundDailyHrsWorked = dblDlyHrsWrked
Case Is <= 6.1167
dblDlyHrsWrked = 6#
RoundDailyHrsWorked = dblDlyHrsWrked
Case Is <= 6.35
dblDlyHrsWrked = 6.25
RoundDailyHrsWorked = dblDlyHrsWrked
Case Is <= 6.6167
dblDlyHrsWrked = 6.5
RoundDailyHrsWorked = dblDlyHrsWrked
Case Is <= 6.8667
dblDlyHrsWrked = 6.75
RoundDailyHrsWorked = dblDlyHrsWrked
Case Is <= 7.1167
dblDlyHrsWrked = 7#
RoundDailyHrsWorked = dblDlyHrsWrked
Case Is <= 7.35
dblDlyHrsWrked = 7.25
RoundDailyHrsWorked = dblDlyHrsWrked
Case Is <= 7.6167
dblDlyHrsWrked = 7.5
RoundDailyHrsWorked = dblDlyHrsWrked
Case Is <= 7.8667
dblDlyHrsWrked = 7.75
RoundDailyHrsWorked = dblDlyHrsWrked
Case Is <= 8.1167
dblDlyHrsWrked = 8#
RoundDailyHrsWorked = dblDlyHrsWrked
Case Is <= 8.35
dblDlyHrsWrked = 8.25
RoundDailyHrsWorked = dblDlyHrsWrked
Case Is <= 8.6167
dblDlyHrsWrked = 8.5
RoundDailyHrsWorked = dblDlyHrsWrked
Case Is <= 8.8667
dblDlyHrsWrked = 8.75
RoundDailyHrsWorked = dblDlyHrsWrked
Case Is <= 9.1167
dblDlyHrsWrked = 9#
RoundDailyHrsWorked = dblDlyHrsWrked
Case Is <= 9.35
dblDlyHrsWrked = 9.25
RoundDailyHrsWorked = dblDlyHrsWrked
Case Is <= 9.6167
dblDlyHrsWrked = 9.5
RoundDailyHrsWorked = dblDlyHrsWrked
Case Is <= 9.8667
dblDlyHrsWrked = 9.75
RoundDailyHrsWorked = dblDlyHrsWrked
Case Is <= 10.1167
dblDlyHrsWrked = 10#
RoundDailyHrsWorked = dblDlyHrsWrked
Case Is <= 10.35
dblDlyHrsWrked = 10.25
RoundDailyHrsWorked = dblDlyHrsWrked
Case Is <= 10.6167
dblDlyHrsWrked = 10.5
RoundDailyHrsWorked = dblDlyHrsWrked
Case Is <= 10.8667
dblDlyHrsWrked = 10.75
RoundDailyHrsWorked = dblDlyHrsWrked
Case Is <= 11.1167
dblDlyHrsWrked = 11#
RoundDailyHrsWorked = dblDlyHrsWrked
Case Is <= 11.35
dblDlyHrsWrked = 11.25
RoundDailyHrsWorked = dblDlyHrsWrked
Case Is <= 11.6167
dblDlyHrsWrked = 11.5
RoundDailyHrsWorked = dblDlyHrsWrked
Case Is <= 11.8667
dblDlyHrsWrked = 4.75
RoundDailyHrsWorked = dblDlyHrsWrked
Case Is <= 12.1167
dblDlyHrsWrked = 12#
RoundDailyHrsWorked = dblDlyHrsWrked
Case Is <= 12.35
dblDlyHrsWrked = 12.25
RoundDailyHrsWorked = dblDlyHrsWrked
Case Is <= 12.6167
dblDlyHrsWrked = 12.5
RoundDailyHrsWorked = dblDlyHrsWrked
Case Is <= 12.8667
dblDlyHrsWrked = 12.75
RoundDailyHrsWorked = dblDlyHrsWrked
Case Is <= 13.1167
dblDlyHrsWrked = 13#
RoundDailyHrsWorked = dblDlyHrsWrked
End Select

End Function

Now the Immediate window shows:

Expression Value Type

dblDlyHrsWrked 7.98333333333334 Double
RoundDailyHrsWorked 0 Double

Why isn’t it pulling the value of dblDlyHrsWrked? I have the following code
in the After_Update events of the TimeIn and TimeOut fields:

RoundDailyHrsWorked (DailyHoursWorked)

I realize that the above is incorrect, but when I put anything else I get an
error about the Type or and error that says Expected: list separator.

I have tried the following code in the AfterUpdate events:

RoundDailyHrsWorked (dblDlyHrsWrked)
RoundDailyHrsWorked (dblDlyHrsWrked As Double)
RoundDailyHrsWorked (dblDlyHrsWrked As Double) As Double
Call RoundDailyHrsWorked (dblDlyHrsWrked)

What am I missing? I really need to get past this so I can move on to other
issues. My deadline for having this timesheet database ready to roll out to
users is 05/20/05. Any help is greatly appreciated. Do I need additional
code in my After_Update events? Is it possible to display the rounded
numbers in a calculated control using code?

Tiffany

Original Code:

Public Function RoundDailyHrsWorked()

Dim lngDlyHrsWrked As Long

lngDlyHrsWrked = Forms!subfrmDailyTimeWTotalsWeek1.DailyHoursWorked.Value

Select Case lngDlyHrsWrked

Case Is <= 0.1167
lngDlyHrsWrked = 0#
Case Is <= 0.35
lngDlyHrsWrked = 0.25
Case Is <= 0.6167
lngDlyHrsWrked = 0.5
Case Is <= 0.8667
lngDlyHrsWrked = 0.75
Case Is <= 1.1167
lngDlyHrsWrked = 1#
Case Is <= 1.35
lngDlyHrsWrked = 1.25
Case Is <= 1.6167
lngDlyHrsWrked = 1.5
Case Is <= 1.8667
lngDlyHrsWrked = 1.75
Case Is <= 2.1167
lngDlyHrsWrked = 2#
Case Is <= 2.35
lngDlyHrsWrked = 2.25
Case Is <= 2.6167
lngDlyHrsWrked = 2.5
Case Is <= 2.8667
lngDlyHrsWrked = 2.75
Case Is <= 3.1167
lngDlyHrsWrked = 3#
Case Is <= 3.35
lngDlyHrsWrked = 3.25
Case Is <= 3.6167
lngDlyHrsWrked = 3.5
Case Is <= 3.8667
lngDlyHrsWrked = 3.75
Case Is <= 4.1167
lngDlyHrsWrked = 4#
Case Is <= 4.35
lngDlyHrsWrked = 4.25
Case Is <= 4.6167
lngDlyHrsWrked = 4.5
Case Is <= 4.8667
lngDlyHrsWrked = 4.75
Case Is <= 5.1167
lngDlyHrsWrked = 5#
Case Is <= 5.35
lngDlyHrsWrked = 5.25
Case Is <= 5.6167
lngDlyHrsWrked = 5.5
Case Is <= 5.8667
lngDlyHrsWrked = 5.75
Case Is <= 6.1167
lngDlyHrsWrked = 6#
Case Is <= 6.35
lngDlyHrsWrked = 6.25
Case Is <= 6.6167
lngDlyHrsWrked = 6.5
Case Is <= 6.8667
lngDlyHrsWrked = 6.75
Case Is <= 7.1167
lngDlyHrsWrked = 7#
Case Is <= 7.35
lngDlyHrsWrked = 7.25
Case Is <= 7.6167
lngDlyHrsWrked = 7.5
Case Is <= 7.8667
lngDlyHrsWrked = 7.75
Case Is <= 8.1167
lngDlyHrsWrked = 8#
Case Is <= 8.35
lngDlyHrsWrked = 8.25
Case Is <= 8.6167
lngDlyHrsWrked = 8.5
Case Is <= 8.8667
lngDlyHrsWrked = 8.75
Case Is <= 9.1167
lngDlyHrsWrked = 9#
Case Is <= 9.35
lngDlyHrsWrked = 9.25
Case Is <= 9.6167
lngDlyHrsWrked = 9.5
Case Is <= 9.8667
lngDlyHrsWrked = 9.75
Case Is <= 10.1167
lngDlyHrsWrked = 10#
Case Is <= 10.35
lngDlyHrsWrked = 10.25
Case Is <= 10.6167
lngDlyHrsWrked = 10.5
Case Is <= 10.8667
lngDlyHrsWrked = 10.75
Case Is <= 11.1167
lngDlyHrsWrked = 11#
Case Is <= 11.35
lngDlyHrsWrked = 11.25
Case Is <= 11.6167
lngDlyHrsWrked = 11.5
Case Is <= 11.8667
lngDlyHrsWrked = 4.75
Case Is <= 12.1167
lngDlyHrsWrked = 12#
Case Is <= 12.35
lngDlyHrsWrked = 12.25
Case Is <= 12.6167
lngDlyHrsWrked = 12.5
Case Is <= 12.8667
lngDlyHrsWrked = 12.75
Case Is <= 13.1167
lngDlyHrsWrked = 13#
End Select

End Function

 

[Guest]

:
*** SNIP
*** SNIP

I have tried several different things to get the unbound control
(DailyHoursWorked) to display the rounded value. I am not sure what I am
doing wrong. It is like everything works great until it hits the Select Case
statement. I have the code in an Access module. I am calling this Function
from the AfterUpdate Events of the TimeIn, and TimeOut Fields on my form in
order for it to recalculate the DailyHoursWorked field after the employee
changes the values in the TimeIn and TimeOut fields. There are 4 TimeIn and
4 TimeOut fields (TimeIn1, TimeOut1, Time In2, etc.) that are stored in a
table named tblTimeCard. ***SNIP
***SNIP

What am I missing? I really need to get past this so I can move on to other
Tiffany

***SNIP

The Function should look like this:

'**** begin code *************
Public Function RoundDailyHrsWorked(dblHrsWrked As Double) As Double
' also could use:
'Public Function RoundDailyHrsWorked(dblHrsWrked As Single) As Single

Select Case dblHrsWrked

Case Is <= 0.1167
RoundDailyHrsWorked = 0
Case Is <= 0.35
RoundDailyHrsWorked = 0.25
Case Is <= 0.6167
RoundDailyHrsWorked = 0.5
Case Is <= 0.8667
RoundDailyHrsWorked = 0.75

 

[Guest]

SteveS:

I hope you are still watching this post....I tried the suggestions that you
gave me below, but it isn't working. When I alter the TimeIn1 it changes the
value in the field I am editing in the TimeIn1 field to 6:00 am no matter
what time I have placed in the field. The other fields display the correct
time, but it is still not rounding. So, I changed the code you gave me for
the After_Update event to:

Me.DailyHoursWorked = RoundDailyHrsWorked(Me.DailyHoursWorked)

because the DailyHoursWorked field is the field that I want to display the
rounded number. But I am getting the following error:

Run-time error '3377':

Field 'DailyHoursWorked' is based on an expression and can't be edited.

Is there anyway to get around this? Thanks for your help!

Tiffany

 

[Guest]

SteveS:

I hope you are still watching this post....I tried the suggestions that you
gave me below, but it isn't working. When I alter the TimeIn1 it changes the
value in the field I am editing in the TimeIn1 field to 6:00 am no matter
what time I have placed in the field. The other fields display the correct
time, but it is still not rounding. So, I changed the code you gave me for
the After_Update event to:

Me.DailyHoursWorked = RoundDailyHrsWorked(Me.DailyHoursWorked)

because the DailyHoursWorked field is the field that I want to display the
rounded number. But I am getting the following error:

Run-time error '3377':

Field 'DailyHoursWorked' is based on an expression and can't be edited.

Is there anyway to get around this? Thanks for your help!

Tiffany

Hi Tiffany,

Yes, I'm still hanging around.

Delete the "TimeIn1_AfterUpdate()" code. I wasn't very clear (happens a
lot). Sorry.

I meant it to be an example of how to use a function to return a value. In
your case, *IF* TimeIn1 was a number (not a time) such as 5.123, then using
the function "RoundDailyHrsWorked", 5.25 would be returned and put in the
TimeIn1 control.


Are you entering times (8:00, 13:00) in the 4 TimeIn and 4 TimeOut fields
(TimeIn1, TimeOut1, Time In2, etc.)? How are you calculating the differences?

What is the control source for the control "DailyHoursWorked"? I'm guessing
it is a calculation. And how is "DailyHoursWorked" recalculated?


The Error message said

"Field 'DailyHoursWorked' is based on an expression and can't be edited";

there are a couple of ways to solve this.

1) wrap the calculation for "DailyHoursWorked" in the "RoundDailyHrsWorked"
function, ie if the calculation (expression) for the control is:
=([TimeOut1] - [TimeIn1])*24 + ([TimeOut2] - [TimeIn2])*24 + ([TimeOut3]
- [TimeIn3])*24 + ([TimeOut4] - [TimeIn4])*24

then to "round" it, change the calculation to (example):
=RoundDailyHrsWorked(([TimeOut1] - [TimeIn1])*24 + ([TimeOut2] -
[TimeIn2])*24 + ([TimeOut3] - [TimeIn3])*24 + ([TimeOut4] - [TimeIn4])*24 )


2) You could remove the calculation from the control source of
"DailyHoursWorked" and use an event (in the after update events of the time
out controls, maybe in the on current event,...) to do the calculation, then
push the value into the control.

I would use the first one. Less chance for errors to creep in.

HTH

 

[Guest]

Are you entering times (8:00, 13:00) in the 4 TimeIn and 4 TimeOut fields
(TimeIn1, TimeOut1, Time In2, etc.)?

Yes. Side note: We are not using military time when entering the times.

How are you calculating the differences?

DailyHoursWorked:
Nz(([TimeOut1]-[TimeIn1])*24)+Nz(([TimeOut2]-[TimeIn2])*24)+Nz(([TimeOut3]-[TimeIn3])*24)+Nz(([TimeOut4]-[TimeIn4])*24)

What is the control source for the control "DailyHoursWorked"? I'm guessing
it is a calculation.

Yes, it is a calculation….the above noted formula is an expression that was
placed in the form’s underlying query. The expression is named
DailyHoursWorked, and appears as a field on the form.

And how is "DailyHoursWorked" recalculated?

I have placed the following code in the Got_Focus event of each TimeIn and
TimeOut event starting with the TimeOut1 field.

Private Sub TimeOut1_GotFocus()

Refresh

Exit_TimeOut1_GotFocus:
Exit Sub

Err_TimeOut1_GotFocus:
MsgBox Err.Description
Resume Exit_TimeOut1_GotFocus

End Sub

The TimeIn3, TimeOut3, TimeIn4, and TimeOut4 fields are used only if someone
has to leave more than once for lunch. So, these fields are not always used,
but in case they are, the code above is used to refresh those fields as well.

Now, an ignorant question for you (yes, another one)….please excuse my lack
of knowledge on this subject…..

Where would I place:

=RoundDailyHrsWorked(([TimeOut1] - [TimeIn1])*24 + ([TimeOut2] -
[TimeIn2])*24 + ([TimeOut3] - [TimeIn3])*24 + ([TimeOut4] - [TimeIn4])*24 )?

What event? I am guessing it would not be an event but the new control
source for DailyHoursWorked? That will be what I try first.

I can’t tell you how much I appreciate your help. Thank you! Thank you!
Thank you!

Tiffany

SteveS:

I hope you are still watching this post....I tried the suggestions that you
gave me below, but it isn't working. When I alter the TimeIn1 it changes the
value in the field I am editing in the TimeIn1 field to 6:00 am no matter
what time I have placed in the field. The other fields display the correct
time, but it is still not rounding. So, I changed the code you gave me for
the After_Update event to:

Me.DailyHoursWorked = RoundDailyHrsWorked(Me.DailyHoursWorked)

because the DailyHoursWorked field is the field that I want to display the
rounded number. But I am getting the following error:

Run-time error '3377':

Field 'DailyHoursWorked' is based on an expression and can't be edited.

Is there anyway to get around this? Thanks for your help!

Tiffany

Hi Tiffany,

Yes, I'm still hanging around.

Delete the "TimeIn1_AfterUpdate()" code. I wasn't very clear (happens a
lot). Sorry.

I meant it to be an example of how to use a function to return a value. In
your case, *IF* TimeIn1 was a number (not a time) such as 5.123, then using
the function "RoundDailyHrsWorked", 5.25 would be returned and put in the
TimeIn1 control.


Are you entering times (8:00, 13:00) in the 4 TimeIn and 4 TimeOut fields
(TimeIn1, TimeOut1, Time In2, etc.)? How are you calculating the differences?

What is the control source for the control "DailyHoursWorked"? I'm guessing
it is a calculation. And how is "DailyHoursWorked" recalculated?


The Error message said

"Field 'DailyHoursWorked' is based on an expression and can't be edited";

there are a couple of ways to solve this.

1) wrap the calculation for "DailyHoursWorked" in the "RoundDailyHrsWorked"
function, ie if the calculation (expression) for the control is:
=([TimeOut1] - [TimeIn1])*24 + ([TimeOut2] - [TimeIn2])*24 + ([TimeOut3]
- [TimeIn3])*24 + ([TimeOut4] - [TimeIn4])*24

then to "round" it, change the calculation to (example):
=RoundDailyHrsWorked(([TimeOut1] - [TimeIn1])*24 + ([TimeOut2] -
[TimeIn2])*24 + ([TimeOut3] - [TimeIn3])*24 + ([TimeOut4] - [TimeIn4])*24 )


2) You could remove the calculation from the control source of
"DailyHoursWorked" and use an event (in the after update events of the time
out controls, maybe in the on current event,...) to do the calculation, then
push the value into the control.

I would use the first one. Less chance for errors to creep in.

HTH

 

[Guest]

How are you calculating the differences?

DailyHoursWorked:
Nz(([TimeOut1]-[TimeIn1])*24)+Nz(([TimeOut2]-[TimeIn2])*24)+Nz(([TimeOut3]-[TimeIn3])*24)+Nz(([TimeOut4]-[TimeIn4])*24)

What is the control source for the control "DailyHoursWorked"? I'm guessing
it is a calculation.

Yes, it is a calculation….the above noted formula is an expression that was
placed in the form’s underlying query. The expression is named
DailyHoursWorked, and appears as a field on the form.

Excellent! Now the calculation is a "field" is the "virtual table" (aka
query).

And how is "DailyHoursWorked" recalculated?

I have placed the following code in the Got_Focus event of each TimeIn and
TimeOut event starting with the TimeOut1 field.

Private Sub TimeOut1_GotFocus()

Refresh

Exit_TimeOut1_GotFocus:
Exit Sub

Err_TimeOut1_GotFocus:
MsgBox Err.Description
Resume Exit_TimeOut1_GotFocus

End Sub

This code is not needed. Any time you change the record, the form is
automatically updated.

The TimeIn3, TimeOut3, TimeIn4, and TimeOut4 fields are used only if someone
has to leave more than once for lunch. So, these fields are not always used,
but in case they are, the code above is used to refresh those fields as well.

What happens if someone "forgets" to enter an TimeOut or the TimeOut is
before the TimeIn
(ie some one means to put in 5:00PM, but enters 5:00AM) or they enter a
second tome in TimeIn3, skipping TimeIn2?

Just wondering out loud..... don't mind me..

Now, an ignorant question for you (yes, another one)….please excuse my lack
of knowledge on this subject…..

Not ignorant, just inexperienced :-D - but not as inexperienced as you
think.

Where would I place:

=RoundDailyHrsWorked(([TimeOut1] - [TimeIn1])*24 + ([TimeOut2] -
[TimeIn2])*24 + ([TimeOut3] - [TimeIn3])*24 + ([TimeOut4] - [TimeIn4])*24 )?

Nowhere. See below

What event? I am guessing it would not be an event but the new control
source for DailyHoursWorked? That will be what I try first.

None.

Remember above where I said the calc is where it should be? Just wrap the
calculation (in the query) with your function. Now the "DailyHoursWorked"
column in the query should look like:

DailyHoursWorked:
RoundDailyHrsWorked(Nz(([TimeOut1]-[TimeIn1])*24)+Nz(([TimeOut2]-[TimeIn2])*24)+Nz(([TimeOut3]-[TimeIn3])*24)+Nz(([TimeOut4]-[TimeIn4])*24))

Then the control source for the text box on the form would be
"DailyHoursWorked", just like the "timein1" control source is the "TimeIn1"
field in the query. The summing and rounding is done in the query... sweeeeet!

****

Also, I looked at your code for "rounding" the hours and I re-wrote it......

You really only have 4 cases:

Case Is <= 0.1167
RoundDailyHrs = ....
Case Is <= 0.35
RoundDailyHrs = .....
Case Is <= 0.6167
RoundDailyHrs = ....
Case Is <= 0.8667
RoundDailyHrs = .....

so if you were able to break the hours worked into a whole number and a
fraction, you could compare the fractional part, then add back in the whole
part, it would look like this:


'****begin code ******
Public Function RoundDailyHrsWorked(dblDlyHrsWrked As Double) As Double
Dim f_Hrs As Single ' fractional hour part

Dim WholeHrs As Integer ' whole hours

' get the whole hours. converting a Single data type to an Interger
' automatically deletes the fractional hours
WholeHrs = dblDlyHrsWrked

' get the fractional hour
f_Hrs = dblDlyHrsWrked - WholeHrs

' find the case and add the whole number back
Select Case f_Hrs
Case Is <= 0.1167
RoundDailyHrsWorked = 0 + WholeHrs
Case Is <= 0.35
RoundDailyHrsWorked = 0.25 + WholeHrs
Case Is <= 0.6167
RoundDailyHrsWorked = 0.5 + WholeHrs
Case Is <= 0.8667
RoundDailyHrsWorked = 0.75 + WholeHrs
End Select
End Function

'****** end code ****


HTH

 

[Guest]

Thank You!! Thank You!! Thank You!! It is working great now. I cannot
tell you how much I appreciate your help. NO ONE has ever taken the time to
help me as much as you have with a question that I posted out here. You are
AWESOME! Thanks again!

How are you calculating the differences?

DailyHoursWorked:
Nz(([TimeOut1]-[TimeIn1])*24)+Nz(([TimeOut2]-[TimeIn2])*24)+Nz(([TimeOut3]-[TimeIn3])*24)+Nz(([TimeOut4]-[TimeIn4])*24)

What is the control source for the control "DailyHoursWorked"? I'm guessing
it is a calculation.

Yes, it is a calculation….the above noted formula is an expression that was
placed in the form’s underlying query. The expression is named
DailyHoursWorked, and appears as a field on the form.

Excellent! Now the calculation is a "field" is the "virtual table" (aka
query).

And how is "DailyHoursWorked" recalculated?

I have placed the following code in the Got_Focus event of each TimeIn and
TimeOut event starting with the TimeOut1 field.

Private Sub TimeOut1_GotFocus()

Refresh

Exit_TimeOut1_GotFocus:
Exit Sub

Err_TimeOut1_GotFocus:
MsgBox Err.Description
Resume Exit_TimeOut1_GotFocus

End Sub

This code is not needed. Any time you change the record, the form is
automatically updated.

The TimeIn3, TimeOut3, TimeIn4, and TimeOut4 fields are used only if someone
has to leave more than once for lunch. So, these fields are not always used,
but in case they are, the code above is used to refresh those fields as well.

What happens if someone "forgets" to enter an TimeOut or the TimeOut is
before the TimeIn
(ie some one means to put in 5:00PM, but enters 5:00AM) or they enter a
second tome in TimeIn3, skipping TimeIn2?

Just wondering out loud..... don't mind me..

Now, an ignorant question for you (yes, another one)….please excuse my lack
of knowledge on this subject…..

Not ignorant, just inexperienced :-D - but not as inexperienced as you
think.

Where would I place:

=RoundDailyHrsWorked(([TimeOut1] - [TimeIn1])*24 + ([TimeOut2] -
[TimeIn2])*24 + ([TimeOut3] - [TimeIn3])*24 + ([TimeOut4] - [TimeIn4])*24 )?

Nowhere. See below

What event? I am guessing it would not be an event but the new control
source for DailyHoursWorked? That will be what I try first.

None.

Remember above where I said the calc is where it should be? Just wrap the
calculation (in the query) with your function. Now the "DailyHoursWorked"
column in the query should look like:

DailyHoursWorked:
RoundDailyHrsWorked(Nz(([TimeOut1]-[TimeIn1])*24)+Nz(([TimeOut2]-[TimeIn2])*24)+Nz(([TimeOut3]-[TimeIn3])*24)+Nz(([TimeOut4]-[TimeIn4])*24))

Then the control source for the text box on the form would be
"DailyHoursWorked", just like the "timein1" control source is the "TimeIn1"
field in the query. The summing and rounding is done in the query... sweeeeet!

****

Also, I looked at your code for "rounding" the hours and I re-wrote it......

You really only have 4 cases:

Case Is <= 0.1167
RoundDailyHrs = ....
Case Is <= 0.35
RoundDailyHrs = .....
Case Is <= 0.6167
RoundDailyHrs = ....
Case Is <= 0.8667
RoundDailyHrs = .....

so if you were able to break the hours worked into a whole number and a
fraction, you could compare the fractional part, then add back in the whole
part, it would look like this:


'****begin code ******
Public Function RoundDailyHrsWorked(dblDlyHrsWrked As Double) As Double
Dim f_Hrs As Single ' fractional hour part

Dim WholeHrs As Integer ' whole hours

' get the whole hours. converting a Single data type to an Interger
' automatically deletes the fractional hours
WholeHrs = dblDlyHrsWrked

' get the fractional hour
f_Hrs = dblDlyHrsWrked - WholeHrs

' find the case and add the whole number back
Select Case f_Hrs
Case Is <= 0.1167
RoundDailyHrsWorked = 0 + WholeHrs
Case Is <= 0.35
RoundDailyHrsWorked = 0.25 + WholeHrs
Case Is <= 0.6167
RoundDailyHrsWorked = 0.5 + WholeHrs
Case Is <= 0.8667
RoundDailyHrsWorked = 0.75 + WholeHrs
End Select
End Function

'****** end code ****


HTH

 

[Guest]

I was so excited that the code was rounding I forgot to answer your question
about what happens if a user neglects to enter the one of the TimeIn or
TimeOut fields. So, I experimented with this a little because you peeked my
curiosity to make sure it was functioning properly.

If they skip one, it will not include the incompleted section in the
calculation. So, if they enter a TimeIn of 7:00 am, a TimeOut of 11:00, and
another Time Out of 5:00 p, but skip entering when they returned from lunch
the amount in the DailyHoursWorked field will show only 4 hours.

If they enter "AM" instead of "PM" or vice versa it will calculate the
hours based on the times entered and show that they worked say 20 hours
instead of 8.

I could not think of anything to do that would handle this any better. How
about you?

Also, you are gonna love this, but I discoverd by during the data
verification process that the rounding is working, but only to a point. For
example, I entered the following time info:

In1: 7:19 am, Out1: 9:25 am, In2: 10:30 am, Out2: 11:56 am,
In3: 1:07 pm, Out3: 3:08 pm, In4: 3:25 pm, Out4: 5:23 pm,

The value displayed in the DailyHoursWorked is showing up as 8.00 hours.

The true value is 7.52 hours, which should by all rights round to 7.50
hours. Also, when you calculate it based on actual minutes or using rounding
it comes up to 7.5 hours. The in and out sets come out as follows

set 1 = 2.00 hours OR 2 hours, 6 min.
set 2 = 1.50 hours OR 1 hour, 26 min.
set 3 = 2.00 hours OR 2 hours, 1 min.
set 4 = 2.00 hours OR 1 hour, 58 min.

Total= 7.50 OR 7 hours 31 min. (31 / 60 = 0.52)

So, I played with it a little and tried entering the following:

In1: 8:00 am, Out1: 11:45 am. and it showed: 4.00 instead of 3.75

I checked the code and the formula but everything looks correct (I copied it
directly from this posting, and doubled checked everything to be sure I
didn't miss anything and that there wasn't a typo). I could not find
anything wrong with it. I did change one Case statement fom <= 0.3500 to <=
0.3667, which corrected the rounding to 0.25 by one minute, which was an
oversight on my part. .3500=21. (for example: 5:21) The cut off we wanted
was the 22nd minute of hour (5:22) should still round to 0.25) and that seems
to work fine.

I tested every minute starting with minute 30 and anything from say 11:30 on
up rounds to the next whole hour. For example:

In1: 8:00 am, Out1: 11:30 am. shows: 4.00 instead of 3.50

I don't understand why it would round up to a whole hour from what clearly
should be the half hour or 0.50. Also, why would it skip the 0.75 part of an
hour altogether? I know! It is possessed? I know you are probably tired of
this posting, but if you could see your way to helping me one last time, I
would be eternally grateful.

Sincerely,

Tiffany

 

[Guest]

I was so excited that the code was rounding I forgot to answer your question
about what happens if a user neglects to enter the one of the TimeIn or
TimeOut fields. So, I experimented with this a little because you peeked my
curiosity to make sure it was functioning properly.

If they skip one, it will not include the incompleted section in the
calculation. So, if they enter a TimeIn of 7:00 am, a TimeOut of 11:00, and
another Time Out of 5:00 p, but skip entering when they returned from lunch
the amount in the DailyHoursWorked field will show only 4 hours.

If they enter "AM" instead of "PM" or vice versa it will calculate the
hours based on the times entered and show that they worked say 20 hours
instead of 8.

I could not think of anything to do that would handle this any better. How
about you?

******** To minimize confusion, see my next post...

*** <snip>

In1: 8:00 am, Out1: 11:45 am. and it showed: 4.00 instead of 3.75

I checked the code and the formula but everything looks correct (I copied it
directly from this posting, and doubled checked everything to be sure I
didn't miss anything and that there wasn't a typo). I could not find
anything wrong with it. I did change one Case statement fom <= 0.3500 to <=
0.3667, which corrected the rounding to 0.25 by one minute, which was an
oversight on my part. .3500=21. (for example: 5:21) The cut off we wanted
was the 22nd minute of hour (5:22) should still round to 0.25) and that seems
to work fine.

I tested every minute starting with minute 30 and anything from say 11:30 on
up rounds to the next whole hour. For example:

In1: 8:00 am, Out1: 11:30 am. shows: 4.00 instead of 3.50

I don't understand why it would round up to a whole hour from what clearly
should be the half hour or 0.50. Also, why would it skip the 0.75 part of an
hour altogether? I know! It is possessed? I know you are probably tired of
this posting, but if you could see your way to helping me one last time, I
would be eternally grateful.

It's not possessed and you are not crazy!
In my playing - oops, I mean experimenting - with the code, I made two
errors (I hate it when I do that....) :-< Sorry


Assuming you are using the shortened code, first, change this:

' get the whole hours. converting a Single data type to an Interger
' automatically deletes the fractional hours
WholeHrs = dblDlyHrsWrked

to this (should have had the Int() function):

' get the whole hours.
WholeHrs = Int(dblDlyHrsWrked)

What was happening was if the fractional part of [dblDlyHrsWrked] was < .5 ,
WholeHrs would get the number rounded down and if it was > .5 , it would
round up to the next whole number.


Next, the Select Case needs two more lines. After

 

[Guest]

I could not think of anything to do that would handle this any better. How
about you?

Here is another function that has a little validation in it.

PLEASE TRY THIS ON A COPY (BACKUP) OF YOUR DATABASE!!! Just in case you
don't like it or something gies wrong....

In a new (standard) module paste the following code: (watch for line wrap)

'*** BEGIN CODE ********

Option Compare Database
Option Explicit

'******************************************************
' Declarations section of the module
'******************************************************
Const Factor = 10000

'=====================================================
' TruncCC is designed to be used in expressions
' and calculated controls on forms and reports.
'=====================================================
Function TruncCC(X)
TruncCC = Int(X * Factor) / Factor
End Function

Public Function RoundDailyHrsWorked_2(T1In, T1out, T2In, T2Out, T3In, T3Out,
T4In, T4Out) As Single

Dim hours As Single
Dim f_Hrs As Single ' fractional hour part

Dim WholeHrs As Integer ' whole hours

hours = 0
' check that times are filled in and time out is after time in
If IsDate(T1In) And IsDate(T1out) And Not IsNull(T1In) And Not
IsNull(T1out) And (T1In < T1out) Then
hours = hours + TruncCC((T1out - T1In) * 24)
End If

If IsDate(T2In) And IsDate(T2Out) And Not IsNull(T2In) And Not
IsNull(T2Out) And (T2In < T2Out) Then
hours = hours + TruncCC((T2Out - T2In) * 24)
End If

If IsDate(T3In) And IsDate(T3Out) And Not IsNull(T3In) And Not
IsNull(T3Out) And (T3In < T3Out) Then
hours = hours + TruncCC((T3Out - T3In) * 24)
End If

If IsDate(T4In) And IsDate(T4Out) And Not IsNull(T4In) And Not
IsNull(T4Out) And (T4In < T4Out) Then
hours = hours + TruncCC((T4Out - T4In) * 24)
End If

' get the whole hours.
WholeHrs = Int(hours)

' get the fractional hour
f_Hrs = hours - WholeHrs

Select Case f_Hrs
Case Is <= 0.1167
RoundDailyHrsWorked_2 = 0 + WholeHrs
Case Is <= 0.3667
RoundDailyHrsWorked_2 = 0.25 + WholeHrs
Case Is <= 0.6167
RoundDailyHrsWorked_2 = 0.5 + WholeHrs
Case Is <= 0.8667
RoundDailyHrsWorked_2 = 0.75 + WholeHrs
Case Else
RoundDailyHrsWorked_2 = 1 + WholeHrs
End Select
End Function

'*** END CODE ********

Paste this in a column of the query:

RndDailyHrs:
RoundDailyHrsWorked_2([TimeIn1],[TimeOut1],[Timein2],[Timeout2],[Timein3],[Timeout3],[Timein4],[Timeout4])


And finally, change the control source of the text box for Total hours
worked to:

RndDailyHrs



The function checks to make sure that the times are date/time type, not null
and
TimeOut is after TimeIn.

You could add a message box if the times were backward...

Example:
If IsDate(T1In) And IsDate(T1out) And Not IsNull(T1In) And Not
IsNull(T1out) And (T1In < T1out) Then
hours = hours + TruncCC((T1out - T1In) * 24)
Else
MsgBox "ERR: Time Out is before Time In. Please check the times"
End If


I did mention to try this on a copy, right???

HTH

 

[Guest]

The rounding is working perfectly now. I really appreciate your help! I
would never have gotten this done without it. I have not tried the code you
sent me for the validation, but I really appreciate it. I am going to give
it a shot tomorrow (in a backup, of course). I really don't do anything
unless I do it in a backup first. ;-)

Thank you so much for all of your help!

Tiffany

I was so excited that the code was rounding I forgot to answer your question
about what happens if a user neglects to enter the one of the TimeIn or
TimeOut fields. So, I experimented with this a little because you peeked my
curiosity to make sure it was functioning properly.

If they skip one, it will not include the incompleted section in the
calculation. So, if they enter a TimeIn of 7:00 am, a TimeOut of 11:00, and
another Time Out of 5:00 p, but skip entering when they returned from lunch
the amount in the DailyHoursWorked field will show only 4 hours.

If they enter "AM" instead of "PM" or vice versa it will calculate the
hours based on the times entered and show that they worked say 20 hours
instead of 8.

I could not think of anything to do that would handle this any better. How
about you?

******** To minimize confusion, see my next post...

*** <snip>

In1: 8:00 am, Out1: 11:45 am. and it showed: 4.00 instead of 3.75

I checked the code and the formula but everything looks correct (I copied it
directly from this posting, and doubled checked everything to be sure I
didn't miss anything and that there wasn't a typo). I could not find
anything wrong with it. I did change one Case statement fom <= 0.3500 to <=
0.3667, which corrected the rounding to 0.25 by one minute, which was an
oversight on my part. .3500=21. (for example: 5:21) The cut off we wanted
was the 22nd minute of hour (5:22) should still round to 0.25) and that seems
to work fine.

I tested every minute starting with minute 30 and anything from say 11:30 on
up rounds to the next whole hour. For example:

In1: 8:00 am, Out1: 11:30 am. shows: 4.00 instead of 3.50

I don't understand why it would round up to a whole hour from what clearly
should be the half hour or 0.50. Also, why would it skip the 0.75 part of an
hour altogether? I know! It is possessed? I know you are probably tired of
this posting, but if you could see your way to helping me one last time, I
would be eternally grateful.

It's not possessed and you are not crazy!
In my playing - oops, I mean experimenting - with the code, I made two
errors (I hate it when I do that....) :-< Sorry


Assuming you are using the shortened code, first, change this:

' get the whole hours. converting a Single data type to an Interger
' automatically deletes the fractional hours
WholeHrs = dblDlyHrsWrked

to this (should have had the Int() function):

' get the whole hours.
WholeHrs = Int(dblDlyHrsWrked)

What was happening was if the fractional part of [dblDlyHrsWrked] was < .5 ,
WholeHrs would get the number rounded down and if it was > .5 , it would
round up to the next whole number.


Next, the Select Case needs two more lines. After
.
.
Case Is <= 0.8667
RoundDailyHrsWorked = 0.75 + WholeHrs

Add these two lines:

Case Else
RoundDailyHrsWorked = 1 + WholeHrs

to handle Cases Greater Than 0.8667 and Less Than 1.0