Advertisement
Help Keep Boards Alive. Support us by going ad free today. See here: https://subscriptions.boards.ie/.
If we do not hit our goal we will be forced to close the site.

Current status: https://keepboardsalive.com/

Annual subs are best for most impact. If you are still undecided on going Ad Free - you can also donate using the Paypal Donate option. All contribution helps. Thank you.
https://www.boards.ie/group/1878-subscribers-forum

Private Group for paid up members of Boards.ie. Join the club.

Access: Workday Diff Between 2 Dates - Multiple Countries

  • 21-01-2008 07:04PM
    #1
    sapper
    Registered Users, Registered Users 2 Posts: 425 ✭✭


    Hi

    Can anyone help me? The records in my Access database have amongst other fields, a country code and a date. I want to come up with a function that will calculate the difference between the date and today without counting weekends or, and this is the tricky bit, holidays in the country referenced by the country code.

    I've been searching the web and I can't really find anything that does it....Not very good at vb, so was hoping to cut and paste something.... :o

    I'm sure the function will have to reference a table/tables for the holiday dates....There are 15 possible countries in my dbase


Comments

  • #2
    Seth Brundle
    Moderators, Politics Moderators, Paid Member Posts: 44,225 Mod ✭✭✭✭Seth Brundle


    It sounds like you will need to set up a list of excluded dates for each of the excluded days (Saturdays, Sundays, holidays, etc.) in a table. This would include the Then your formula would be something like (needs tidying but you will get the gist) 
    x = datefiff("d", [date value], Date())
y = dcount("*", "[tbl_Excluded_Dates]", "[Excluded Date Field] >=#" & [date value] & "# AND [Excluded Date Field] <=#" & Date() & "#")
calculated_date_difference = x-y
    You could make it country centric by adding a yes/no field for each country. Then just tick each date that is excluded for each country and by updating the code above.

    Help Keep Boards Alive. Support us by going ad free today. See here: https://subscriptions.boards.ie/ .



Advertisement