Calculate passed working days

[megatronixs]

Hi all,

I need to calculate the passed working days and show them in a form. I should be also able to use the number in a query later on.

in excel I use the formula to get the days passed:

=IF(ISERROR(MATCH(F10,Dictionary!C:C,0)),NETWORKDAYS(D10, TODAY(), Dictionary!$E$2:$E$43),"Status Excluded")

D = "Date_uploaded" in access table "tbl_All_Cases"
F = "Status_Case" in access table "tbl_All_Cases"
c:c = dictionary case status
E:E = dictionary holidays
"Status Excluded" will show up in the cell if a case has one of the status from the dictionary
I created a table: tbl_Dictionary where there are 2 fields: "Case_Status" and "Holidays"

How can I translate the above formula into something that access will understand?

Greetings.

 

[megatronixs]

Hi all,

I found the below link helpfull, but it has a catch. The English bank holidays are backed in and I have no clue how to change that. Any one knows how to bypass the English bank holidays>

http://www.access-programmers.co.uk/forums/showpost.php?p=200746&postcount=21

Greetings.

 

[plog]

Can you explain your formula with data? Show me a few samples of what you put into it, and based on those values, what you expect out of it.

 

[namliam]

The English bank holidays are backed in and I have no clue how to change that. Any one knows how to bypass the English bank holidays>

Public Function CountDays(ByVal dteStartDate As Date, dteEndDate As Date) As Integer
 
    On Error GoTo Err_CountWeeks
    
    Dim intDays As Integer
    
    If dteStartDate > dteEndDate Then
        dteTemp = dteEndDate
        dteEndDate = dteStartDate
        dteStartDate = dteTemp
    End If
        
    dteTemp = dteStartDate
    
    Do While dteTemp <> dteEndDate + 1
        
        Select Case WeekDay(dteTemp)
            Case Is = 1, 7
                ' do nothing
            Case Else
                If Not DCount("DefinedDate", "qrySelectDate") = 1 Then
                    Select Case dteTemp
[COLOR="Red"]                        Case Is = DateSerial(Year(dteTemp), 1, 1), _
                            DateOfEaster(Year(dteTemp)) - 2, _
                            DateOfEaster(Year(dteTemp)) + 1, _
                            GetBankHoliday(DateSerial(Year(dteTemp), 5, 1)), _
                            GetBankHoliday(DateSerial(Year(dteTemp), 5, 25)), _
                            GetBankHoliday(DateSerial(Year(dteTemp), 8, 25)), _
                            DateSerial(Year(dteTemp), 12, 25), _
                            DateSerial(Year(dteTemp), 12, 26)[/COLOR]
                            ' do nothing
                        Case Else
                            intDays = intDays + 1
                    End Select
                End If
        End Select
        dteTemp = dteTemp + 1
    Loop
    
    CountDays = intDays

Exit_CountDays:
    Exit Function

Err_CountWeeks:
    MsgBox Err.Description, vbExclamation, "Error #" & Err.Number
    Resume Exit_CountDays
        
End Function

I do wonder where those bank holidays are coded in,


Other usefull samples:
http://www.access-programmers.co.uk/forums/showthread.php?t=238821&highlight=holidays
http://www.access-programmers.co.uk/forums/showthread.php?t=133329&highlight=holidays

 

[megatronixs]

Hi Plog,

In the excel sheet I have column D where the upload date is (when we got the case in our database) from that date I need to calculate how many days passed since then to see which are the oldest cases.

As for example I have a case that was uploaded on the 29/06/2015 and as per today it is 8 days old (not counting the weekend and including today)
Next example is a case uploaded on the 21/04/2015 and it is now 55 days old. These are the holidays Holidays that I have in the table tbl_Holidays.
01-Jan-2015
06-Jan-2015
06-Apr-2015
01-May-2015
04-Jun-2015
25-Dec-2015

I hope this will reflect what I'm trying to do.

Greetings.

 

[megatronixs]

Hi Namliam,

I found them a few minutes before your post, changed the days, but still I managed to mess it up and got the days counted bad

Greeting.

 

[plog]

I need to calculate how many days passed since then to see which are the oldest cases.

So you want to order a list of dates? I don't see how holidays and calculations fit into this. Just order the dates.

December 23rd comes before December 24th comes before December 26th comes before December 27th. Determining Christmas is in the middle of all that has no bearing on the sort.

 

[megatronixs]

Hi Plog,
Maybe you know to deal with it better
I got instructions to limit the cases a quelity checker could choose from to do the quality check. The boss' idea is to have a button that when you click on it, it will open the next case to do the quality check in the main form. To stop the "cherry picking", they only will need to press the button and then the oldest case that is ready for quality check will pop up. Maybe I just came with a silly idea to just calculate which is the oldest one and have this one appear on top so with a button it will pick up that one (if more with the same number, the first one on top).

So, in your opinion what would be the best way to solve this?

Greetings.

 

[plog]

The boss' idea is to have a button that when you click on it, it will open the next case to do the quality check in the main form

So this is what you want to actually achieve and your initial post was an intermediate step to it?

If so, here's what I would do:

First, since you didn't provide one, let's say your form is based on table Z. Z has a primary key field of zID. Also, it has a field called zDate which is the one you want to use in determining the oldest record.

I would create a query using Z to determine all those records that still need to be processed. I would have zID and zDate be the fields it shows and then I would apply whatever criteria you have to determine a record still needs to be processed. Let's call that query Y.

Then for that button you talk about, I would write code to first determine the zID of the oldest record in Y. I would do this using DMin to get the oldest zDate in Y, then use Dlookup to get the zID of that particular record in Y. Then I would use that zID to construct the WhereCondition argument of a DoCmd.OpenForm (https://msdn.microsoft.com/en-us/library/office/ff820845.aspx ) and open the form to the correct zID.

 

[namliam]

Select zID from Z where <determine the records> order by Casedate

That would do way with the DMin and Dlookup...

 

[megatronixs]

Hi all,

Please find attached a dummy database.
To make things simpler and to have a smaller list, it needs 2 more criteria: 1= for checking, 2=new client.
There could be 40 or more cases and we simply need to have the first oldest case on top. Maybe it is possible that I'm just looking for a more difficult solution than it should be :-(

Greetings

 

[plog]

That provides us with nothing new. You have a main table in there, a form with a button on it to take you to the next record. That's exactly how I envisioned your system in my post prior to giving you advice.

You should read post #9 and attempt to achieve that. Then post back your database if you are having issues.

 

[megatronixs]

Hi Plog,

Sorry, uploaded the wrong one :-(
Just need to think about the button still. When the database opens, they see first the search form so users can look for their cases or a particular one. I was maybe thinking about to put the button there visible for only those users who will do quality check. They could click the button and it will open the main form with already the correct case to work on. The form as it is now in the example database, as the form with the record source set to the query, that is great for the quality checkers, but not for all. how can I solve that?

Greetings.

 

[plog]

A form that directly interacts with data (add/edit/delete) shouldn't be based on a query. The form in the database should have tbl_main_data as its data source.

And, you lost me with that last post. You finished with 'how can I solve that' and I have no idea what 'that' is.

Can you better articulate what it is you want to achieve? Or formulate a better question?

 

[megatronixs]

Hi Plog,

Sorry for so much confusion. All I want to archive is that when the button is pressed on the form the results of the query populate the form. It should always take the first record from the query.
I hope this makes better sense.

Greetings.

 

[plog]

To do that you put an OnClick event on the button. In that code, you use DMax to get the date of the last record in your query, then a DLookup to get the actual ID of that record in your query. Then you can use that ID as the WhereCondition of a DoCmd.OpenForm to open your form to that specific record.

You should still base that form off of the table and not the query.

 

[megatronixs]

Thanks Plog,

I will try it out and see if I will get it right the first time

Greetings,