Retrieve all records for 5 mths from now regardless of year
The following code works to bring back records from an Access2007 database that have a renewal month 5 months from current month. I would like to further delineate by year? I want only records 5 months from current date or, December of 2013. When I run next month it would be for January of 2014 etc... It currently brings back all renewal dates in December regardless of year, for example December of 2022. Thanks so much for the help.
rs.Open "SELECT * Owners INNER JOIN Renewal ON Owners.Owner = Renewal.Comp " _
& "WHERE Owners.FOorSA = 'FO' And " _
& "DatePart(""m"", Renewal.RenewalDte) = DatePart(""m"", NOW) + 5", Conn, adOpenStatic, adLockOptimistic
Eddy
Re: Retrieve all records for 5 mths from now regardless of year
You can build a crude solution, like:
Code:
Dim MyString AS String
If Month(Now) > 8 Then
MyString = "SELECT * Owners INNER JOIN Renewal ON Owners.Owner = Renewal.Comp " & _
"WHERE Owners.FOorSA = 'FO' And " & _
"Month(Renewal.RenewalDte) = (Month(NOW) + 5) And " & _
"Year(Renewal.RenewalDte) = Year(Now) +1)"
Else
MyString = "SELECT * Owners INNER JOIN Renewal ON Owners.Owner = Renewal.Comp " & _
"WHERE Owners.FOorSA = 'FO' And " & _
"Month(Renewal.RenewalDte) = (Month(NOW) + 5) And " & _
"Year(Renewal.RenewalDte) = Year(Now)"
End If
rs.Open MyString, Conn, adOpenStatic, adLockOptimistic
(not tested)
Re: Retrieve all records for 5 mths from now regardless of year
Thanks for the reply. I replaced the "(Month(Now) + 5)" portion with a variable I manipulate because for the month of July or after it returned 13 or 14 etc...
It works fine now. Thanks again.
Re: Retrieve all records for 5 mths from now regardless of year
Quote:
Originally Posted by
EddyLLC
Thanks for the reply. I replaced the "(Month(Now) + 5)" portion with a variable I manipulate because for the month of July or after it returned 13 or 14 etc...
It works fine now. Thanks again.
That's right... I thought I was using a function to add months... sorry!