Payroll Query

[visidro]

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

 

[Ken Sheridan]

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]

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

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

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

 

[Ken Sheridan]

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");

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

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

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

 

[visidro]

Thanks Ken,

still getting an error, "Undefined function "TIMESUM" in expression.

I have both Functions in the Modules section of the database .... why cant
the query find it?

Victor.

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");

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

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

:

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

 

[Ken Sheridan]

Victor:

This sometimes happens when functions are pasted into modules, though I
don't know why. First make sure that the module in which you have put the
functions has not been given the same name as one of the functions, or any
other for that matter. I usually tag module names with 'bas', e.g.
basDateTimeStuff to ensure no confusion with other object names. If that's
OK then try opening the database from a command prompt with the /decompile
switch. Then compile the code from the VBA menu bar. If that doesn't work
try changing the names of the functions to something slightly different; that
generally seems to do the trick if all else fails. You'll need to change the
references to the function names in their own body code and in the query too
of course.

I have tested both functions and the query against some dummy data, so am
confident that it all does work.

Ken Sheridan
Stafford, England

Thanks Ken,

still getting an error, "Undefined function "TIMESUM" in expression.

I have both Functions in the Modules section of the database .... why cant
the query find it?

Victor.

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");

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

:

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

:

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

 

[visidro]

Changed the name of the functions and recompiled, that worked just fine!.
Thank you very much Ken for your help very much appreciated!

take care
victor

Victor:

This sometimes happens when functions are pasted into modules, though I
don't know why. First make sure that the module in which you have put the
functions has not been given the same name as one of the functions, or any
other for that matter. I usually tag module names with 'bas', e.g.
basDateTimeStuff to ensure no confusion with other object names. If that's
OK then try opening the database from a command prompt with the /decompile
switch. Then compile the code from the VBA menu bar. If that doesn't work
try changing the names of the functions to something slightly different; that
generally seems to do the trick if all else fails. You'll need to change the
references to the function names in their own body code and in the query too
of course.

I have tested both functions and the query against some dummy data, so am
confident that it all does work.

Ken Sheridan
Stafford, England

Thanks Ken,

still getting an error, "Undefined function "TIMESUM" in expression.

I have both Functions in the Modules section of the database .... why cant
the query find it?

Victor.

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");

:

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

:

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

:

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