Access 2000 Calculation (2K)

Topic

New Reply

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

Reply | Quote

Replies

Do a search in this lounge for holidays and weekdays, there are plenty of suggestions here to cope with what you are after.
Pat

Reply | Quote

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:

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

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…

Reply | Quote