A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Challenging Date Query help needed



 
 
Thread Tools Display Modes
  #1  
Old July 6th, 2008, 02:49 PM posted to microsoft.public.access.queries
Billy B
external usenet poster
 
Posts: 42
Default 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  
Old July 6th, 2008, 03:46 PM posted to microsoft.public.access.queries
Marshall Barton
external usenet poster
 
Posts: 5,361
Default 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  
Old July 6th, 2008, 08:40 PM posted to microsoft.public.access.queries
John Spencer
external usenet poster
 
Posts: 2,364
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 09:14 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.