1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Access 2010 Set a date field to today plus 30 working days

Discussion in 'Business Applications' started by SlowHnds, Mar 13, 2013.

Thread Status:
Not open for further replies.
Advertisement
  1. SlowHnds

    SlowHnds Thread Starter

    Joined:
    Mar 11, 2010
    Messages:
    212
    When items are loaned out we want them returned in 30 working days (approximately 6 weeks I'm not going to bother trying to calculate holidays) but to only come back on a week day Monday through Friday.

    Field Name ReturnDate_Fld this needs to be calculated as 30 working days from the current date

    or should I not worry about the extra effort and use the approximate by adding 42 to the BorrowDate_Fld (plus 42 days = 6 weeks)

    Should I worry about this in a form or only set it as field in the report/query?
     
  2. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    It really depends on how accurate you or the users need the data to be.
    I have in the past worked them out exactly using VBA, you could probably do the same using IIF()s, it might take more than 1 query.
     
  3. SlowHnds

    SlowHnds Thread Starter

    Joined:
    Mar 11, 2010
    Messages:
    212
    Does it have to be... not really I suppose though I'd rather be accurate and have the closest weekday show up on the borrowers copy of the charge out/borrowers report.

    I was thinking that it should be possible to check a field PossibleReturnDate_FLD (for instance) 42 days away and if the day of the week is Saturday add 2 days or if the day of the week is Sunday add one day. Set the field ReturnDate_Fld and exit.

    Would that be possible using VBA? Without getting too complex.
     
  4. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    If you are going to use VBA I would use it to get an exact date, do you wish to include Holidays as well as weekends as not counting?

    I am not sure if I can still find the original database I wrote a few years ago that did the calculations, I may have to redo them from scratch if you want them.
     
  5. SlowHnds

    SlowHnds Thread Starter

    Joined:
    Mar 11, 2010
    Messages:
    212
    If it's a huge increase in the work to calculate holidays, then we can skip them. I'm not sure how one would calculate Good Friday & Easter Monday, which seems to change every year.

    Good Friday and Easter Monday are holidays. Dec.25 and 26 June 21st. July 1st. are all statutory holiday (and if the holiday is on a weekend for all those the following Monday is a holiday)

    First Monday in August and September. 2nd Monday in October. November 11th. or if on weekend the following Monday.

    Seems like a lot of work to calculate it out.

    I thought of using expressions in a query. format it to date "dddd" then use another expression with IIF to evaluate it and then a third to add 2 days if Saturday or 1 day if Sunday (though I'm not sure how to accomplish that either)

    The more I look at this "simple" idea the more I think maybe I should just use the date picker on the form. But then I'd love to automate it and have it right also.
     
  6. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    OK so what is this based on, a currently entered date on the Form?

    You could have a table of the Holidays.
     
  7. SlowHnds

    SlowHnds Thread Starter

    Joined:
    Mar 11, 2010
    Messages:
    212
    Yes the BorrowDate_FLD is the date the item was borrowed from the Seniors Centre. This field is on the form and stored in the table. The return date would be calculated on that date plus 30 working days.

    The report is printed in duplicate one for signature and one for the borrower. The report is generated off of a query. The return date doesn't have to be stored in the table though I'd like it to be. It is probably enough that it would appear in the query and on the report.

    I've done up a table of scheduled Holidays until the end of 2014 there is 23 holidays that I'm aware of between now and December 31, 2014

    Holidays_Tbl
    IdDateNbr_Fld Autonumber Primary Key
    HolidayDate_Fld Date Long
    HolidayDescription Text

    This table will have to be updated/added to over time. I'll add a form for entry and include it in the forms on the Switch Board Menu
     
  8. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    What is the name of the return date field?
    Can you create a query based on the Holidays table with the dates in Ascending order and let me know it's name.
     
  9. SlowHnds

    SlowHnds Thread Starter

    Joined:
    Mar 11, 2010
    Messages:
    212
    Holiday_Qry it's sorted by date
    cut and pasted

    Holiday_Qry
    IdDateNbr_Fld HolidayDate_Fld HolidayDescription_FLD
    1
    Friday, March 29, 2013 Good Friday 2013
    2
    Monday, April 01, 2013 Easter Monday 2013
    3
    Monday, May 20, 2013 Victoria Day 2013
    4 Friday, June 21, 2013 Aboriginal Day 2013
    5
    Monday, July 01, 2013 Canada Day 2013
    6
    Monday, August 05, 2013 Heritage Day 2013
    7
    Monday, September 02, 2013 Labour Day 2013
    8 Monday, October 14, 2013 Thanksgiving Day 2013
    9 Monday, November 11, 2013 Remembrance Day 2013
    10 Wednesday, December 25, 2013 Christmas Day 2013
    11 Thursday, December 26, 2013 Boxing Day 2013
    12 Wednesday, January 01, 2014 New Years Day 2014
    13
    Friday, April 18, 2014 Good Friday 2014
    14
    Monday, April 21, 2014 Easter Monday 2014
    15
    Monday, May 19, 2014 Victoria Day 2014
    16
    Monday, June 23, 2014 Aboriginal Day Stat 2014
    17
    Tuesday, July 01, 2014 Canada Day 2014
    18 Monday, August 04, 2014 Heritage Day 2014
    19 Monday, September 01, 2014 Labour Day 2014
    20
    Monday, October 13, 2014 Thanksgiving Day 2014
    21
    Tuesday, November 11, 2014 Remembrance Day 2014
    22 Thursday, December 25, 2014 Christmas Day 2014
    23
    Friday, December 26, 2014 Boxing Day 2014
    24
    Thursday, January 01, 2015 New Years Day 2015

    ReturnDate_Fld Date Long
     
  10. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    I obviously can't test this without the Database, but try this VBA code in the BorrowDate_Fld After Update Event Procedure.

    On Error GoTo errorcatch

    Dim rs As Object, SQL As String
    me.ReturnDate_Fld = me.BorrowDate_Fld + 42
    SQL = "SELECT Holiday_Qry .* " & _
    "FROM Holiday_Qry " & _
    "WHERE HolidayDate_Fld <= #" & Format(Me.ReturnDate_Fld, "mm/dd/yyyy") & "#" & _
    "And Date2 > #" & Format(Me.BorrowDate_Fld, "mm/dd/yyyy") & "#"
    Set rs = CurrentDb.OpenRecordset(SQL)
    rs.movelast
    if rs.recordcount> 0 then
    msgbox rs.recordcount
    me.ReturnDate_Fld = me.ReturnDate_Fld + rs.recordcount
    End if
    rs.Close
    Set rs = Nothing

    Exit Sub
    errorcatch:
    MsgBox Err.Description

    So let me know if you get any error messages.
    You do not actually need the "msgbox rs.recordcount" as that is just to tell you it found a holiday.
    I would suggest testing it where you know that a holiday will occur between the borrowed and return dates.
     
  11. SlowHnds

    SlowHnds Thread Starter

    Joined:
    Mar 11, 2010
    Messages:
    212
    I'll try this when I get home from work and I'll post the results tonight or tormorrow morning.

    It looks like it should work, if I'm following the code right.

    If I get an error I'll empty the data base and put in a few rows of dummy data and post a copy zipped. Is 2007 format ok?

    Thanks for all your help OBP you've really helped me learn a lot over the last few years, I wish I could remember it all.
     
  12. SlowHnds

    SlowHnds Thread Starter

    Joined:
    Mar 11, 2010
    Messages:
    212
    Access does set a date. But it's not the correct number of working days.

    If I set the borrow date to March 27, 2013 on update it sets the ReturnBy_Fld to 2013/05/08 when it should be 2013/05/10. Holidays are Good Friday March 29th, and Easter Monday April 1st. Weekends are March 30 & 31, April 6 & 7, April 13 & 14, April 20 & 21, April 27 & 28, and May 4 & 5.

    It would appear that it calculated the weekends but not the two holidays in the time period.


    Also it gives an error. "Too few parameters. Expected 1."

    It is probably something simple but I'm not seeing it and I've ran it step by step. I'll try to get the the data base copied and emptied of personal information and zip you a copy. if you need it.

    it's close though and the date always lands on a weekday. So worse comes to worse I'll use it as is.
     
  13. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    Due to the error message it appears that the query is not being opened by the Recordset.
    Does it have any Criteria in it, as Recordsets don't work with preset Criteria.
     
  14. SlowHnds

    SlowHnds Thread Starter

    Joined:
    Mar 11, 2010
    Messages:
    212
    The Holiday_Qry has no criteria set.

    The form source has no criteria set when I look at the querry.
     
  15. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    OK, can you check the spelling of the date field in the query and the dates on the Form and compare them to the names of the fields in the VBA SQL?

    Any chance of a dummy database with a couple of records in it?
     
  16. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1092962

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice