Criteria Needs to Ignore Weekends

[RichardAnderson]

OK I give up on trying to get Holidays skipped. I'm gonna have to find someone and pay them to take my current db and make is skip over holidays and weekends. Everything else in this db I am picking up on.

[June7]

Sorry I couldn't fix your form. When I tried to open it I ran into other code bugs I didn't want to try tracking down. If you can get someone on-site, should be a fairly quick fix, maybe $100 worth.

[RichardAnderson]

Sorry I couldn't fix your form. When I tried to open it I ran into other code bugs I didn't want to try tracking down. If you can get someone on-site, should be a fairly quick fix, maybe $100 worth.

Code bugs? When you open the DB? I dont get any errors at all.

[June7]

I can't open the db right now. Seem to remember there were error messages when I first open the db that appeared to have something to do with the Active Directory coding. Also think there was a reference to a form that errored which might be just because I didn't open forms properly but since the db errored on opening, hard to figure out proper procedure.

Seems like such a simple edit is required. As stated, I tested in my db calling the custom function AddWorkDays from Filter property and it worked. But when I tried to open your form I hit code error (reference to a form I think) and gave up from there.

[amrut]

OK I give up on trying to get Holidays skipped. I'm gonna have to find someone and pay them to take my current db and make is skip over holidays and weekends. Everything else in this db I am picking up on.

I am ready to jump in !!

Case in point. This coming up Monday is Labor Day. Right now, when I click the button, The 5 days I get in the result are 8/28, 8/29, 8/30, 9/2, and 9/3. As you can see, the filter I have in the filter property on the form is working. It is skipping the weekend. But 9/2 needs to be skipped over too because it is a holiday. So I need my result on the form to actually be 8/28, 8/29, 8/30, 9/3, and 9/4.

Further, please reconfirm that holiday ( like 9/2 above) needs to be completely ignored ?

[RichardAnderson]

I am ready to jump in !!


Further, please reconfirm that holiday ( like 9/2 above) needs to be completely ignored ?

All holidays need to be skipped over. Take today for instance. Today is 8/29. When the button to display frmProduction_Next5Days in the subform is clicked, The result should be a list of builds whose ProduceByDate is 8/30, 9/3, 9/4, 9/5, 9/6. Sat and Sun skipped over because they are weekends, and Mon skipped over because its a holiday. But still showing a total of 5 workdays ahead.

[June7]

Then the expression in Filter property needs to be:

ProduceByDate Between Date()+1 And AddWorkDays(Date(),5) And Completed Is Null

[amrut]

Then the expression in Filter property needs to be:

ProduceByDate Between Date()+1 And AddWorkDays(Date(),5) And Completed Is Null

In above case, the filter accounts for the holiday by adding a day but fails to skip the holiday itself.

Here is what I tried -
Created a query named qryNext5Days and later used it to filter the form -

SELECT TOP 5 tblBuilds.ProduceByDate
FROM tblBuilds
WHERE (((tblBuilds.ProduceByDate)>Date()) AND ((tblBuilds.Completed) Is Null) AND (((SELECT Count(tblHolidays.ID) AS CountOfID
FROM tblHolidays
WHERE (((tblHolidays.HolidayDate)=[ProducebyDate]))))=0) AND ((Weekday([producebydate],0))<6))
ORDER BY tblBuilds.ProduceByDate;

The db is attached.
@RichardAnderson - Test it. If it is OK, I am waiting for my reward $$ !!

[June7]

amrut, that query looks nice and might be a simpler approach for the OP.

However, not sure what you mean by saying the function fails to skip the holiday. It does calculate an end date that is 5 workdays from the current day (includes the current day as one of the 5 workdays). All records falling within the range should be returned and because they don't work on weekends and holidays, shouldn't be any for those days.

However, need a minor adjustment to the expression because OP doesn't seem to want the current day records included and BETWEEN AND is inclusive for the range start/end values. Also, I forgot to include []:

[ProduceByDate] Between Date()+1 And AddWorkDays(Date(),6) And [Completed] Is Null

[amrut]

All holidays need to be skipped over. Take today for instance. Today is 8/29. When the button to display frmProduction_Next5Days in the subform is clicked, The result should be a list of builds whose ProduceByDate is 8/30, 9/3, 9/4, 9/5, 9/6. Sat and Sun skipped over because they are weekends, and Mon skipped over because its a holiday. But still showing a total of 5 workdays ahead.

The function will add one more day to the filter's upper limit (to account for the holiday) but the holiday will still be displayed on the form as it is falling within the filter criteria. Am I right ?

[June7]

It is my understanding that fits the OP's requirements. Return records for the next 5 workdays and since there shouldn't be any records with the weekend or holiday dates, no records will show those dates. And if someone did happen to misdate record or, heaven forbid, worked on Saturday and created a record, it will be captured by the date range criteria. Should record be ignored just because it doesn't conform to SOP? That's one way to upset and lose customers.

[RichardAnderson]

Thank you guys... I will download what you did and see what I can pick up from it.

Thanks so much