Calculated Field Question

[Ron Weaver]

I downloaded the 2003 Access database "Service Call Management". I am hung up
at one point. The Workorder Labor form has fields, "BillableHours" and
"BillingRate". You manually insert the hours, which is then multiplied times
the billing rate to get the total labor charge. I have tried to automate this
by adding fields "ArriveTime" and "DepartTime' to fill in "BillableHours".
Can anyone tell me how to do this without putting code in "BillableHours", or
give some direction of a work around?
Thanks

 

[Ron Weaver]

Never mind. I finally got it. You can't do dat! It is simple. Just do the
calculation on the form. DAAAAAA!

 

[Dale Fye]

Ron,

I'm not familiar with that particular database, but I think what you will
need to do is put some code in the AfterUpdate event of the ArriveTime and
DepartTime text boxes. Something like:

Private Sub ArriveTime_AfterUpdate

if LEN(ArriveTime & "") = 0 then
msgbox "Enter an arrival time"
me.ArriveTime.setfocus
exit sub
ENDIF

If Len(DepartTime & "") = 0 then
BillableHours = NULL
Else
BillableHours = DateDiff("n", ArriveTime, DepartTime) / 60
Endif

End Sub

The code in the DepartTime AfterUpdate event would be similar to this. This
implies that your billable hours can be a partial hour, and that there is no
"break time" between the ArriveTime and DepartTime.

If you are going to use these two fields to define billable hours, I would
probably lock the BillableHours control so that the value determined in the
AfterUpdate events of ArriveTime and DepartTime could not be over-ridden. Or
would provide some elegant way to override that value.

HTH
Dale

 

[John W. Vinson]

On Mon, 25 Feb 2008 11:25:00 -0800, Ron Weaver

Well... the problem there is that the labor charge on last year's work will
change if you change the BillingRate. This is one case where you DO need to
store the calculated value, using VBA code in the AfterUpdate event.

 

[Ron Weaver]

Dale,
That didn't work. Thanks for trying.
I am going to supply a link to that database and explain the modifications I
made.
Maybe someone will help.
Thanks

 

[Ron Weaver]

Here is the database:
http://office.microsoft.com/en-us/templates/TC010184671033.aspx?CategoryID=CT101426031033
I want to replace [BillableHours] which is manually input, with [ArriveTime]
and [DepartTime] which would automate the time. Any suggestions would be
appreciated.

 

[John W. Vinson]

Here is the database:
http://office.microsoft.com/en-us/templates/TC010184671033.aspx?CategoryID=CT101426031033
I want to replace [BillableHours] which is manually input, with [ArriveTime]
and [DepartTime] which would automate the time. Any suggestions would be
appreciated.

Well, I for one will download and work on your database at my normal
consulting terms. How's your budget? Would you like a copy of my resume and
terms?

Sorry, but that request goes fairly far beyond the norms for this group.

Hint:

If Not IsNull(Me!ArriveTime) And Not IsNull(Me!DepartTime) Then
Me!BillableHours = DateDiff("n", [ArriveTime], [DepartTime]) / 60.
End If

in VBA code on the AfterUpdate event of the Arrivetime and Departtime
textboxes will calculate hours and fractions of an hour between the times
cited and "push" this value into a bound control named BillableHours.

 

[Ron Weaver]

Thanks John,
I will keep working on it. I will try the "Hint" and go from there.
You don't know till you ask.

Here is the database:
http://office.microsoft.com/en-us/templates/TC010184671033.aspx?CategoryID=CT101426031033
I want to replace [BillableHours] which is manually input, with [ArriveTime]
and [DepartTime] which would automate the time. Any suggestions would be
appreciated.

Well, I for one will download and work on your database at my normal
consulting terms. How's your budget? Would you like a copy of my resume and
terms?

Sorry, but that request goes fairly far beyond the norms for this group.

Hint:

If Not IsNull(Me!ArriveTime) And Not IsNull(Me!DepartTime) Then
Me!BillableHours = DateDiff("n", [ArriveTime], [DepartTime]) / 60.
End If

in VBA code on the AfterUpdate event of the Arrivetime and Departtime
textboxes will calculate hours and fractions of an hour between the times
cited and "push" this value into a bound control named BillableHours.

 

[Ron Weaver]

John,
Thank you so much. I believe that did it.

Here is the database:
http://office.microsoft.com/en-us/templates/TC010184671033.aspx?CategoryID=CT101426031033
I want to replace [BillableHours] which is manually input, with [ArriveTime]
and [DepartTime] which would automate the time. Any suggestions would be
appreciated.

Well, I for one will download and work on your database at my normal
consulting terms. How's your budget? Would you like a copy of my resume and
terms?

Sorry, but that request goes fairly far beyond the norms for this group.

Hint:

If Not IsNull(Me!ArriveTime) And Not IsNull(Me!DepartTime) Then
Me!BillableHours = DateDiff("n", [ArriveTime], [DepartTime]) / 60.
End If

in VBA code on the AfterUpdate event of the Arrivetime and Departtime
textboxes will calculate hours and fractions of an hour between the times
cited and "push" this value into a bound control named BillableHours.