Show current year without parameter

[Wylie C]

I have a query that uses Statdate from a table. Is there a way to have the
query show the current year statistics without having to update the year in
the query definitions or using a parameter query?
Thank you.

 

[Allen Browne]

Try this in the Criteria row under your date field:

Between DateSerial(Year(Date()), 1,1) And DateSerial(Year(Date()), 12, 31)

 

[Wylie C]

Allen,
I copied your code to the query and the values did not show, nor the year. I
am pasting a copy of the SQL statement for your review.

SELECT TOP 1 Min(Statistics.[Odometer Start]) AS Firstdate,
Max([Statistics].[Odometer Ending]) AS Lastdate, [Lastdate]-[Firstdate] AS
[Total Miles],
Sum(Abs(Format(Date(),"yyyymm")=Format([Statdate],"yyyymm"))*[Mileage]) AS
[Month to Date],
Sum(Abs(Format(Date(),"yyyyww")=Format([Statdate],"yyyyww"))*[Mileage]) AS
[Week to Date]
FROM Statistics
WHERE (((Year([StatDate])) Between DateSerial(Year(Date()),1,1) And
DateSerial(Year(Date()),12,31)));

 

[Allen Browne]

Drop the Year() around StatDate in your WHERE clause, i.e.:
WHERE [StatDate] Between DateSerial(Year(Date()),1,1)
And DateSerial(Year(Date()),12,31)));

DateSerial() builds an actual date value.
The query optimizer only calls this once, and the clause is constructed so
that it can take advantage of any index you have on the StatDate field, so
that's about the most efficient solution you can get.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Allen,
I copied your code to the query and the values did not show, nor the year.
I
am pasting a copy of the SQL statement for your review.

SELECT TOP 1 Min(Statistics.[Odometer Start]) AS Firstdate,
Max([Statistics].[Odometer Ending]) AS Lastdate, [Lastdate]-[Firstdate] AS
[Total Miles],
Sum(Abs(Format(Date(),"yyyymm")=Format([Statdate],"yyyymm"))*[Mileage]) AS
[Month to Date],
Sum(Abs(Format(Date(),"yyyyww")=Format([Statdate],"yyyyww"))*[Mileage]) AS
[Week to Date]
FROM Statistics
WHERE (((Year([StatDate])) Between DateSerial(Year(Date()),1,1) And
DateSerial(Year(Date()),12,31)));

Try this in the Criteria row under your date field:

Between DateSerial(Year(Date()), 1,1) And DateSerial(Year(Date()), 12,
31)

 

[Svein Ludvigsen]

You can try
Like "*" & Year(Date()) & "*"

Svein

 

[Rick Brandt]

If StatDate does not include time...

WHERE DateField
BETWEEN DateSerial(Year(Date), 1, 1)
AND DateSerial(Year(Date), 12, 31)

If StatDate does include the time...

WHERE DateField
BETWEEN DateSerial(Year(Date), 1, 1)
AND DateSerial(Year(Date), 13, 1)