CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2009
    Posts
    2

    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

  2. #2
    Join Date
    Jul 2005
    Posts
    1,083

    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 ...

  3. #3
    Join Date
    Nov 2009
    Posts
    2

    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.

  4. #4
    Join Date
    Jul 2005
    Posts
    1,083

    Re: Retrieve all records for 5 mths from now regardless of year

    Quote Originally Posted by EddyLLC View Post
    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
  •  





Click Here to Expand Forum to Full Width

Featured