DatePart problems considering month and year

[EddyLLC]

I'm trying to pull all records that include an anniversary date 6 months from the current month. For example it is now July so I want to list all records with an anniversary any day in the month in December of 2013. I tried the following code but does not consider the year. It returns records whose anniversary is December of 2022. How can I take into consideration the year so I and get those records with an anniversary date 6 months from current month regardless of what month I star at? Thanks.

rs.Open "SELECT * FROM Owners INNER JOIN Renewal ON Owners.Owner = Renewal.Comp " _
& "WHERE Owners.FOorSA = 'FO' And " _
& "DatePart(""m"", Renewal.RenewalDte) = DatePart(""m"", NOW) + 2", Conn, adOpenStatic, adLockOptimistic

 

[Andrzejek]

an anniversary date 6 months from the current month" and then "any day in the month in December of 2013". Well, there is 5 months between July and December.

Consider this:
Each month has the 1st, so you can compare the 1st of the curent month (add 5 months) with the 1st of the month from Renewal.RenewalDte:

[tt]
DateAdd("m", 5, Month(Date) & "/01/" & Year(Date)) = Month(Renewal.RenewalDte) & "/01/" & Year(Renewal.RenewalDte)[/tt]

Have fun.

---- Andy

 

[EddyLLC]

Thanks for the reply Andy. I used an If/Else statement with "If Month(Now) > 6 Then" because from July and on 6 months puts me in the next year. I then used a variable for the month because again, July and on plus 6 gives me 13, 14....

In the If/Else I use Year(Now) and Year(Now)+1 depending if it is after June (July thru December).

Seems to work for now. Thanks again.

Eddy

 

[Andrzejek]

I can see your point, and that's what I started with.
But that seams to be too complicated.

But... if it works and you are happy, then you are done

Have fun.

---- Andy

 

[strongm]

Me, I think I'd go with:

WHERE Owners.FOorSA = 'FO' AND Renewal.RenewalDate BETWEEN DateSerial(Year(Now), Month(Now) + 5, 1) AND DateSerial(Year(Now), Month(Now) + 6, 0)

 

[Rock4J]

Why not try using DATEDIFF() function, for example:

MyHours = DateDiff("h", MyDate1, MyDate2)
MyMinutes = DateDiff("m", MyDate1, MyDate2)
MySeconds = DateDiff("s", MyDate1, MyDate2)
MyDays = DateDiff("d", MyDate1, MyDate2)
MyMonths = DateDiff("m", MyDate1, MyDate2)
MyYear = DateDiff("y", MyDate1, MyDate2)

I have not figured out your code yet, so I just could leave this ideas to you to think about..

God bless.

Regards,
Rocky