Criteria Needs to Ignore Weekends

[RuralGuy]

Works for me.

[June7]

As RG noted, will need a function for excluding holidays. Might as well include weekends in the function. This is common topic in forum.

Search: holiday function

Here is one: https://www.accessforums.net/program...elp-34578.html

Or Bing: VBA workdays function

[RichardAnderson]

As RG noted, will need a function for excluding holidays. Might as well include weekends in the function. This is common topic in forum.

Search: holiday function

Here is one: https://www.accessforums.net/program...elp-34578.html

Or Bing: VBA workdays function

This is not helping me. I have also looked all over the web and all I can find is how to calculate working days between 2 dates. That is not what I need.

I need to make the form show a list of scheduled jobs for the next 5 days. Skipping OVER holidays and weekends until 5 days are included in the result.

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.

The trickiest part is some holidays are undetermined. We know were off on Dec 25th. But since Xmas is on Wed this year, I do not know yet is the boss is going to include both the day before and the day after. But if holiday days are in a table, I am sure I could just add more dates when needed

I am still searching, but again, everything I find is needing a start and end date and just calculated workdays

[June7]

You need a function to calculate the end date of a range based on a given start date. The referenced link is one example of a function that determines if a date is a weekend or holiday and excludes if it is. Adapt/change as appropriate for your situation. Or try this one http://answers.microsoft.com/en-us/o...0-d9e0b1eb579b.

[RichardAnderson]

You need a function to calculate the end date of a range based on a given start date. The referenced link is one example of a function that determines if a date is a weekend or holiday and excludes if it is. Adapt/change as appropriate for your situation. Or try this one http://answers.microsoft.com/en-us/o...0-d9e0b1eb579b.

Yea... I just have no idea how to accomplish this. On alot of sites they say make a table with a list of holidays and "call a function". I have no idea what "call a function" means. Or how to tie it into my existing filter on my form so both holidays and weekends are skipped over. I think I am getting confused and lost in all the verbage. When I see things like "start date" I stop. cause I dont have a start date. Unless date() is the start date.

Maybe I need to change my thought process. I just need this form to show records for 5 days out. If one of those 5 days is a weekend day or a holiday, then it shows 6 days out. If 2 of those days are such days then it shows 7 days out and so on.

[June7]

The start date is whatever you want it to be. It can be the current date or a date field from table.

You will need to use a function to accomplish what you describe.

If you don't understand basic programming concepts, you will not get very far in this effort. Start with a review of http://office.microsoft.com/en-us/ac...010341717.aspx

A function can be called in query or textbox.

Example of calling an intrinsic function in a textbox is:

= Date()

That function returns the current date.

Example of calling a custom user defined function (UDF) with arguments:

= AddWorkDays(Date(), 5)

[RichardAnderson]

OK, I went to that link and tried that.

I created a table named "tblHolidays". It has 3 fields. ID, HolidayName, HolidayDate. I then populated the table with holidays for the remainder of this year and next year up to Labor Day.

I then made a module and named it "basWorkdays".
In this module I put...

Public Function AddWorkDays(StartDate As Date, NumDays As Integer) As Date 
  '....................................................................
  ' Name: AddWorkDays
  ' Inputs: StartDate As Date
  ' NumDays As Integer
  ' Returns: Date
  ' Note that this function has been modified to account for holidays.
  ' It requires a table named tblHolidays with a field named HolidayDate.
  '....................................................................
  Dim rst As DAO.Recordset
  Dim dbs As DAO.Database
  Dim dtmCurr As Date
  Dim intCount As Integer
  On Error GoTo ErrHandler
  Set dbs = CurrentDb
  Set rst = dbs.OpenRecordset("tblHolidays", dbOpenSnapshot)
  intCount = 0
  dtmCurr = StartDate
  Do While intCount < NumDays
    dtmCurr = dtmCurr + 1
    If Weekday(dtmCurr, vbMonday) < 6 Then
      rst.FindFirst "[HolidayDate] = #" & Format(dtmCurr, "mm\/dd\/yyyy") & "#"
      If rst.NoMatch Then
        intCount = intCount + 1
      End If
    End If
  Loop
  AddWorkDays = dtmCurr
ExitHandler:
  rst.Close
  Set rst = Nothing
  Set dbs = Nothing
  Exit Function
ErrHandler:
  MsgBox Err.Description, vbExclamation
  Resume ExitHandler
End Function

I then opened the form in question frmProduction_Next5Days and it shows 5 days skipping the weekend but NOT Monday 9/2/2013 which is a holiday and listed in the table I made.

I am attaching the database so you can see. I am totally stumped. The column in question in the Produce column.

[June7]

I can't find the function used in those forms. I did find that nightmare of a filter criteria expression. Instead try:

ProduceByDate = AddWorkDays(Date(),2) And Completed is Null

[RichardAnderson]

I can't find the function used in those forms. I did find that nightmare of a filter criteria expression. Instead try:

ProduceByDate = AddWorkDays(Date(),2) And Completed is Null

I will try that.

The function is named basWorkdays. At least what I understand a function to be.

[RichardAnderson]

ProduceByDate = AddWorkDays(Date(),2) And Completed is Null

I put this as my filter and only got today's builds not the next 5 days. I will keep trying.

[June7]

basWorkdays is the name of the module the function is in. The function is named AddWorkDays. How is it this db has so much sophisticated code but you don't understand modules and procedures?

I know the function works. I tested it by calling from the VBA immediate window. I tried opening the form but encountered issues with other code.

[RichardAnderson]

basWorkdays is the name of the module the function is in. The function is named AddWorkDays. How is it this db has so much sophisticated code but you don't understand modules and procedures?

I know the function works. I tested it by calling from the VBA immediate window. I tried opening the form but encountered issues with other code.

I build my DB on top of a sample DB from these forums that uses active directory to limit access to forms based on membership of AD groups. Everything I learned is from these forms. I only learn specific things i need to get done what i need to get done. I didnt do any of the modules except basWorkdays. I just wont to get this project done so I can get back to my normal job. If it was up to me I would have a consult with a professional and have them do it. But I am almost done now. Just have a few features like the "Next 5 Days" to add. I will attach it.

[June7]

Interesting sample. I tried some code using Active Directory a few weeks ago (prompted by another thread) but didn't get very far with it.

I did a test in my db of the AddWorkDays function called in Filter property and it does work.

[RichardAnderson]

Interesting sample. I tried some code using Active Directory a few weeks ago (prompted by another thread) but didn't get very far with it.

I did a test in my db of the AddWorkDays function called in Filter property and it does work.

I would love to see that. When I put AddWorkDays into my filter it prompts for user input which I dont need.

[June7]

Okay, check it out.