Criteria Needs to Ignore Weekends

[RichardAnderson]

Hello folks,



Still working on my first ever database and It is coming together nicely thanks to a lot of help I have received here. Thanks to everyone for that.

Now to my new issue...

In one of my forms, I display the records for builds that need to be produced either next day or the day after. The filter I used in the properties is...

ProduceByDate = Date() +2 And Completed in Null

This code works great except for 1 problem....

The purpose for this form is for the warehouse to know what builds are coming up within the next 2 days so he can get all the parts pulled and ready. But the current code is only helpful on Monday through Wednesday. On Monday this form will list builds for Tue and Wed. On Tue, it will show builds for Wed and Thur. And in Wed it will show builds for Thur and Fri. But when it comes to Thursday, it will only show builds for Fri and Sat. But we don't work on the weekend. On Friday it wouldn't show anything at all when it should show Monday's and Tuesday's builds.

Does anyone know a way to make this code ignore Sat and Sun and include Mon and Tue at the end of a week?

Thank you.

[June7]

Should that be 'is Null' instead of 'in Null'?

Try IIf:

Date() + IIf(WeekDay(Date())=>5,4,2)

[RichardAnderson]

Where do I put this? The form in question is not based off of a query but rather just the table. The filter I mentioned is in the forms filter property.

[RichardAnderson]

I decided to attach my database to make it easier to understand.
The form in question is frmProduction_Next2Days.

When you open frmMainForm, you will find it when clicking the "Production" button.


Edit1: Never mind... Your sig says the limit is 2MB on uploading a db. but when i tried it said the limit is 500k.

Edit2: Thank you.... Once zipped it got real small.

Attachment 13548

[RuralGuy]

The limit is 2MB *if* it is a ZIP file. Do a Compact and Repair 1st and then ZIP it.

[RuralGuy]

There is a Function in this thread that you may find useful: https://www.accessforums.net/access/...ule-37338.html

[RichardAnderson]

That is interesting but not what I need. I uploaded my db in my previous comment.

It will be easier for you to see how the db works and my needs. Keep in mind. This is my first ever db and I had zero experience in Access, VB, or VBA prior to this project. I am learning on the fly.

[RichardAnderson]

I should probably also mention that this database is build on top of another db I downloaded from this site. It applies user security using Active Directory. BUt I do not think I applied the securities to any pages yet.

[RuralGuy]

I think a filter something like: ProduceByDate=Date() + IIF(Weekday(Date(),2) >3,4,2) may work for you.

[RichardAnderson]

I think a filter something like: ProduceByDate=Date() + IIF(Weekday(Date(),2) >3,4,2) may work for you.

I will give this a go. Also,

How would I include the holidays that are in the table tblHolidays

[RichardAnderson]

OK... I tried what you said and this is what I have....

In the filter I have ProduceByDate=Date() + IIF(Weekday(Date(),2) >3,4,2) And Completed is Null.

Then I changed the ProduceByDate on record 3 to 8/27/2013 and record 5 to 8/26/2013. This is Mon and Tue next week.
Then I opened frmMainForm and clicked on the "Production" then "Next 2 Days" buttons. The only record listed was record 3 with a ProduceByDate of the 27th (Tuesday). Record 5 for the 26th (Monday) was not shown.

[June7]

You are using = operator. Maybe you need <=?

[RuralGuy]

You know, that is really not the right filter. It may need to be something like:
ProduceByDate =< Date() + IIF(Weekday(Date(),2) >3,4,2) And Completed is Null.

[RuralGuy]

Oops! June is right. <= not =<. As for a Holiday table, you will need a Function for that.

[RichardAnderson]

OK you 2, I got the filter to skip the weekends.

With all the talk about IIF and Weekday, I researched what these 2 functions do. After about 10 min. I felt I kinda understood it so with a little experience in Excel formulas, I wrote out something on paper. Then I tried it....

In the filter for frmNext2Days I put.... ProduceByDate = Date() + IIF(Weekday(Date(),2) Between 1 and 4,1,IIF(Weekday(Date(),2) = 5,3,IIF(Weekday(Date(),2) = 6,2,1))) And Completed is Null Or ProduceByDate = Date() + IIF(Weekday(Date(),2) Between 1 and 3,2,IIF(Weekday(Date(),2) = 4,4,IIF(Weekday(Date(),2) = 5,4,IIF(Weekday(Date(),2) = 6,3,2)))) And Completed is Null.

I put the producebydate in the table I changed a couple records to Monday and Tuesday of next week. When I went to that form it filtered correctly. I will Test it everyday next week to make sure everything is ok. But as of now, it seems to work. If it is OK, I will leave this unsolved till the end of next week while I test it.

Unless I can test it manually changing the system date to different days of the week.

My next step is to include holidays now. I will need these skipped as well.