Getting the correct dates, based on a calendar with check box (aka The Great Quest)

[jlgray0127]

Kind of a joke in the title for humor...
I have a scheduling database and need to only count and select dates in a query if the value of check box is Yes.
To make this is as simple as I can, I have a calendar table, which lists every day of the year for the next 10 years. It contains a check box value, which the user can select specific dates that are "Work Days"
I have another table that lists our production lines 1,2,3,4 and each process for that line, with the number of days we need to plan, to complete the process.
When I give a completion date, such as 09/20/2018, and I need 20 days for the process, I need the query to calculate from 09/20/2018, backwards the dates with a value of Yes in Work Day field to come with the start date.
So, if I need to finish on 09/20, and I am off ea sat and sun, and 09/03 is a holiday we are not working, I need it to return the value 08/22/2018, as my start date. That tells me if I start on 08/22, I have 20 work days to get to 09/20 for my completion date.
I've tried an old, and modified code, that I found in the forums, with help from another user, who modified it, trying to assist me. It's an actual work day module that is supposed to look at my holidays, and take them out, but it is counting holidays as a work day, and it takes for ever to run. Also, in a comment in the code from the original developer, it states that it may miss the holidays and basically, just deal with it. lol
I will attach the last sample we used, to try to get it to work. Thank you in advance for any assistance you can offer!



Sample_Vlad_Updatev3.zip

[isladogs]

Fine. What's the question?

[jlgray0127]

Updated my post! Sorry, accidently hit the post before I was ready!

[isladogs]

You certainly have and the details look very familiar .... as does the name in the attachment.
Before I look at it properly, can you confirm whether I'm right and, if so, provide links to previous threads

[jlgray0127]

Thank you for looking!

https://www.accessforums.net/showthr...620#post397620

[JSR]

I think this would be much easier with VBA than a query. The code simply locates the row of the calendar table that has the completion date. Then do .moveprev over and over, adding 1 along the way if that row is a work day, until the accumulated total days equals the days you need.

For example, if you need 20 days, ending on 9/20/2018, locate the row 9/20/2018 (make a unique index on the table on that date column for quick access), initialize a variable TotalDays with 1 if that completion date counts (start with 0 if it doesn't count), and go into a loop that ends when TotalDays = 20. When you exit the loop, the current record of the table is your start date.

[jlgray0127]

Thank you for the reply!
I'm not 100% sure what you're saying, though. in my attachment, I have the module I was trying to use, with help from Vlad. I'm not proficient with VBA, just enough to be dangerous! lol

Thanks!
Josh

[isladogs]

I was right. I do remember the thread from a few months ago.
As Vlad (Gicu) was so heavily involved before, I'm going to wait & see if he responds before investigating this myself

[jlgray0127]

Thank you!
Until I'd went back to the original, I had forgotten he was still open to helping assist in this! The project was put on a hold for a few months, and we'd thought we'd gotten it, until the 4th of July. Then it had thrown the calculations off, when we were around the holiday.
Thank you for looking at this!

[JSR]

Open the table Calendar in design view, go to the field Calendar Date, and change Indexed from No to Yes (No Duplicates) and save it.

Add this code to your module:

Public Function GetProductionStartDate(NeededDays As Integer, FinishDate As Date) As Date

Dim db As Database
Dim CalendarRst As Recordset
Dim TotalDays As Integer
Dim cont As Boolean

If NeededDays <= 0 Then
  MsgBox ("needed days must be at least 1")
  Exit Function
End If

Set db = CurrentDb()
Set CalendarRst = db.OpenRecordset("Calendar")
 CalendarRst.Index = "Calendar Date"     'unique index on Calendar Date

CalendarRst.Seek "=", FinishDate

If CalendarRst.NoMatch Then
  MsgBox (Format(FinishDate, "m/d/yyyy") & " is not in the calendar table!")
  Exit Function
End If

TotalDays = 0       'change initialized value to 1 if the finish date counts as one of the needed production dates
cont = True

Do While cont
  CalendarRst.MovePrevious
  
  If CalendarRst.BOF Then
    MsgBox ("not enough entries in the calendar date table!")
    Exit Function
  End If
  
  If CalendarRst![Working] Then
    TotalDays = TotalDays + 1
  End If        'else non-work days add zero to total days needed
  
  If TotalDays = NeededDays Then
    cont = False
  End If
Loop

GetProductionStartDate = CalendarRst![Calendar Date]
CalendarRst.Close
db.Close

End Function

I tested this by adding the following field to the query qryMaster_BusinessDates:

Start_Date: GetProductionStartDate(20,#9/20/2018#)

Replace the constant 20 with the number of days you actually need, and replace the constant #9/20/2018# with the actual production end date.

When I ran the query, Start_Date returned the date 8/22/2018.

For a query that only returns five rows, it seems slow. Nevertheless, it still works.