Results 1 to 6 of 6
  1. #1
    UKFlamingo is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    May 2017
    Posts
    3

    Breaking down dates into periods on a form to change a listbox's records based on period

    Hello Everyone,



    I am trying to tell access how long a period is in a year, i.e. There are 26 periods in a year each lasting 2 weeks(14 Days) starting on the 1st of Jan and ending on the 30th of Dec. Don't worry about any extra days.

    I need this so that when I select the period in a combo box it will update a list box for dates that match the period.

    Here is a bit more detail:

    I have a table (MatterList) that shows all of our active jobs and the date that they started.


    On a form i want to have a combo box that when i select a number between 1 and 26 it will change the dates in a list box to show all the jobs that are active.

    e.g I select "1" and it shows me all the active jobs between 1st of Jan and 14th of Jan. because it knows that 1 = that date range.

    The main fields I need to use for this are: Job_status, and Matter_OpenDate.

    I have been previously given help with this but I didn't understand the code I was given; Here is what it says;
    Code:
    SELECT ID, projname FROM tblProjects where active=true and datestarted between dateadd("d", ([period]-1)*14,datevalue("01/01/" & year(date())) and dateadd("d", (([period]-1)*14)+14,datevalue("01/01/" & year(date())) 
    All help is much appreciated!

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    for responders - link is here https://www.access-programmers.co.uk...d.php?t=293883

    @ UKFlamingo - why are you not asking on that thread? why have you not had the courtesy to acknowledge the help you have been provided? Since you are new to these forums, also recommend you read this link http://www.excelguru.ca/content.php?184

  3. #3
    UKFlamingo is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    May 2017
    Posts
    3
    If you actually look at the thread you'll see that I have replied and there hasn't been any additional help. I created this post to add more detail to my help request and to see if anyone else has any ideas.

  4. #4
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    you could use a custom function. In a query, submit the date to the function to get back what period....
    usage: getPeriod([dateField])

    Code:
    Public Function getPeriod(ByVal pvDate)
    Dim vYr
    Dim vPd1St As Date, vPd1End As Date
    Dim vPd2St As Date, vPd2End As Date
    Dim vPd3St As Date, vPd3End As Date
    Dim vPd4St As Date, vPd4End As Date
    Dim vPd5St As Date, vPd5End As Date
    Dim vPd6St As Date, vPd6End As Date
    Dim vPd7St As Date, vPd7End As Date
    Dim vPd8St As Date, vPd8End As Date
    Dim vPd9St As Date, vPd9End As Date
    Dim vPd10St As Date, vPd10End As Date
    Dim vPd11St As Date, vPd11End As Date
    Dim vPd12St As Date, vPd12End As Date
    Dim vPd13St As Date, vPd13End As Date
    Dim vPd14St As Date, vPd14End As Date
    Dim vPd15St As Date, vPd15End As Date
    Dim vPd16St As Date, vPd16End As Date
    Dim vPd17St As Date, vPd17End As Date
    Dim vPd18St As Date, vPd18End As Date
    Dim vPd19St As Date, vPd19End As Date
    Dim vPd20St As Date, vPd20End As Date
    Dim vPd21St As Date, vPd21End As Date
    Dim vPd22St As Date, vPd22End As Date
    Dim vPd23St As Date, vPd23End As Date
    Dim vPd24St As Date, vPd24End As Date
    Dim vPd25St As Date, vPd25End As Date
    Dim vPd26St As Date, vPd26End As Date
    
    vYr = Year(pvDate)
    vPd1St = "1/1/" & vYr
    vPd1End = "1/15/" & vYr
    vPd2St = "2/1/" & vYr
    vPd2End = "2/15/" & vYr
    vPd3St = "3/1/" & vYr
    vPd3End = "3/15/" & vYr
    'etc
    
    Select Case True
       Case pvDate >= vPd1St And pvDate <= vPd1End
           getPeriod = "Pd-1"
       Case pvDate >= vPd2St And pvDate <= vPd2End
           getPeriod = "Pd-2"
       Case pvDate >= vPd3St And pvDate <= vPd3End
           getPeriod = "Pd-3"
       Case pvDate >= vPd4St And pvDate <= vPd4End
           getPeriod = "Pd-4"
       Case pvDate >= vPd5St And pvDate <= vPd5End
           getPeriod = "Pd-5"
       Case pvDate >= vPd6St And pvDate <= vPd6End
           getPeriod = "Pd-6"
       Case pvDate >= vPd7St And pvDate <= vPd7End
           getPeriod = "Pd-7"
       Case pvDate >= vPd8St And pvDate <= vPd8End
           getPeriod = "Pd-8"
       Case pvDate >= vPd9St And pvDate <= vPd9End
           getPeriod = "Pd-9"
       Case pvDate >= vPd10St And pvDate <= vPd10End
           getPeriod = "Pd-10"
       Case pvDate >= vPd11St And pvDate <= vPd11End
           getPeriod = "Pd-11"
       Case pvDate >= vPd12St And pvDate <= vPd12End
           getPeriod = "Pd-12"
       Case pvDate >= vPd13St And pvDate <= vPd13End
           getPeriod = "Pd-13"
       Case pvDate >= vPd14St And pvDate <= vPd14End
           getPeriod = "Pd-14"
       Case pvDate >= vPd15St And pvDate <= vPd15End
           getPeriod = "Pd-15"
       Case pvDate >= vPd16St And pvDate <= vPd16End
           getPeriod = "Pd-16"
       Case pvDate >= vPd17St And pvDate <= vPd17End
           getPeriod = "Pd-17"
       Case pvDate >= vPd18St And pvDate <= vPd18End
           getPeriod = "Pd-18"
       Case pvDate >= vPd19St And pvDate <= vPd19End
           getPeriod = "Pd-19"
       Case pvDate >= vPd20St And pvDate <= vPd20End
           getPeriod = "Pd-20"
       Case pvDate >= vPd21St And pvDate <= vPd21End
           getPeriod = "Pd-21"
       Case pvDate >= vPd22St And pvDate <= vPd22End
           getPeriod = "Pd-22"
       Case pvDate >= vPd23St And pvDate <= vPd23End
           getPeriod = "Pd-23"
       Case pvDate >= vPd24St And pvDate <= vPd24End
           getPeriod = "Pd-24"
       Case pvDate >= vPd25St And pvDate <= vPd25End
           getPeriod = "Pd-25"
       Case pvDate >= vPd26St And pvDate <= vPd26End
           getPeriod = "Pd-26"
       Case Else
           getPeriod = "error"
    End Select
    End Function

  5. #5
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    If you actually look at the thread you'll see that I have replied and there hasn't been any additional help
    responders provide their time FOC and often have day jobs, they are not available 24/7 - you need to be more patient. I've just checked and a response has been provided a few minutes ago

  6. #6
    UKFlamingo is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    May 2017
    Posts
    3
    @Ranman256 Thank you for your help buddy, that does the Job.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Select Records based on dates or no dates or both
    By usatraveler in forum Queries
    Replies: 3
    Last Post: 08-12-2016, 07:52 AM
  2. Replies: 2
    Last Post: 10-27-2015, 09:12 AM
  3. Replies: 9
    Last Post: 06-02-2014, 08:10 AM
  4. Replies: 5
    Last Post: 03-25-2012, 09:38 AM
  5. Change Financial Quater Periods
    By scorched9 in forum Access
    Replies: 0
    Last Post: 01-26-2010, 08:38 PM

Tags for this Thread

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