RE: Payroll Query

Victor:

Mea culpa. I missed the from clause from the query:

SELECT FORMAT([payroll table].[work Date], "yyyy mm") AS [Work Month],
TIMESUM(SUM(GETTIME([payroll table].[work Date]))) AS [Total Monthly Hours],
SUM([Payroll Table].[Total Pay]) AS [Total Monthly Pay]
FROM [YourTableNameGoesHere]
GROUP BY FORMAT(([payroll table].[work Date]), "yyyy mm");

"visidro" wrote:

Thank you very much for your reply Ken

Yes the text column is in the format hh:mm and no there are no null values,
also the total time will exceed 23:59 as this will be for a group of
employees.

I have added the two functions and are experiencing a syntax error: "...
reserved work argument name misspelled or missing, or punctuation ..."
here is the query:

SELECT FORMAT([payroll table].[work Date], "yyyy mm") AS [Work Month],
TIMESUM(SUM(GETTIME([payroll table].[work Date]))) AS [Total Monthly Hours],
SUM([Payroll Table].[Total Pay]) AS [Total Monthly Pay]
GROUP BY FORMAT(([payroll table].[work Date]), "yyyy mm");

can you see what the error might be or what am I missing?

thanks again
victor

"Ken Sheridan" wrote:

Victor:

You don't say what format your Total Hours text column is in but if its in a
format hh:nn such as 7:24 (for 7 hours 24 minutes) you can use the CDate
function to return it as a Date/Time data type provided that that no value
exceeds 23:59.

You can use the following function to show the sum of the date/time values
in the format hh:nn:ss

Public Function TimeSum(dblTotalTime As Double) As String

Const HOURSINDAY = 24
Dim lngHours As Long
Dim strMinutesSeconds As String
Dim strDaysHours As String

'get number of hours
lngHours = Int(dblTotalTime) * HOURSINDAY + _
Format(dblTotalTime, "h")

' get minutes and seconds
strMinutesSeconds = Format(dblTotalTime, ":nn:ss")

TimeSum = lngHours & strMinutesSeconds

End Function

So a query to Sum of the total hours and total pay columns would be along
these lines:

SELECT FORMAT[Date], "yyyy mm") AS [Work Month],
TIMESUM(SUM(CDATE([Total Hours]))) AS [Total Monthly Time],
SUM([Total Pay]) AS [Total Monthly Pay]
GROUP BY FORMAT[Date], "yyyy mm");

If, however the Total Hours values can exceed 23:59, e.g. if it represents
the total hours for a group of employees per day so could be 123:45 for
instance, then you'd need to parse the text value before converting it to a
date/time value. This can be done with the following function:

Public Function GetTime(strTime As String) As Date

' accepts time as string in format #hh:nn
' i.e time can exceed 23:59
' returns time as date/time data type

Const DATEZERO As Date = #12:00:00 AM#

If Left(strTime, InStr(strTime, ":") - 1) \ 24 = 0 Then
GetTime = CDate(strTime)
Else
GetTime = _
CDate((DATEZERO + Left(strTime, InStr(strTime, ":") - 1) \ 24) & _
" " & Left(strTime, InStr(strTime, ":") - 1) Mod 24 & ":" & _
Mid(strTime, InStr(strTime, ":") + 1))
End If

End Function

So the query would be:

SELECT FORMAT([Date], "yyyy mm") AS [Work Month],
TIMESUM(SUM(GETTIME([Date]))) AS [Total Monthly Hours],
SUM([Total Pay]) AS [Total Monthly Pay]
GROUP BY FORMAT([Date], "yyyy mm");

You could of course use a different format to group by and return the month
in, but the above has the advantage of sorting in the correct chronological
order. If the data spans more than one year, however, the year must be
included in the formatting to distinguish between the same months in
different years.

BTW I'd recommend not using Date as a column name. It’s the name of a built
in function, so is best avoided. Something specific like [Work Date] is
better.

The Total Hours column cannot be Null of course, so if a date has no hours
worked and is represented in the table the Total Hours would be 00:00, which
it would be appropriate to set as the columns DefaultValue property.

Incidentally you might notice that if you use the GetTime function on your
text values without using the TimeSum function to sum the values you'll get
individual dates around the start of the 20th century for times over 23:59 as
the result. this is because Access uses 30 December 1899 00:00:00 as the
origin for its date/time data type so a 'time value' is in reality a
date/time value counting from that point of origin. Under the skin it’s a 64
bit floating point number with the integer part representing the days and the
fractional part the times of day.

Ken Sheridan
Stafford, England

"visidro" wrote:

I would like to create a query that totals two fields based on date.
Add and show the total hours worked and total pay for each month.

The date field is a long date/time field
the total hours is a text field
the total pay is a currency field.

I would really appreciate help with the query code.
thank you very much
victor

.

Relevant Pages

  • RE: Payroll Query
    ... I have tested both functions and the query against some dummy data, ... Yes the text column is in the format hh:mm and no there are no null values, ... function to return it as a Date/Time data type provided that that no value ... Public Function TimeSumAs String ...
    (microsoft.public.access.queries)
  • Re: DCOUNT format question
    ... > When I enter your string into the control on the form, ... >> Yes, your format is incorrect. ... >>>I can get this to work in a totals query but not on a form. ... >>> initials ...
    (microsoft.public.access.forms)
  • RE: Payroll Query
    ... I missed the from clause from the query: ... Yes the text column is in the format hh:mm and no there are no null values, ... function to return it as a Date/Time data type provided that that no value ... Public Function TimeSumAs String ...
    (microsoft.public.access.queries)
  • Re: Access not accepting functions in queries
    ... Simon, change the last line of your query statement to: ... Judging from your Format() expression, you wanted the date only, so use Date. ... Assuming that your Date field is a Date/Time type, you have now asked JET to match a date against a string. ...
    (microsoft.public.access.queries)
  • Re: formatting a calculated number field on a form
    ... > A query in an MDB file CAN call a user-defined function. ... > function name is Age and it returns a double. ... The user will certainly "notice" if a patient was born on Jan ... I just want control over the *display format* of a number ...
    (microsoft.public.access.forms)
Loading