Hope requesting a specific expert is not against the rules.

Trying to find a way to figure elapsed time for a school daycare.
Checked MS --found the article to which you refer in your UA reply in December 2007 .
When trying to use the code provided by MS,
=HoursAndMinutes(Sum([CheckOut]-[CheckIn]))

I get #NAME? or #ERROR depending on whether I use
HoursAnd Minutes or the field name.

You had the following in your reply.
Since daycare isn't going to run over 24 hours, would I put this in the query for the timecard or into the form field control field.

? Format(DateAdd("n",90,0),"Short Time")
01:30

Thanks--

Not Brent .. but try:

= HoursAndMinutes([CheckOut]-[CheckIn])

... but I really don't know what you are after and where you are using the expression.

RDH

-o!
hi Ricky !!!! (IMG:http://www.utteraccess.com/forum/style_emoticons/default/notworthy.gif)

... and a very warm hello to you Crystal ... -o!

RDH

Am trying to figure the hours & minutes that children are in daycare for billing purposes.


Thanks. Had tried = HoursAndMinutes([CheckOut]-[CheckIn])
in the query, and get:

0.0763888888888888 when testing CheckIn as 6:35 AM and CheckOut as 8:25AM

Obviously, I need to do something more. Do I need to somehow incorporate date? Or is there a calculation I need to add at the end of the code? the Help!
putting = HoursAndMinutes([CheckOut]-[CheckIn]) in control for field on form returns #NAME


Also want to say thank you to all of UA. It's only been a short time, and already it feels like you are all old friends.
Thanks!

G’day Cynthia.

The number 0.0763888888888888 when multiplied by 1440 (the number of minutes in a day) returns 110 which is the duration between 6:35 AM and 8:25AM.

Hope that helps.

Regards,
Chris.

First, understand that there are differences between Dates (and Times) with DURATION. In addittion, there are formatting considerations that are confusing as well. It's not that hard as it sounds, though. You are dealing with duration, and in particular, how many hours and minutes that the duration of time was involved.

I won't get too involved with the specifics, but suffice to say that Access and VBA calculates time periods using the unit of day. That means that if 1 = 1 day, and one hour equals 1/24 day, then one hour equals 1/24, equals .04166+.
In your example, you have 1 hour and 50 minutes duration, which indeed comes to .0763888+.

However, you don't need to get that mathematical. Access has a function called the DateDiff function that can return a duration in any date or time format you choose. So the next logical step is to select the smallest date/time element you need. In this case, it is "minute."

NOTE: I notice that Check-In and Checkout times are written in times, without dates. I won't get into the specifics, but many beginning developers separate dates from times, when in reality they should never be separated. However, for the purpose of your particular problem, and considering check-in and checkout times do not overlap midnight, then your calculation can work withough date/times. Using just times is ok in that case.

So, back to your calculation

If CheckIn is 6:35 AM, and CheckOut is 8:25 AM, the calculation is as follows:

Duration = DateDiff("n", [CheckIn], [CheckOut])

In this case, Duration = 110 (minutes).

The NEXT step is to convert 110 minutes to a format of hours:minutes. Keep in mind that 110 is a Single datatype, NOT a date/time datatype. So basically, we need to use two more functions: the Int function and the Mod function.

When the calculation 110/60 is performed, the answer is 1.833333. But you only need the integer part, or "1". Therefore, you need to use the Int function. So:

Hours = Int(110/60) = 1

Next, to get the amount of minutes remaining, use the Mod function as follows:

Minutes = 110 Mod 60 = 50

By the way, although the answer if 50, in some cases the answer will be a single digit. In standard time formats, you need to use a leading zero to indicate the minutes, that is, "2:07" would signify 2 hours and 7 minutes. To do this, use the Format function like this: Format (110/60 Mod 50, "00")

OK...let's tie EVERYTHING together.

FinalAnswer = Int(DateDiff("n", [CheckIn], [CheckOut])/60) & ":" & _
Format(DateDiff("n", [CheckIn], [CheckOut]) Mod 60, "00")

That should help you with your problem.




Edited by: ZapDude on Tue Mar 25 23:19:46 EDT 2008.

thaks Chris.
That helps--a little. Now to turn the minutes into hours & minutes. Dividing by 60 didn't work as I got date 1898 & a time of 3:25.
any other ideas?

Hello Cynthia ---

Before we start, please don't let my wife see your avatar! ... I am perfectly fine with 7! ... (IMG:style_emoticons/default/smirk.gif)

---

On the form where you tried to use HoursAndMinutes() as a control source expression, do you have the fields used in the RecordSource on the form? .. or as Controls on the form? ... also, just to make sure, I am assuming you have the HoursAndMinutes() code in a Standard Module declared as Public correct? ... The reason I ask is that HoursAndMinutes() returns a STRING, and I would not expect it to give you a result of 0.0764. However, as ChrisO points out, the 0.0764 is indeed the elapsed time between the two times.

As I look at what you have, I am assuming the fields CheckIn and CheckOut are fields in the same table, and your goal is to get the elapsed time for a single record (presumably a day). Then, I assume, you hope to sum up those elapsed times at the end of the week (or some other periodic value).

The "trick" I showed with ...
? Format(DateAdd("n",90,0),"Short Time")
01:30

Is merely formating the moment in time that is 90 minutes away from #12:00 AM# (a timeserial value of 0) ...

So ... the elapsed time for one record can be done without a user defined function if you wish .. (note the assumption is that all CheckIn's and CheckOut's are between #12:00 AM# and #11:59:50# ...

=Format(DateAdd("n",DateDiff("n",[CheckIn],[CheckOut]),0),"Short Time")

As a test ...

? Format(DateAdd("n",DateDiff("n",#6:35 AM#,#8:25 AM#),0),"Short Time")
01:50

...

But I personally use a function I have posted before named TimeDiff() ...


The assumption is that the passed times are within a 24hr span, the date component of passed date is pretty much ignored ...

? TimeDiff("n",#1/1/1979 6:35 AM#, #2/2/2008 8:25 AM#)
110 (note the "n" indicates the value is returned in minutes)

So now that you have minutes, you can use the Format() trick to add 110 minutes to #12:00 AM# (or 0) to get the format in hh:mm

=Format(DateAdd("n", TimeDiff("n",[CheckIn],[CheckOut]),0),"Short Time")

As a test ...

? Format(DateAdd("n",TimeDiff("n",#6:35 AM#, #8:25 AM#),0),"Short Time")
01:50

...

Now .. when you want to sum up the elapse time over a week or something, you will have to have each day's elapsed time represented as a numeric value in a specific unit of measure so you can sum it up, then convert the result if need be ... So a query may look something like this:

SELECT AccountID, Sum(TimeDiff("n",[CheckIn],[CheckOut])) As TotalMinutes
FROM tblDailyRecords
WHERE DateOfService Between #1/1/2008# And #1/31/2008 11:59:59 PM#
GROUP BY AccountID

Now that the about can produce a huge amount of minutes, you can use the following function to change your minutes into HH:MM format .... (note that the passed argument is expected to be hours)



So in a control source for a text box (assuming the form/report is bound to a query like the one above) you can have ...

=FormatHHMM([TotalMinutes]/60.0)

As a test ...

? FormatHHMM(1441/60.0)
24:01

....

Hope that helps!!! ...

PS> I think this is the first time I have been requested in the subject line!!! (IMG:style_emoticons/default/smile.gif) ... (IMG:style_emoticons/default/thumbup.gif)


EDITS ADDED >>>

Couple of replies while I was composing ... sorry for any dup info!!

Edited by: datAdrenaline on Wed Mar 26 0:08:02 EDT 2008.

Brent,

Thanks for the help. Re: avatar, have 3 grandkids, and when they’re here, it SEEMS like that many. (IMG:http://www.utteraccess.com/forum/style_emoticons/default/laugh.gif)


In your reply, you asked about a public function in a module:

1. No, I don’t have = HoursAndMinutes([CheckOut]-[CheckIn]) in a module.


2. I did put the following into a module, got the error message: “Expected Identifier”

______________________________________
Public Function TimeDiff(strInterval As String, _

dtStartTime As Date, _

dtStopTime As Date) As Long



TimeDiff = DateDiff(strInterval, #12:00:00 AM#, _

Format(dtStartTime - 1 - dtStopTime, _

"hh:nn:ss"))



End Function
________________________________________


3.When I pasted the following into text box Format property, Form view displayed the code in text box:

=Format(DateAdd("n", TimeDiff("n",[CheckIn],[CheckOut]),0),"Short Time")


3. Pasted this into a new module:

________________________________________
Public Function FormatHHMM(dblHours As Double) As String

'Converts the passed number of hours to HH:MM format. It is not a TIME OF DAY

'it is a length of time.



Dim lngHours As Long

Dim intMinutes As Integer



lngHours = Int(dblHours)

intMinutes = Int(Round((dblHours - lngHours) * 60, 0))



FormatHHMM = Format(CStr(lngHours), "00") & ":" & Format(CStr(intMinutes), "00")



End Function
________________________________________
VB didn’t seem to have any problems with this one (IMG:http://www.utteraccess.com/forum/style_emoticons/default/laugh.gif)


I know you’re trying to do your “real” job, so thanks for your help so far. I really appreciate the code examples, etc. However, I feel guilty, (like I’m cheating” ) so if you would, please explain the “why” for me. (Perhaps you did already, and I need to study your post more.) Have always needed to know “why” in order to understand “how”. (Yes, I was one of those annoying children, who was always asking,“Why?”(IMG:http://www.utteraccess.com/forum/style_emoticons/default/laugh.gif)
Blessings on your day,
Cynthia

Hello Cynthia (By the Way, you have been given one of my favorite names!)

>> 1. No, I don’t have = HoursAndMinutes([CheckOut]-[CheckIn]) in a module.<<

If you wish to use the HoursAndMinutes() code, you will have to put that code into a module ... it can go in the same module as TimeDiff() and FormatHHMM() ... I actually have a module named "mod_TimeFunctions" where are place all my functions that have to with time ...

>> 2. I did put the following into a module, got the error message: “Expected Identifier” <<

The TimeDiff() code did not work because some browsers show the code in dbl-spaced text ... so when you see the line continuation characters {the underscore (_)} the line following should be immediate below the line above ... The FormatHHMM() worked fine because there are not any line continuation characters, so the dbl-space had no effect on you.

So ... this is what TimeDiff() *should* look like (editing directly in the post window instead of a copy/paste from VBA, when you copy/paste from this ... it all comes out as one line! ... so you will need to edit the result of a copy/paste ... )



Just for consistency ... here is what FormatHHMM() looks like in my module ...



>> 3.When I pasted the following into text box Format property, Form view displayed the code in text box: <<

The expression does NOT go in the Format property of the control, that should be blank ... once you get TimeDiff() into your Module correctly, you will put the expression in the "Control Source" property of the text box control.

=Format(DateAdd("n", TimeDiff("n",[CheckIn],[CheckOut]),0),"Short Time")


....

>> Have always needed to know “why” in order to understand “how”. (Yes, I was one of those annoying children, who was always asking,“Why?”(IMG:http://www.utteraccess.com/forum/style_emoticons/default/laugh.gif) <<

I try to teach my kids to know "why", as I feel it is the best path for learning ... but occassionaly there is something good about the "push a button and it works" philosophy! ... (IMG:http://www.utteraccess.com/forum/style_emoticons/default/laugh.gif)

>> Blessings on your day, <<
Thank you! ... and to you as well!

....

Please let me know how all this goes!

Sorry it took so long to get back to this.
Grandkids for the day, & son & daughter-in-law in from Colorado, and trying to get the PowerPoint ready for worship on Sunday. Don't think my brain is working too well tonight.

Did try to understand your posts better.
Created the module as you suggested. Not sure how to then specify module. Do I need to create a macro to "start" the module? Have tried to create macros in the past. Not sure that I did them correctly.

Thanks for your help. Think I'll call it a night and try again in the morning.

>> Not sure how to then specify module. Do I need to create a macro to "start" the module? <<

No need to start, or specify the module (.. there are occassionally needs to specify which Module a procedure resides in, but that lesson is generally saved for a later time in the learning curve! ..) ... Access just "knows" they are there! ...

When you call a User Defined Function (aka: UDF) {ie: TimeDiff() or FormatHHMM()} ... Access will search through its list of functions that are part of the Access program, if there is no match, Access will then search through all the user created Modules for the function being called .. if NO MATCH is found, you get an the "#Name?" error in your text box because Access can not find the function NAME you are calling ...

For example, a text box with a Control Source property set to:

=TimeDiff("n",[CheckIn],[CheckOut])

As soon as the form opens, Access will try to evaluate your expression. Your expression calls the function TimeDiff() and passes the values in the controls named "CheckIn" and "CheckOut" to the arguments required by the function. Access then goes on the hunt for the function named "TimeDiff" .. it will search through the library of functions that are coded into the Access program, then (if Access can't find "TimeDiff") it will look through the Modules for a Public module named "TimeDiff" .. when Access finds it, Access will use that function to acquire a result, and display that result in the text box that made the call

A note about scope. Procedures (a Function and a Sub are procedures, there only difference is that functions return a value to the caller, a Sub does not) can have different levels of "visibility" to possible callers ...

Public: Can be called by anything that has an ability to call a function or sub. A function declared as Public can even be called by another database application IF the caller "references" the host database as a searchable library for procedures. Note: a Control Source property or an expression in a query will only call functions, they can not call Subs.

Example: <the procedures in this post are all declared as Public>

Private: Can be called by a caller in the same scope ... that means if a function/sub is Private in a standard module, then the only callers that can see/use the private procudure have to be in the same module as the private procedure.

Example:
mod_TimeFunctions
-Public Function TimeDiff(....) As Long
-Private Function GetToday() As Date

mod_RecordsetFunctions
-Public Sub GotoBookmark()

TimeDiff can call GetToday because they are in the same module (aka: at the same scope), but GotoBookmark can not call GetToday because GotoBookmark is in a different scope, and thus can not "see" GetToday in order to use it.

There is another level of scope identified as "Friend" ... but that is a "later in the learning curve" too (IMG:http://www.utteraccess.com/forum/style_emoticons/default/smirk.gif) ...

---

Hope all this helps!!

PS: >> Grandkids for the day, & son & daughter-in-law in from Colorado, and trying to get the PowerPoint ready for worship on Sunday. <<

... That said to a person in my situation {as stated 7 kids ... 6 boys (12 through 3) / 1 girl (1.5) .. plus we mustn't forget the dog, cat, chinchilla, and bald rat!!} ... I say this ...

"Bring in on!!!" .... and ... "I ain't skeerd" ... and ... (getting in touch with my feminine side) ... "calgon -- take me away!" ... but most importantly ... "Pray .... Pray REAL Hard!!!" ... (IMG:http://www.utteraccess.com/forum/style_emoticons/default/thumbup.gif) ... enjoy those grandkids!!! ...

Brent,

Maybe I’m just really tired or maybe I’m in over my head or maybe I’m making it harder than it needs to be or I’m trying to reinvent a wheel… OR I’m terrified of starting a project from scratch
In the past I have converted MS Access templates from Customer Orders into a Parochial School program that keeps track of all student & parent info, including Registration, Tuition, & payments, census, health, tuition by classroom, etc. I was the school secretary and just did it for myself, because I had used similar programs in the past and wanted to take advantage of what access could help me do.

Have recently converted a membership template into a membership program for our church that tracks attendance, activity, committees, boards, offerings, families, age groups etc.

Now I’m trying to convert MS access template for Time and Billing into a program for the same parochial school’s before & after school daycare.

My request is would you also download this Time & Billing Template, look at it and then at the changes I’ve listed below and then point me in a direction. Right now I feel a bit like my avatar 
I’ve attached my db.

So here’s what I’ve done:

Downloaded Template
Deleted Client Table
Imported the Parents Table renamed it Clients, renamed FamilyID field to ClientID

Deleted Projects Table
Imported the Students Table renamed it Projects, renamed StudentID field to ProjectID, FamilyID Field to ClientID

Left the Employee Table with the thought that the day care director could have employees clock in and out (if I could figure out how to convert the amount of time into hours and minutes)

Left Form names the same-changing only labels that would be visible to daycare workers
Updated controls on all forms

Changed Report controls and visible labels where necessary
Created module for time

Now I’m wondering if I should have used the Employee table for students rather than the Projects table.
All Time references, whether in query, form, or report, are linked to both Employees AND Projects.

This template’s Timecard Form seems to be set up on the “honor” system of entering hours as there are none of the queries that I have been picking your brain trying to understand.

Sorry about the previous post. Was having a REALLY big pity party. Shouldn't have sent the invitation to join. (IMG:http://www.utteraccess.com/forum/style_emoticons/default/blush.gif)

Am getting a "Missing Operatorin LEFT JOIN" error

SELECT DISTINCTROW [Projects].ProjectID,[Projects].LastName,[Projects].FirstName,
LEFT JOIN [Projects].ProjectsID, ON [Time Card Hours].ProjectID, WHERE [Time Card Hours].ProjectID=[Projects].ProjectID ORDER BY Projects.LastName;

Since this is for The "Time Card Hours Form", should it be "LEFT JOIN [Time Card Hours].ProjectID ON Time Card Hours].ProjectID" ?

Thanks,
Cynthia

... First ... you need a FROM clause ...

SELECT <field list> FROM SomeTable LEFT JOIN SomeOtherTable ON SomeTable.SomeField = SomeOtherTable.SomeRelatedField

Short on time ... Hope this helps ...