Dynamic Date Query

[dbtunr]

I have a table tracking user entries and I want to build a few queries to show the sum of a particular field for the current year, last year and two years back.

Currenty I do this using >=#1/1/2008# for the current year query and >#12/31/2006 and <#1/1/2008# for last years query. The problem with this is that the query and the forms that display its results must be updated every year.

I tried to use >Year(now()) for the current year and
>year(now()-2) and <year(now()) for last year. however that does not work.

Any suggestions?

Thanks

 

[PWise]

=year(now()) for this year
=year(now())-1 for last year
=year(now())-2 for 2 year Ago

 

[lespaul]

what's the whole query? are you using the YEAR function on your date field too?

Leslie

Have you met Hardy Heron?

 

[dbtunr]

I tried to use >=Year(Now()) for the current year, However it returns all of the dates from when the log was started back in 2006. If I use >=#1/1/2008# for the current year it will retrieve only the records from 2008 which is what I want.

The Query is looking only at two fields Date and Amount. The date is entered in the form by the user as a medium date and is stored in the table like 1/1/2008 ot 3/15/2008.

The amount is stored as currency.

I have a form that displays the result of this query using a text box with =Sum([Amount]) as its control source.

 

[PWise]

try

year([date])=year(now()) for this year
year([date])=year(now())-1 for last year
year([date])=year(now())-2 for 2 year Ago

BTW it is A bad idea to call a field date

 

[dbtunr]

That works great!!

Thanks