Show current year without parameter

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
Try this in the Criteria row under your date field:

Between DateSerial(Year(Date()), 1,1) And DateSerial(Year(Date()), 12, 31)
 
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)));
 
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.

Wylie C said:
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 said:
Try this in the Criteria row under your date field:

Between DateSerial(Year(Date()), 1,1) And DateSerial(Year(Date()), 12,
31)
Click to expand...
Click to expand...
 
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)
 
Back
Top