Select Case Usage for Date Due Calculation

[Meso]

Hello,

I have what I think should be a simple problem to solve, just need to have some guidance on VBA. Since I have little working knowledge of VBA, I'm guessing on the Select Case usage.

I'm working on a small database to track grievances filed by various employee unions. There are several date fields used for tracking deadlines. The time between when a particular grievance is filed and when a response from management is due is based on the union filing the grievance. For example, one union may require a 30 day response, another 15 days.

I have a lookup field on the form "grievancedetails" that selects the union. What I would like to have is the ResponseDueDate field automatically calculated based on the union selected against the GrievanceReceivedDate.

The date calculations are mostly based on calender days. I imagine when I get to the fields requiring working day calculations things will be a bit more complicated.

I appreciate any help provided...Bart

 

[JeremyNYC]

No need for select case, at least not yet. In the combo box in which you choose the Union, add another hidden column, between the ID and the Union's name, which will store the number of days the Union allows. Then, in the after update event of this combo, do something like this:
me!txtDaysToFile = me!UnionCombo.Columns(1)

You'll of course have to change the names of things, but what's going on here is that you're retrieving the grace period allowed by the union in the combo box and filling a text box based on that value.

Of course, that's assuming you'll want to display that grace period. If not, you can just refer to that column in whatever calculations you do on the form and reports.

Yes, business days will get more complicated, though it shouldn't be horrible. Here's some code I got from Arvin Meyer, and then modified. It requires tblHoliday, to store the holidays for the company. Of course, in your situation you may have to store multiple sets of holidays, adding a UnionID field to that table and the sql that grabs data from it.

Arvin is one of the guys who upkeep

http://www.mvps.org/access/,

one amazing web site.

I have tweaked his code to fit my applicaiton. Actually, I just found the link to his code:

http://www.mvps.org/access/datetime/date0006.htm

But I'll include my hack of it here, as I've already pasted it in.

Hope this helps.

Jeremy

Function AddBusinessDays(datStart As Date, ByVal intDayAdd As Integer)
On Error GoTo Error
'Adds the proper Business day skipping holidays and weekends
'Copyright Arvin Meyer 05/26/98
Dim gDB As Database
Dim rst As Recordset
Dim strSql As String

Set gDB = CurrentDb
Set rst = gDB.OpenRecordset("SELECT [HolDate] FROM tblHoliday", dbOpenSnapshot)

Do While intDayAdd > 0
datStart = datStart + 1
rst.FindFirst "[HolDate] = #" & datStart & "#"
If WeekDay(datStart) <> vbSunday And WeekDay(datStart) <> vbSaturday Then
If rst.NoMatch Then intDayAdd = intDayAdd - 1
End If
Loop

AddBusinessDays = datStart

rst.Close
Set rst = Nothing
Exit Function
Error:
ErrorTrap Err.Number, Err.Description, &quot;AddBusinessDays&quot;
End Function
=============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995

http://www.AlphaBetCityDataworks.com

Take a look at the Developer's section of the site for some helpful fundamentals.

 

[Meso]

Thanks Jeremy, I really appreciate the response. I'll have to try this out as I didn't think of this approach. There are actually various suspense days based on each step of the grievance process, but I think this should work fine with the calender days. Attm, we just manually enter the suspense dates, but I'd like to automate that entry. I've been putting that off as these union contracts can be complicated. We have four steps to a grievance, with four different unions, and each of four unions have calendar and working days applied to various grievance steps. I'm sure there's a reason for this, but that's above my level!