Results 1 to 3 of 3
  1. #1
    Join Date
    May 2014

    Thumbs up Unanswered: I need Help with my Date Function to be stored in temp table Looping Statement

    Hi Everyone,
    I need your support regarding my small project in storing the dates to a temporary table in my access database. Here's the scenario.

    I have two textboxes that will accept two different dates. txtDateFrom and txtDateTo.
    I need to pickup only the "THURSDAY" weekdays in between of these two dates control and store it to the #temp table.

    eg. 1. txtDateFrom - May 28 2015 (user will input first date which is THURSDAY)
    2. txtDateTo - December 31 2015 (this may vary depending on the second date input)

    I need to collect all the THURDAYS dates in-between of the two inputted dates and stored in my temp table.

    sample temp table:
    IndexKey Date Week No
    1. May 28 2015 1
    2. June 4 2015 2
    3. Jun 11 2015 3

    etc up to the last Thursday
    n. Dec31 2015 n


    Loop while it is not reach the last date txtDateTo inputted
    If the first date is THURS store it to Week 1 else move to the next date until it finds first Thurs.
    Stop the loop when it reach the txtDateTo (last inputted date).

    You reply is much appreciated.



  2. #2
    Join Date
    Nov 2004
    out on a limb
    Provided Answers: 59
    several approaches could work, two of which are:-
    1) create a dfates table with only thursdays in it (eefectively thsi is no different to creatiugn a calendar table )
    ..then in a query join to that table extracting all the relevanbt dates

    2) use a function that returns the next thursday

    public function GetNextThursday(fromdate as date, todate as date) as date
    'function to find the next thursday AFTER the from date AND on on OR before the todate
    'note you may want to set an alternative default value than NULL, thats your perogative and do as you see fit
    GetNextThursday = NULL 'initialise to a default value
    'validations... well we must have to valiud dates AND the todate must be greater than the from date
    if not isnull(fromdate) and no isnull(todate) and fromdate<todate then
      CurrentDay = weekday(fromdate) 'find out what the current day is, according to this, thursday returns 5
      if CurrentDay < 5 then ' the fromdate is Sunday through to Wednesday
        DaysToAdd = 5 - CurrentDay
      else 'its a thursday through to saturday
        daystoadd = 12 - currentday 'thats 7+5 - the current value
      GetNextThursday = dateadd("d",datefrom  , daystoadd, datefrom)
      'just check that the next thursday is within our dateband
      if GetNextThursday > dateto then
        GetNextThursday = NULL
    end function
    then call it as
    nextThu = GetNextThursday("2015-11-01", "2015-11-12")
    if you need multiple values then
    nextThu = GetNextThursday("2015-11-01", "2015-11-18")
    thursdayafterthat = GetNextThursday(nextThu, "2015-11-18")
    like (virtually) all sample code I post here the above will almost certainly contain typos, some logic errors and will need debugging. its air code, providing with good intentions but no guarantees it works or is fit for purpose. in essence yer gets what yer pays for...
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    May 2014
    Thanks Healdem, i will try to do it as per your instruction and keep you posted also about the outcome, once again thank you for your valuable help.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts