Look up holidays help!

G

GerryE

I have a form that schedules the "RequestedDate" field in a subform for 4
work days ahead of the current date. I am having trouble making the event
procedure look in a Holiday table. The name of the table is Holidays and the
column is Holdate. If the Requested date matches the holdate, increase the
requested date by 1. Can anybody help? Here is part of the code I am using.
Your help is greatly appreciated.

Private Sub Quote_Status_AfterUpdate()

If Me![QuoteStatus] = "LDS" Then
If MsgBox("Do You want to update the requested date? Select no to Cancel.",
vbYesNo, "Exit?") = vbYes Then
Me![tblQuoteLog subform]![LDS] = Date
If Me![tblQuoteLog subform]![TTime] > #11:45:00 AM# Then
Me![tblQuoteLog subform]![LDS] = Date + 1
End If
If Me![tblQuoteLog subform]![DatePrt] <= 2 Then
Me![tblQuoteLog subform]![RequestedDate] = Me![tblQuoteLog
subform]![LDS] + 4
ElseIf Me![tblQuoteLog subform]![DatePrt] >= 3 Then
Me![tblQuoteLog subform]![RequestedDate] = Me![tblQuoteLog
subform]![LDS] + 5
End If
Me![tblQuoteLog subform]![Designer] = "LDS"
Me![tblQuoteLog subform]![Drawing Done] = True
Else
'Do Nothing
End If
 
A

Al Campagna

Gerry,
I just built pretty much the same thing a few days ago.
This is the statement that makes the decision whether a day is not a
Saturday or Sunday or Holiday.
Given a StartWeave, and DaysToWeave, from that StartWeave, I add 1 day to
StartWeave and then determined if it is NOT a Saturday, a Sunday, or a
Holiday. Accordingly
Email may cause a "one line" entry to "wrap", so be careful to "unwrap"
then in your code.

Me.StartWeave = DLookup("[StartDate]", "qryLoomScheduleWarpStart",
"LoomID = " & LoomID)
DaysToWeave = ([NoPieces] * [FinishPieceLength]) /
DLookup("[ActualMetersPerDay]", "qryHowFastWillItWeave", "StyleID = '" &
StyleID & "'")
'********************************** Calculate The Priority 1 WorkDays
to Complete
WorkDays = 0
Ctr = 0
Do Until WorkDays = (DaysToWeave + 1)
TempDate = Me.StartWeave + Ctr
If IsNull(DLookup("[HolidayDate]", "tblHolidays", "HolidayDate = #"
& TempDate & "#")) And WeekDay(TempDate) <> 7 And WeekDay(TempDate) <> 1
Then
WorkDays = WorkDays + 1
Ctr = Ctr + 1
Else
Ctr = Ctr + 1
End If
Loop
Me.EndWeave = TempDate
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html
"Find a job that you love... and you'll never work a day in your life."


GerryE said:
I have a form that schedules the "RequestedDate" field in a subform for 4
work days ahead of the current date. I am having trouble making the event
procedure look in a Holiday table. The name of the table is Holidays and
the
column is Holdate. If the Requested date matches the holdate, increase
the
requested date by 1. Can anybody help? Here is part of the code I am
using.
Your help is greatly appreciated.

Private Sub Quote_Status_AfterUpdate()

If Me![QuoteStatus] = "LDS" Then
If MsgBox("Do You want to update the requested date? Select no to
Cancel.",
vbYesNo, "Exit?") = vbYes Then
Me![tblQuoteLog subform]![LDS] = Date
If Me![tblQuoteLog subform]![TTime] > #11:45:00 AM# Then
Me![tblQuoteLog subform]![LDS] = Date + 1
End If
If Me![tblQuoteLog subform]![DatePrt] <= 2 Then
Me![tblQuoteLog subform]![RequestedDate] = Me![tblQuoteLog
subform]![LDS] + 4
ElseIf Me![tblQuoteLog subform]![DatePrt] >= 3 Then
Me![tblQuoteLog subform]![RequestedDate] = Me![tblQuoteLog
subform]![LDS] + 5
End If
Me![tblQuoteLog subform]![Designer] = "LDS"
Me![tblQuoteLog subform]![Drawing Done] = True
Else
'Do Nothing
End If
Click to expand...
 
G

GerryE

Al thanks for your help. Using Dcount I was able get my answere. Here is
what I used for anyone else that might need it.

Hday = DCount("*", "holidays", "[holdate] between #" _
& [startDate] & "# And #" & [EndDate] + 6 & "#")

Al Campagna said:
Gerry,
I just built pretty much the same thing a few days ago.
This is the statement that makes the decision whether a day is not a
Saturday or Sunday or Holiday.
Given a StartWeave, and DaysToWeave, from that StartWeave, I add 1 day to
StartWeave and then determined if it is NOT a Saturday, a Sunday, or a
Holiday. Accordingly
Email may cause a "one line" entry to "wrap", so be careful to "unwrap"
then in your code.

Me.StartWeave = DLookup("[StartDate]", "qryLoomScheduleWarpStart",
"LoomID = " & LoomID)
DaysToWeave = ([NoPieces] * [FinishPieceLength]) /
DLookup("[ActualMetersPerDay]", "qryHowFastWillItWeave", "StyleID = '" &
StyleID & "'")
'********************************** Calculate The Priority 1 WorkDays
to Complete
WorkDays = 0
Ctr = 0
Do Until WorkDays = (DaysToWeave + 1)
TempDate = Me.StartWeave + Ctr
If IsNull(DLookup("[HolidayDate]", "tblHolidays", "HolidayDate = #"
& TempDate & "#")) And WeekDay(TempDate) <> 7 And WeekDay(TempDate) <> 1
Then
WorkDays = WorkDays + 1
Ctr = Ctr + 1
Else
Ctr = Ctr + 1
End If
Loop
Me.EndWeave = TempDate
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html
"Find a job that you love... and you'll never work a day in your life."


GerryE said:
I have a form that schedules the "RequestedDate" field in a subform for 4
work days ahead of the current date. I am having trouble making the event
procedure look in a Holiday table. The name of the table is Holidays and
the
column is Holdate. If the Requested date matches the holdate, increase
the
requested date by 1. Can anybody help? Here is part of the code I am
using.
Your help is greatly appreciated.

Private Sub Quote_Status_AfterUpdate()

If Me![QuoteStatus] = "LDS" Then
If MsgBox("Do You want to update the requested date? Select no to
Cancel.",
vbYesNo, "Exit?") = vbYes Then
Me![tblQuoteLog subform]![LDS] = Date
If Me![tblQuoteLog subform]![TTime] > #11:45:00 AM# Then
Me![tblQuoteLog subform]![LDS] = Date + 1
End If
If Me![tblQuoteLog subform]![DatePrt] <= 2 Then
Me![tblQuoteLog subform]![RequestedDate] = Me![tblQuoteLog
subform]![LDS] + 4
ElseIf Me![tblQuoteLog subform]![DatePrt] >= 3 Then
Me![tblQuoteLog subform]![RequestedDate] = Me![tblQuoteLog
subform]![LDS] + 5
End If
Me![tblQuoteLog subform]![Designer] = "LDS"
Me![tblQuoteLog subform]![Drawing Done] = True
Else
'Do Nothing
End If
Click to expand...
Click to expand...
 
Top