Advertisement
If you have a new account but are having problems posting or verifying your account, please email us on hello@boards.ie for help. Thanks :)
Hello all! Please ensure that you are posting a new thread or question in the appropriate forum. The Feedback forum is overwhelmed with questions that are having to be moved elsewhere. If you need help to verify your account contact hello@boards.ie

Access: Workday Diff Between 2 Dates - Multiple Countries

Options
  • 21-01-2008 7:04pm
    #1
    sapper
    Registered Users Posts: 421 ✭✭


    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

  • Options
    #2
    Seth Brundle
    Moderators, Politics Moderators Posts: 38,985 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.


Advertisement