[AccessD] datediff & format

[Kostas Konstantinidis]

Thanks Gustav

/kostas

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

Hi Kostas

If you wish to sum, you must either group by something or nothing:

SELECT tblEmployeeTime.EmployeeId, FormatHourMinute(Sum([TimeOUT]-[TimeIN]))
AS TotalTime FROM tblEmployeeTime GROUP BY tblEmployeeTime.EmployeeId;

or:

SELECT FormatHourMinute(Sum([TimeOUT]-[TimeIN])) AS TotalTime FROM
tblEmployeeTime;

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

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