[AccessD] datediff & format

[Kostas Konstantinidis]

Hi Gustav,

For any reason it doesn't work for me... 
TotalTime: FormatHourMinute(Sum([TimeOut]-[TimeIn]))
returns "You tried to execute a query that does not include the specified expression 'TimeIn' as part of an aggregate function."

May be it happens because of the Greek MS Office version? (Greek date format #dd/mm/yyyy#)

The query is 
SELECT tblEmployeeTime.TimeIN, tblEmployeeTime.TimeOUT, FormatHourMinute(Sum([TimeOUT]-[TimeIN])) AS TotalTime
FROM tblEmployeeTime
ORDER BY tblEmployeeTime.TimeOUT DESC;

Any idea please?


Many thanks
/kostas


-----Original Message-----
From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Gustav Brock
Sent: Friday, May 26, 2017 1:17 PM
To: 'Access Developers discussion and problem solving' <accessd at databaseadvisors.com>
Subject: Re: [AccessD] datediff & format

Hi Kostas

You can use this function:

<code>
Public Function FormatHourMinute( _
  ByVal datTime As Date, _
  Optional ByVal strSeparator As String = ":") _
  As String

' Returns count of days, hours and minutes of datTime ' converted to hours and minutes as a formatted string ' with an optional choice of time separator.
'
' Example:
'   datTime: #10:03# + #20:01#
'   returns: 30:04
'
' 2005-02-05. Cactus Data ApS, CPH.

  Dim strHour       As String
  Dim strMinute     As String
  Dim strHourMinute As String

  strHour = CStr(Fix(datTime) * 24 + Hour(datTime))
  ' Add leading zero to minute count when needed.
  strMinute = Right("0" & CStr(Minute(datTime)), 2)
  strHourMinute = strHour & strSeparator & strMinute

  FormatHourMinute = strHourMinute

End Function
</code>

    TotalTime: FormatHourMinute(Sum([TimeOut]-[TimeIn]))

/gustav
________________________________________
Fra: AccessD <accessd-bounces at databaseadvisors.com> på vegne af Kostas Konstantinidis <kost36 at otenet.gr>
Sendt: 26. maj 2017 12:05:32
Til: 'Access Developers discussion and problem solving'
Emne: Re: [AccessD] datediff & format

Hi Stuart,
I guess I need both of them while I need extra calculation for weekly and monthly sum

Thanks
/kostas

-----Original Message-----
From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Stuart McLachlan
Sent: Friday, May 26, 2017 12:51 PM
To: Access Developers discussion and problem solving <accessd at databaseadvisors.com>
Subject: Re: [AccessD] datediff & format

Is it always within a single day?
or
Can it roll over midnight?  i.e. Can TimeIn be greater than TimeOut?
Can it exceed 24 hours?  If so, do you want 1:02:30 or 26:30 ?

--
Stuart


On 26 May 2017 at 11:55, Kostas Konstantinidis wrote:

> Hi all,
> In a time tracking I use the datediff
> hours: (DateDiff("";[TimeIn];[TimeOut]))/60
>
> but what I really need is to track the time difference result in 
> format as hh:mm
>
> Could anyone help please?
> Thanks
> /kostas
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com