Calculated Fields In Access Queries

[akins4lyfe]

My question is based of Access Database.

I have an application that calculates total hours and breaks for employees within my department.

The database has an action query, "QryTimesheet". This contains three calculated fields, and the criteria i used for displaying results are:

Total Break 1: Round(([Breakin 1]-[Breakout 1])*24,2)
Total Break 2: Round(([Breakin 2]-[Breakout 2])*24,2)
Total Hours: Round(([Logout Time]-[Login Time])*24,1)

These fields have Date/Time datatypes. Exact Time are entered by each employee using a text field & command button on a form.

but i have a problem with the total breaks result format, its coming up in decimal, for example if an employee was on break between 3:06:19 PM - 3:20:27 PM
total breaks output 0.24, im not quite sure if this is 24mins or 24 seconds.

I would prefer my query output results similar to:

1 Hour 20 Minutes or

30 minutes e.tc

Converting those decimals to hrs/minutes and formatted with Hrs Minutes.


Pls Assist

thank you all.

 

[SkipVought]

it is .24 hrs, or more exactly 0.235555556 hours.

If you want the value formatted as HH:MM, I would convert BACK to days (which the given 3:20:27 PM - 3:06:19 PM actually returns).


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[PHV]

A starting point:
Total Break 1: Format([BreakOut 1]-[BreakIn 1], "H\hn\ms\s")

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[akins4lyfe]

thanks PH..

thats exactly what i wanted to do...great suggestion pple!!!

 

[akins4lyfe]

i have a Compile Error on line 4 of my SQL. when i removed the double quotes around "ww"..my query returns empty result....any one knows whats going on here pls

thanks.

strSQL = "SELECT [Member No],[First Name],[Last Name],Dates,[Record Closed] "
strSQL = strSQL & " From tblMember "
strSQL = strSQL & " WHERE (((tblMember.[Record Closed])=No) "
strSQL = strSQL & " AND ((DatePart("ww",[Dates]))=DatePart("ww",Date())"
strSQL = strSQL & " AND ((Year([Dates]))=Year(Date())));"


Me.lstMembers.RowSource = strSQL

 

[akins4lyfe]

I have now fixed this error. apparently VBA does not like " " within SQL with DatePart Syntax. so i replaced those double quotes by single quotes ' ' around the ww.