query will not work between years

[Chet Martin]

trying to fix a problem in a club membership program
the query uses Date and Year function
(DatePart("m",Date$())+2) DatePart("yyyy",Date$())-10
the query is to find members that have been members for 10 years, using the
current month and year minus 10
the query works until Nov & Dec when the year needs to jump to the next year

 

[Ron2006]

I am not quite sure what logic you are placing around the year and
month comparison (why are you adding 2 to the month?)

However IF you feel you need to add 2 to the month then at that same
time you will HAVE to add 1 to the year AND subract 12 from the month
IF IF IF the original month is November or December.

A month of 11 plus 2 will give you a month of 13 which will NOT
match any calander that I can think of.

Ron.

 

[Ron2006]

RBrandt's second solution has the same problem, I believe, because it
will derive a month 13.

Ron

 

[Dirk Goldgar]

RBrandt's second solution has the same problem, I believe, because it
will derive a month 13.

You'd be surprised. DateSerial can accept month and day numbers outside the
normal bounds.

?DateSerial(Year(Date())-10, Month(Date())+1, 1)
1/1/1999

?DateSerial(2008, 13, 1)
1/1/2009

?DateSerial(2008, 18, 45)
7/15/2009

 

[Ron2006]

If your logic requires either adding or subtracting or adding from/to
month, then you should

get the month by using month(datediff("m",-2,date()))
and the year would have to be year(datediff("m",-2,date()))


Ron

 

[Ron2006]

If your logic requires either adding or subtracting or adding from/to
month, then you should

get  the month by using   month(datediff("m",-2,date()))
and the year would have to be   year(datediff("m",-2,date()))

Ron

I stand corrected on the DateSerial aspect. I have never used it and
so when I read the code, my mind read it as date not date serial.

Live and Learn.

Thank you.

Ron

 

[Beetle]

It's a very handy little function. For example;

DateSerial(Year(Date()), Month(Date()) + 1, 0)

will return the last day of the current month.

 

[Chet Martin]

the data is in two fields

Expr1: DatePart("m",[Membership Data]![Date Joined])
(DatePart("m",Date$())+2)

Expr2: DatePart("yyyy",[Membership Data]![Date Joined])
DatePart("yyyy",Date$())-10