Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Sep 2002
    East Coast(USA)
    Thanked 0 Times in 0 Posts

    Access 2000 Calculation (2K)

    I have an eligibility tracker database designed in access 2000. The employees have to process the applications within 10 business days.
    1. How do you determine business days, weekends and holidays?
    2. If I have the following fields: AppStatus, EligDay, EligDate, AppReceived, Reassess etc. AppStatus has a dropdown of (NEW, EAP, REACTIVATE etc.)

    How do I do the following calculations:

    When you select NEW from AppStatus dropdown,
    EligDay = EligDate

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Melbourne, Australia
    Thanked 27 Times in 27 Posts

    Re: Access 2000 Calculation (2K)

    Do a search in this lounge for holidays and weekdays, there are plenty of suggestions here to cope with what you are after.
    Pat <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  3. #3
    4 Star Lounger
    Join Date
    Feb 2001
    Richland, Washington, USA
    Thanked 0 Times in 0 Posts

    Re: Access 2000 Calculation (2K)

    For the business days, weekends, and holidays question, as Pat mentioned you can probably find various solutions in the Lounge archives. I've built my own routines to do this -- if you're up for some VBA coding (have experience with it, etc.), here's the basic framework:

    1. Build a IsWorkday(date) boolean function that returns True if the date argument falls on a workday. This function can check, first, if the date is Monday through Friday (or whatever your work week is) using the built in VBA Weekday function. If it passes that then check against a table of Holidays you've set up for your company (e.g., using the DLookup function). If the date's not in that table, then return true for the function; false otherwise. You can build in additional logic if you need it for your particular work schedule. A similar scheme could be used for an IsWeekend and IsHoliday function, if needed.

    2. If you need to count business days, weekend days, and/or holidays between two dates, then you could set up a routine that loops starting with the early date, stepping to the final date and counting up the number of times the above function (which ever one is appropriate for what you want to count up) returns true for the dates in the range.

    This is a by-the-bootstraps approach, but it might give you the greatest flexibility to match your particular needs. If you have a huge database, or your date ranges are huge, this might be rather sluggish.

    Another alternative is to "hijack" Excel's calendar functions through VBA automation. Setting up the "hooks" into Excel is a little tricky, but once you do it, you can reference Excel's rather large pool of date worksheet functions directly from Access.

    Regarding your second question, I'd suggest writing an event routine for the combo box (dropdown) control's After_Update() event that checks the new value of the control and recalculates EligDay, essentially using the code you've already suggested, almost verbatim. For example, if the field names are AppStatus, EligDay, EligDate, AppReceived, and Reassess, then your code would look something like this:
    <font face="Georgia">
    Private Sub AppStatus_AfterUpdate()

    If Me!AppStatus = "NEW" Then
    Me!EligDay = CDate(Me!EligDate) - CDate(Me!AppReceived)
    ElseIf Me!AppStatus = "REACTIVATE" Then
    Me!EligDate = CDate(Me!EligDate)-CDate(Me!Reassess)
    End If

    End Sub
    </font face=georgia>
    For this to work, make sure that the bound values in the Combo Box are the text values themselves. If the bound values are something else, then use those in the logic above, rather than the text strings.

    I know this is a little sketchy, and there are certainly a zillion ways to skin the Access cat, but maybe this will get you started...

Posting Permissions

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