Year to Date Query Issue

[Steve]

Hi all-

Thanks in advance for taking time to give me a hand.

I'm having an issue with trying to make a Year to Date function in my
query. I want it to give me the year to date rainfall totals for each
individual site along with historical data and rainfall of the current
month. I can get everything else but the YTD to work. Here's what I
have so far:

SELECT Month([ldate]) AS [Month], Year([ldate]) AS [Year],
wdata.sitename, Avg(wdata.rainfall) AS AvgRainfall,
Max(wdata.rainfall) AS [Historical Hi], Min(wdata.rainfall) AS
[Historical Lo], Max(wdata.rainfall) AS HistoricalYTD, ([YearDate])-
([HistoricalYTD]) AS Deviance, Sum(wdata.rainfall) AS HistRain,
wdata.sitecode, (SELECT Sum(wdata.rainfall) FROM wdata WHERE
wdata.sitename=[Forms]![Excel Rain]![lakeCombo] AND
Year(ldate)=Year(Date()) And Month(ldate)<=Month(Date())) AS YearDate
FROM wdata, HistRain
WHERE (((wdata.ldate) Between [Forms]![Excel Rain]![StartDate] And
[Forms]![Excel Rain]![EndDate]))
GROUP BY Month([ldate]), Year([ldate]), wdata.sitename, wdata.sitecode
ORDER BY Month([ldate]), Year([ldate]);


Thanks in advance for any help any of you give.

-steve-

 

[Michael Gramelspacher]

FROM wdata WHERE
wdata.sitename=[Forms]![Excel Rain]![lakeCombo]

Does this give the year-to-date rainfall?
This method is taken from Hugo Kornelis, SQL Server MVP

SELECT SUM(IIF(DATEDIFF("y",DATEADD("y",DATEDIFF
("y",0,NOW),0),w.rainfall) = 0,
w.rainfall,0))
FROM wdata AS w
WHERE w.sitename = [Forms]![Excel Rain]![lakeCombo];

 

[Michael Gramelspacher]

SELECT SUM(IIF(DATEDIFF("y",DATEADD("y",DATEDIFF
("y",0,NOW),0),w.rainfall) = 0,
w.rainfall,0))
FROM wdata AS w
WHERE w.sitename = [Forms]![Excel Rain]![lakeCombo];

Try:

SELECT SUM(IIF(DATEDIFF("yyyy",DATEADD("yyyy",DATEDIFF
("yyyy",0,NOW),0),w.rainfall) = 0,
w.rainfall,0))
FROM wdata AS w
WHERE w.sitename = [Forms]![Excel Rain]![lakeCombo];