Calculating last year

[Al]

I have a text field that has 2 digit year format like (07,06,05) I want to
filter for this year and last year. I need to use "Date()" for generic
criteria due to the fact that next year will be 08. I need the query to
always filter for the current year and the year before without having to go
to the query design to change the year digits.
I used the following:Format(Date(),"yy") Or
Format(DatePart("yyyy",Date()-1),"yy") but this is returning (07 and 05) what
happened to 06? can someone help.
the data is imported from somewhere else where the format is like that
(07,06, etc) therefore access assigns a text field for this data. I would
like to work under this conditions unless it is impossible to achieve what I
need then I will have to change the field to data/time and change the date
format.
thanks
Al

 

[KARL DEWEY]

Date()-1 is yesterday - not last year.
DateAdd("yyyy",-1,Date()) is one year ago.
Format(DateAdd("yyyy",-1,Date()),"yy") gives you 06 for last year.

 

[John Spencer]

Format(Date(),"yy") - Current Year

Format(DateAdd("yyyy"-1,Date()),"yy") -Prior Year

Or
Format((Year(Date())-1) Mod 100,"00") - Prior year


Your expression, was
Format(DatePart("yyyy",Date()-1),"yy")

DatePart("yyyy", Date() -1) should return the current year number except on
January 1 when it would return the prior year.
Format(2006,"yy") will return 05 for the date 6/28/1905 which is the 2006th
day after 12/30/1899 (the zero day).

HTH
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[John Spencer]

Typo in the formula. Missed a comma in the DateAdd function.

Format(DateAdd("yyyy",-1,Date()),"yy") -Prior Year


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Al]

Karl/John,
thanks it worked.
Al

Typo in the formula. Missed a comma in the DateAdd function.

Format(DateAdd("yyyy",-1,Date()),"yy") -Prior Year


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..