Sum of hours by year

[hoachen]

I have a table contain columns a long Date (Saturday, 02 August, 2014) and hours.



My query is to sum only those in year after 2013, but query on sum up all. I have tried below:

Total Days Taken: Sum(IIf([Date]>2013 and [Hours]<0,[Hours],0)/-8)


Please help!

[June7]

Is the date field actually a date/time type? Regardless of the format setting on a date/time field, the actually value is unchanged.

Why don't you just apply a filter to exclude all records prior to 2014? If you use an aggregate function in query the query must be a TOTALS (GROUP BY) query. Post the SQL statement for analysis.

Consider building a report and use report Grouping & Sorting features. This will allow display of detail records and summary calcs.

Date is a reserved word and should not use reserved words as names.

[hoachen]

Thanks for your reply.
Here is my sql query. If i add a column for the year, it will show all the rows instead of group by the member.

SELECT [Member - vacation].Name, Sum([Member - vacation].Hours) AS [Total Hours Remain], Sum([Hours]/8) AS [Total Days Remain], Sum(IIf([Hours]<0,[Hours],0)/-8) AS [Total Days Taken]
FROM [Member - vacation]
GROUP BY [Member - vacation].Name;

[June7]

Try:

SELECT [Member - vacation].Name, Sum([Member - vacation].Hours) AS [Total Hours Remain], Sum([Hours]/8) AS [Total Days Remain], Sum(IIf([Hours]<0,[Hours],0)/-8) AS [Total Days Taken]
FROM [Member - vacation]
WHERE Year([Date])=2014
GROUP BY [Member - vacation].Name;