Calcuate Start Date

  • Thread starter Thread starter Brandy via AccessMonster.com
  • Start date Start date
B

Brandy via AccessMonster.com

A user inputs when cabinets for a specific customer need to be delivered into
a form. All the header information is added. Customer, entry date, phase, etc.
They also select a checkbox at the header level if the job has a special
color.

There is a subfrm where the user input the detail for that job. Lot number,
species, door style, and when that lot is due to be delivered.

So when the user inputs the lot delivery date (LotDelDate) then I would like
the database to calculate when we should start building the cabinets. This
start date is also based on some of the other criteria entered into the form.
The code is listed below. Everything works except when determining if the job
is a special color. I would also like the start date to skip weekends.

Can anyone help me out?

Private Sub LotDelDate_AfterUpdate()

If Me.DoorStyle = "Eagle" Then
Me.WOSD = Me.LotDelDate - 5
Else
If Me.DoorStyle = "RP-9" Then
Me.WOSD = Me.LotDelDate - 5
Else
If Me.DoorStyle = "H/E" Then
Me.WOSD = Me.LotDelDate - 5
Else
If Me.DoorStyle = "F/E" Then
Me.WOSD = Me.LotDelDate - 5
Else
If Me.DoorStyle = "RP-9" Then
Me.WOSD = Me.LotDelDate - 5
Else
If Me.DoorStyle = "RP-22" Then
Me.WOSD = Me.LotDelDate - 5
Else
If Me.DoorStyle = "RP-23" Then
Me.WOSD = Me.LotDelDate - 5
Else
If Forms!FRMDELIVERY.Form.SpecialColor = "YES" Then
Me.WOSD = Me.LotDelDate - 6
Else
Me.WOSD = Me.LotDelDate - 4

End If
End If
End If
End If
End If
End If
End If
End If

End Sub
 
Brandy,

This might help make your code shorter and more readable.

Private Sub LotDelDate_AfterUpdate()

If Forms!FRMDELIVERY.Form.SpecialColor = "YES" Then
Me.WOSD = Me.LotDelDate - 6
Exit Sub
End If

Select Case Me.DoorStyle
Case "Eagle","RP-9","H/E","F/E","RP-22","RP-23"
Me.WOSD = Me.LotDelDate - 5
Case Else
Me.WOSD = Me.LotDelDate - 4
End Select

End Sub
 
Hi Brandy

First, it is not a good idea to hard-code a whole heap of business rules in
your code. This means that whenever you add a new door style, or change the
number of days taken to produce a certain style of door, you will need to
get in and edit your code.

You should have a separate table of DoorStyles, with fields:
DStyleID (autonumber, primary key)
DStyleName (text, unique index)
WODays (days to produce this door style)
SCDays (extra days needed if SpecialColor is selected)

Then you should change the DoorStyle field in your other table to numeric
(long), and create a relationship between it and the DStyleID field in your
new table.

You can then use a combo box to select the DoorStyle:
Name: cboDoorStyle
ControlSource: DoorStyle
RowSource: Select * from DoorStyles order by DStyleName;
BoundColumn: 1
ColumnCount: 4
ColumnWidths: 0;;0;0

Now the number of days for production is available by examining columns 2
and 3 of the combo box (remember columns start numbering from 0). The
number of days to take off from the delivery date is calculated by:
Dim iWODays as integer
iWODays = cboDoorStyle.Column(2)
If Me.Parent!SpecialColor then
iWODays = iWODays + cboDoorStyle.Column(2)
End If

Now, to work out the start date, you want to avoid weekends. That's no
problem, but you probably also want to avoid holidays, so you should create
another table named Holidays with two fields:
HolidayDate (date/time, primary key)
HolidayName (text)

You can fill it up with entries like:
4-Jul-2005 "4th of July"
25-Dec-2005 "Christmas Day"

Now, the following function adds a given number of days to a given date,
skipping weekends. If you specify a holiday table then it will skip any
holidays as well. If the number of days is negative, then it subtracts them
instead.

Public Function AddWorkDays( _
StartDate As Date, _
iDays As Integer, _
Optional sHolidaysTable As String, _
Optional sHolidayDateField As String) As Date
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim fCheckHolidays As Boolean
Dim fWorkday As Boolean
Dim iCount As Integer
Dim iIncrement As Integer
Dim dtResult As Date
On Error GoTo ProcErr
fCheckHolidays = Len(sHolidaysTable) <> 0 _
And Len(sHolidayDateField) <> 0
If fCheckHolidays Then
Set db = CurrentDb
Set rs = db.OpenRecordset("Select [" _
& sHolidayDateField & "] from [" _
& sHolidaysTable & "] order by [" _
& sHolidayDateField & "]", dbOpenSnapshot)
End If
dtResult = StartDate
iIncrement = Sgn(iDays)
Do Until iCount = iDays
dtResult = dtResult + iIncrement
fWorkday = Weekday(dtResult, vbMonday) < 6
If fWorkday And fCheckHolidays Then
rs.FindFirst "[" & sHolidayDateField & "]=" _
& Format(dtResult, "\#mm\/dd\/yyyy\#")
fWorkday = rs.NoMatch
End If
If fWorkday Then iCount = iCount + iIncrement
Loop
AddWorkDays = dtResult
ProcEnd:
If fCheckHolidays Then
rs.Close
Set rs = Nothing
Set db = Nothing
End If
Exit Function
ProcErr:
MsgBox Err.Description
Resume ProcEnd
End Function

Now your code to calculate the WOSD looks like this:
Dim iWODays as integer
iWODays = cboDoorStyle.Column(2)
If Me.Parent!SpecialColor then
iWODays = iWODays + cboDoorStyle.Column(2)
End If
Me.WOSD = AddWorkDays(Me.LotDelDate, -iWODays, _
"Holidays", "HolidayDate" )
 
first, you're nesting single If statement the same way you would nest IIf()
functions in a calculated field in a query or calculated control in a form.
in VBA, use a single If...Then...ElseIf...Else, for example:

If Me.DoorStyle = "Eagle" Then
Me.WOSD = Me.LotDelDate - 5
ElseIf Me.DoorStyle = "RP-9" Then
Me.WOSD = Me.LotDelDate - 5
ElseIf Me.DoorStyle = "H/E" Then
Me.WOSD = Me.LotDelDate - 5
ElseIf Me.DoorStyle = "F/E" Then
Me.WOSD = Me.LotDelDate - 5
Else
' something you may want to do when nothing else applies.
End If

suggest you read up on the If statement in VBA Help.
next, your nested If statements allow the SpecialColor condition to be acted
on *only IF* none of the preceding DoorStyle values are a match. what you
need are two separate If statements, one to check the DoorStyle, and one to
check the SpecialColor. since all the door styles in the If statement use
the same date calculation value (-5), suggest using a Select Case statement
followed by an If statement, as

Select Case Me.DoorStyle
Case "Eagle", "RP-9", "H/E", "F/E", "RP-22", "RP-23"
Me.WOSD = Me.LotDelDate - 5
Case Else
Me.WOSD = Me.LotDelDate - 4
End Select

If Forms!FRMDELIVERY.Form.SpecialColor = "YES" Then
Me.WOSD = Me.LotDelDate - 6
End If

okay, the above takes care of the straight date-assignment calculations.
"skipping weekends" is trickier - and what about holidays? this question has
come up in the newsgroups before, though i've never used such a function;
suggest you do a google search. or somebody else may post code, or a link,
to "skip weekends in the date calculations".

hth
 
Back
Top