Challenging Date Query help needed

[Billy B]

I have the following query that displays total distances for each year of my
riding statistics. It works correctly. What I would like to do is change it
to show the statistics for each year up to and including the date the user
runs the query. I have tried using <=Today(). That and all other attempts
have failed. Can someone help me? Thank you very much.

SELECT DISTINCTROW Year(Statistics.StatDate) AS [Year],
Sum(Statistics.Mileage) AS [Timed Miles], Sum(Statistics.[Total Miles]) AS
[Total Miles], Sum(Statistics.ESeconds) AS SumOfESeconds,
Sum(Statistics.TSeconds) AS SumOfTSeconds, Format(Sum([TSeconds]\3600),"00")
AS THrs, Format(Sum([TSeconds] Mod 3600\60),"00") AS TMin,
Format(Sum([TSeconds])\3600,"#") & ":" & Format(Sum([TSeconds])\60 Mod
60,"00") & ":" & Format(Sum([TSeconds]) Mod 60,"00") AS [Total Hours],
Sum(Statistics.Elevation) AS [Total Elevation]
FROM Statistics
GROUP BY Year(Statistics.StatDate)
ORDER BY Year(Statistics.StatDate) DESC;

 

[Marshall Barton]

I have the following query that displays total distances for each year of my
riding statistics. It works correctly. What I would like to do is change it
to show the statistics for each year up to and including the date the user
runs the query. I have tried using <=Today(). That and all other attempts
have failed. Can someone help me? Thank you very much.

SELECT DISTINCTROW Year(Statistics.StatDate) AS [Year],
Sum(Statistics.Mileage) AS [Timed Miles], Sum(Statistics.[Total Miles]) AS
[Total Miles], Sum(Statistics.ESeconds) AS SumOfESeconds,
Sum(Statistics.TSeconds) AS SumOfTSeconds, Format(Sum([TSeconds]\3600),"00")
AS THrs, Format(Sum([TSeconds] Mod 3600\60),"00") AS TMin,
Format(Sum([TSeconds])\3600,"#") & ":" & Format(Sum([TSeconds])\60 Mod
60,"00") & ":" & Format(Sum([TSeconds]) Mod 60,"00") AS [Total Hours],
Sum(Statistics.Elevation) AS [Total Elevation]
FROM Statistics
GROUP BY Year(Statistics.StatDate)
ORDER BY Year(Statistics.StatDate) DESC;

I think you can get that by inserting:

WHERE DatePart("y",StatDate) <= DatePart("y", Date())

 

[John Spencer]

Marshall's solution will work except when leap years are involved and
you have gone beyond Feb 28, then you could end up with some years being
off by one day.

This may be more accurate.

WHERE Format(Statistics.StatDate,"MMDD") <= Format(Date(),"MMDD")



'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

I have the following query that displays total distances for each year of my
riding statistics. It works correctly. What I would like to do is change it
to show the statistics for each year up to and including the date the user
runs the query. I have tried using <=Today(). That and all other attempts
have failed. Can someone help me? Thank you very much.

SELECT DISTINCTROW Year(Statistics.StatDate) AS [Year],
Sum(Statistics.Mileage) AS [Timed Miles], Sum(Statistics.[Total Miles]) AS
[Total Miles], Sum(Statistics.ESeconds) AS SumOfESeconds,
Sum(Statistics.TSeconds) AS SumOfTSeconds, Format(Sum([TSeconds]\3600),"00")
AS THrs, Format(Sum([TSeconds] Mod 3600\60),"00") AS TMin,
Format(Sum([TSeconds])\3600,"#") & ":" & Format(Sum([TSeconds])\60 Mod
60,"00") & ":" & Format(Sum([TSeconds]) Mod 60,"00") AS [Total Hours],
Sum(Statistics.Elevation) AS [Total Elevation]
FROM Statistics
GROUP BY Year(Statistics.StatDate)
ORDER BY Year(Statistics.StatDate) DESC;

I think you can get that by inserting:

WHERE DatePart("y",StatDate) <= DatePart("y", Date())