If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Challenging Date Query help needed
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; |
#2
|
|||
|
|||
Challenging Date Query help needed
Billy B wrote:
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()) -- Marsh MVP [MS Access] |
#3
|
|||
|
|||
Challenging Date Query help needed
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 '================================================= === Marshall Barton wrote: Billy B wrote: 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()) |
Thread Tools | |
Display Modes | |
|
|