-
July 26th, 2013, 10:55 AM
#1
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
-
July 27th, 2013, 01:49 AM
#2
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)
Last edited by jggtz; July 27th, 2013 at 01:54 AM.
JG
... If your problem is fixed don't forget to mark your threads as resolved using the Thread Tools menu ...
-
July 29th, 2013, 04:29 PM
#3
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.
-
July 29th, 2013, 05:18 PM
#4
Re: Retrieve all records for 5 mths from now regardless of year
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!
JG
... If your problem is fixed don't forget to mark your threads as resolved using the Thread Tools menu ...
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|