Page 1 of 2 12 LastLast
Results 1 to 15 of 29
  1. #1
    Join Date
    May 2020
    Posts
    26

    Month renew

    I am trying to select all my records that fall between the last 18th and the next 18th.
    So today is the 16th June. I want all records that have a date between the 18th May and the 18th June.


    But when I run the same query on the 19th June it will pick the records with a date from 18th June to the 18th July.
    It will always be the 18th of the month

    I am think that I might need a table with the start and end dates for each time period and work it that way, but I am not sure how.

    It must be possible, as it is a similar requirement to Tax years falling between the 6th April 2019 and 5th April 2020 for example

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    What table and fields are involved?
    Please show the SQL you are using.

  3. #3
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    vDate = date()
    vDate2 = dateadd("m",1,Date())

    select * from table where [dateFld] between date() and
    dateadd("m",1,Date())

  4. #4
    davegri's Avatar
    davegri is online now Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,389
    Criteria would be:
    Between dateserial(Year(Date()),Month(Date())-1,18) AND dateserial(Year(Date()),Month(Date()),18)

  5. #5
    Join Date
    May 2020
    Posts
    26
    Ahah I have not used "dateserial" before!! that seems to have done the trick.Now I just have to study up on what it means, so I can use it again!
    Many Thanks
    Phil

  6. #6
    Join Date
    Apr 2017
    Posts
    1,673
    Quote Originally Posted by davegri View Post
    Criteria would be:
    Between dateserial(Year(Date()),Month(Date())-1,18) AND dateserial(Year(Date()),Month(Date()),18)
    At 19. June this gives the interval at 18. May to 18. June. It must give the interval at 18.June to 18. July!
    Code:
     
    DateSerial(Year(Date()),Month(Date())+Iif(Day()<18, -1, 0),18) AND dateserial(Year(Date()),Month(Date())+Iif(Day()<18, 0, 1),18) 
    or depending how you want to handle 18. day of month
    DateSerial(Year(Date()),Month(Date())+Iif(Day()<19, -1, 0),18) AND dateserial(Year(Date()),Month(Date())+Iif(Day()<19, 0, 1),18)

  7. #7
    davegri's Avatar
    davegri is online now Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,389
    But when I run the same query on the 19th June it will pick the records with a date from 18th June to the 18th July.
    Arvil, it's unclear from the OP if this is the desired result or a problem. In any case, between us, we've covered both situations.
    Last edited by davegri; 06-16-2020 at 09:32 AM. Reason: sp

  8. #8
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Pardon me for asking, but what happens if the query is executed on Dec 22, 2020 or on Jan 5,2021??

    To me, it doesn't look like the year part adjusts accordingly.... maybe I'm missing something?

  9. #9
    davegri's Avatar
    davegri is online now Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,389
    Pardon me for asking, but what happens if the query is executed on Dec 22, 2020 or on Jan 5,2021??
    Here's a function that lets you specify a date instead of using Date(), with results from IW

    Code:
    Public Function testBOFA(Rundate As Date) As String
        testBOFA = "Between " & DateSerial(Year(Rundate), Month(Rundate) - 1, 18) & " And " & DateSerial(Year(Rundate), Month(Rundate), 18)
    End Function
    And the results:

    ?testBOFA(#1/5/2021#)
    Between 12/18/2020 And 1/18/2021


    ?testBOFA(#12/22/2020#)
    Between 11/18/2020 And 12/18/2020

    That's the beauty of the dateserial function. It knows how to handle end/beginning of year.

    Still haven't heard from OP to verify this is the desired result.
    Last edited by davegri; 06-16-2020 at 08:11 PM. Reason: got the op's mixed up

  10. #10
    Join Date
    Apr 2017
    Posts
    1,673
    Quote Originally Posted by ssanfu View Post
    Pardon me for asking, but what happens if the query is executed on Dec 22, 2020 or on Jan 5,2021??

    To me, it doesn't look like the year part adjusts accordingly.... maybe I'm missing something?
    DateSerial(2020,13,18) is same as DateSerial(2020+1,1,18);
    DateSerial(2021,0,18) is same as DateSerial(2021-1,12,18).

    Btw, this feature is excellent way to calculate end of month too - like DateSerial(Year(Date()),Montth(Date())+1,0)

  11. #11
    Cottonshirt is offline Competent Performer
    Windows 8 Access 2007
    Join Date
    Feb 2018
    Posts
    116
    this feature is excellent way to calculate end of month too - like DateSerial(Year(Date()),Month(Date())+1,0)

    that's really interesting; it literally returns day zero of next month, which ACCESS interprets as being the last day of the current month. it would never have occurred to me that day zero could be anything other than an error. thanks for sharing.


    Cottonshirt

  12. #12
    Join Date
    May 2020
    Posts
    26
    Ahah I have not used "dateserial" before!! that seems to have done the trick.Now I just have to study up on what it means, so I can use it again!
    I manged to adapt the to get next months activity as well and that has worked.
    Many Thanks
    Phil

  13. #13
    Join Date
    May 2020
    Posts
    26
    Hi,
    You were correct. Come the 19th June it is returning the wrong month.
    However your code is giving an error message saying there is the wrong number of arguments.
    It looks like there is a bracket missing

  14. #14
    Join Date
    May 2020
    Posts
    26
    Quote Originally Posted by davegri View Post
    Arvil, it's unclear from the OP if this is the desired result or a problem. In any case, between us, we've covered both situations.
    Neither of these work. The first one , as Avril says, gives the wrong month. and the second one gives an error that there are thean incorrect number of arguments. I have tried to edit it but there is something I am not understanding.
    Help please!

  15. #15
    Join Date
    May 2020
    Posts
    26
    Quote Originally Posted by ArviLaanemets View Post
    At 19. June this gives the interval at 18. May to 18. June. It must give the interval at 18.June to 18. July!
    Code:
     
    DateSerial(Year(Date()),Month(Date())+Iif(Day()<18, -1, 0),18) AND dateserial(Year(Date()),Month(Date())+Iif(Day()<18, 0, 1),18) 
    or depending how you want to handle 18. day of month
    DateSerial(Year(Date()),Month(Date())+Iif(Day()<19, -1, 0),18) AND dateserial(Year(Date()),Month(Date())+Iif(Day()<19, 0, 1),18)
    Neither of these work. The first one , as Avril says, gives the wrong month. and the second one gives an error that there are the an incorrect number of arguments. I have tried to edit it but there is something I am not understanding.
    Help please!

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 12-29-2014, 10:14 AM
  2. Replies: 5
    Last Post: 10-08-2014, 02:23 PM
  3. Replies: 6
    Last Post: 05-05-2014, 10:51 PM
  4. Update Query to change Renew Dates
    By GraemeG in forum Queries
    Replies: 4
    Last Post: 04-07-2011, 12:09 PM
  5. Renew Multiple records via a form
    By Phil Knapton in forum Forms
    Replies: 23
    Last Post: 01-01-2011, 07:05 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums