Compare 2007 and 2008 - count records.

[SpookiePower]

I would like to get the records from my table, from
01.01.2007 until today's date. What I mean is, that
the date today is January 3, so I want to get the records
from January 1 2007 until January 3 2007.

My code looks like this -

SELECT *
FROM TReport
WHERE Date BETWEEN '01-01-2007' AND 'dateToday-2007'

I don't know how to take out only the date (without the year)
from a date field like this (03-01-2008 06:20:00)

date(daReportCreated) returns 03-01-2008.

Can someone help me ?

 

[John Spencer]

One method
SELECT *
FROM TReport
WHERE [Date] BETWEEN #01-01-2007# AND
DateSerial(2007,Month(Date()),Day(Date())

That works as long as you are using an .mdb and your date field is a
datetime field. If you are using an Access Project (ADO) then post back
with that information.

You could also use this method (again with an .mdb)
SELECT *
FROM TReport
WHERE [Date] Between #1/1/2007# and DateAdd("yyyy",-1, Date())
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Ron2006]

And if next year you want to get the same but for 2008 but not have to
change the code every year:

SELECT *
FROM TReport
WHERE [Date] Between DateSerial(year(date())-1,12,31) and
DateAdd("yyyy",-1, Date())

Ron

 

[Ron2006]

Note: In the next table that you design DO NOT name the date field
"Date" That is a reserved word and can cause you problems if the
whatever code you are writing can't figure out which you want OR it
thinks it knows but it is actually the wrong one.

Ron

 

[SpookiePower]

Thanks to both of you.
I'll take a look at it tomorrow.

 

[SpookiePower]

Thanks to both of you again.
Both of your helps work for me