Results 1 to 7 of 7
  1. #1
    access_tom is offline Novice
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Oct 2010
    Posts
    2

    Various Day Calculation Function

    how do you do this :s


    The date each bike is due for return is calculated automatically as 7 days after the bike was initially hired.
    Throughout the year, there are many holidays when the library and other public services will be closed. The mayor has decided that for the bicycle hire scheme, school holiday dates will be used as holidays. If a bike is due for return during a holiday or weekend, then its due date will be the next working day after the holiday or weekend. The school holidays can be found in the file "holidays.txt".
    Examples:
     If a bike is hired on 12/9/2009 then it will be due for return on 19/9/2009 (eg Tracey Browne)
     If a bike is hired on 21/10/2009 then it would be due for return on 28/10/2009 (eg Philip Schmidt) but this is during the school holiday and so it will actually be due for return on 2/11/2009


    this is related to this:

    http://www.tek-tips.com/faqs.cfm?fid=4531


  2. #2
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Please do not multi-post your issue. The answer to your question is in the link you posted. What part is not understood?

  3. #3
    Harry is offline Novice
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Oct 2010
    Location
    library
    Posts
    2
    i have looked upon this piece of data and do not see how i would add the formula in and where, could i too please have help on how to do this,
    also what does a fuction do in a query

  4. #4
    access_tom is offline Novice
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Oct 2010
    Posts
    2
    well my point is the same to harry as i dont understand how to input the function
    i also dont understand what each part of the formula does

  5. #5
    NTC is offline VIP
    Windows Vista Access 2007
    Join Date
    Nov 2009
    Posts
    2,392
    Generically speaking; embedded in visual is the DateAdd function/method - so that if you put in one date - it will supply you another date.

    But no database knows anything about 'school holidays'. So you can consider the solution which is purely managerial - which is to say - use the DateAdd function but post the policy that if the return date falls on holday then the actual return date is the next regular day - - and let the human manage that unique variation.

    To do it with software, and not human, then you must build a table - with 365 records and have one column be the real date and the next column be the return date....you yourself in building the table would have to preset the correct values, and then use this table. As bad as this sounds in terms of grunt work - once built you could reuse this table with slight variations each year.

    Hope this helps.

  6. #6
    slave138's Avatar
    slave138 is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Oct 2010
    Location
    WI
    Posts
    233

    An easier way...

    You could create a table of just the school holidays then cross-check it when assigning a return date. If the standard return date is on the holiday table, add a day and check again until it no longer matches.

    See the sample for an example of what I mean.

  7. #7
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Here's the function I use:
    Code:
    Public Function PlusWorkdays(dteStart As Date, intNumDays As Long) As Date
    PlusWorkdays = dteStart
    Do While intNumDays > 0
         PlusWorkdays = DateAdd("d", 1, PlusWorkdays)
    '     If Weekday(PlusWorkdays, vbMonday) <= 5 Then
    '-- If you have a Holiday Table use the next IF instead!
         If Weekday(PlusWorkdays, vbMonday) <= 5 And _
          IsNull(DLookup("[HoliDate]", "tblHolidays", _
          "[HoliDate] = " & Format(PlusWorkdays, "\#mm\/dd\/yyyy\#;;;\N\u\l\l"))) Then
          '-- The above Format of PlusWorkdays works with US or UK dates!
              intNumDays = intNumDays - 1
         End If
    Loop
    End Function

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

Similar Threads

  1. Calculation Help
    By edge5511 in forum Programming
    Replies: 4
    Last Post: 12-18-2015, 11:11 AM
  2. Need help with calculation please
    By Gabriel984 in forum Forms
    Replies: 6
    Last Post: 09-09-2010, 12:06 PM
  3. Calculation Help
    By ErnieS in forum Access
    Replies: 13
    Last Post: 07-08-2010, 08:35 AM
  4. Want function to get current function name
    By Davis DeBard in forum Programming
    Replies: 2
    Last Post: 08-13-2009, 05:02 AM
  5. Calculation
    By thestclair in forum Reports
    Replies: 1
    Last Post: 04-29-2006, 11:03 AM

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