calculate the expected resolution time

[megatronixs]

Hi all,

I have in a databasae a field called [date_received] where the date is saved when a case comes in. then there is thecombo box [query_categorization] that has different values. If value is "non standard" then we have 5 days for the case, if something else selected, then 2 days.


How can I get the expected resolution time calculate? I tried with:

=IF([query_categorization]="non standard]),(=Date + 5),(=Date + 2))

but no luck

any clue where I went wrong? it is just an unbound text box I try to use.

Greetings.

[ItsMe]

I suggest you use the AfterUpdate of your combo to troubleshoot this. You could use the following VBA test some different expressions and determine the .Value of your combo.

MsgBox "The Combo Value is: " & Me.query_categorization.Value
MsgBox IIf(query_categorization = "non standard", Me!date_received + 5, Me!date_received + 2)

[megatronixs]

Hi,

I was on a sick leave for some time and had no chance to get back to this :-(
I did run the above code and it is really nice. I did some small changes and was able to get the date into a field. The only thing that happens when I reopen the form, the expected date is gone :-(

Private Sub cmbo_query_categorization_AfterUpdate()
'MsgBox "The Combo Value is: " & Me.cmbo_query_categorization.Value
'MsgBox IIf(cmbo_query_categorization = "non standard", Me!date_received + 5, Me!date_received + 2)
Me.expected_resolution_time.Value = IIf(cmbo_query_categorization = "non standard", Me!date_received + 5, Me!date_received + 2)
End Sub

Any chance this could be still there when the form is open again, or at least write the date to the field [expected_resolution_time]

Greetings.

[ItsMe]

What is the control source of expected_resolution_time? If it is bound to a field, the result of your IIf will be assigned to that field. You need to test various records to make sure your IIf is producing the desired results.

[megatronixs]

Hi,

I made it work :-) thanks to you :-)
I had per mistake the control source unbound, so, it just put the date there, but did not save it.

Greetings and thanks a lot for making it possible for me to learn something new :-)

[ItsMe]

Glad to hear you were able to get it working.

[megatronixs]

Hi,

Now they asked me to skip the weekends. Now it needs to calculate only working days.
I tried to use the below, but I get only 0, any chance you can see where I go wrong?

Private Sub cmbo_query_categorization_AfterUpdate()
'MsgBox "The Combo Value is: " & Me.cmbo_query_categorization.Value
'MsgBox IIf(cmbo_query_categorization = "non standard", Me!date_received + 5, Me!date_received + 2)
Me.expected_resolution_time.Value = IIf(cmbo_query_categorization = "non standard", Me!date_received = Weekday(vbMonday) + 5, Me!date_received = Weekday(vbMonday) + 2)
End Sub

Greetings.

[ItsMe]

The Weekday() function returns a value between 1 and 7, depending on what date you provide it.

It sounds like what you need is a custom function that counts only days considered to be working days. Here is one example.
https://msdn.microsoft.com/en-us/lib...ffice.12).aspx

[megatronixs]

Hi,

Thanks a lot. It looks pretty difficult so much code. I will give it a try and if I can solve it, I will share it so others can make use of it too.

Greetings.

[ItsMe]

It is a lot of code but you do not have to worry about all of the code inside of the function. Similar to using any built in function, like Date() or Weekday(), you call it and maybe pass it some arguments. You do not concern yourself with how Microsoft gets the Weekday() function to work or what code they used. You only care about the arguments you need to pass it and the value it returns.

Copy the function and place it in a Standard Module. Name the standard module something other than a Reserved Word or the name of the function. In other words, do not name your Standard Module, "Workdays".

Then use the function you created/pasted by calling it from within a form.

dim intResult as integer
dim dtStartDate as date
dim dtEndDate as date

dtStartDate = Date
dtEndDate = 12 / 25 / 2016

intResult = Weekday(dtStartDate, dtEndDate)
msgbox intResult

[megatronixs]

Hi,

I used your function (I called it "WorkdaysTest", I know, sounds silly). then I use the below code:

Private Sub cmbo_query_categorization_AfterUpdate()
'MsgBox "The Combo Value is: " & Me.cmbo_query_categorization.Value
'MsgBox IIf(cmbo_query_categorization = "non standard", Me!date_received + 5, Me!date_received + 2)
Me.expected_resolution_time.Value = IIf(cmbo_query_categorization = "non standard", Me!date_received = WorkdaysTest() + 5, Me!date_received = WorkdaysTest() + 2)
End Sub

I guess I go some where wrong, but no clue where.

Greetings.

[ItsMe]

A common mistake is trying to accomplish too much at once. If you do not understand how the function works or how to get it to work, why not start by answering those questions?

I suggest you start with a new blank DB and test the function I linked to in post #8. Test the function by creating a Standard Module in your new DB. Save the new module and name the Standard Module, Module1. Now, paste the Function from the link in post #8 into your new Standard Module. Save the module.


Now, create a new form and place a command button on the new form. Save the new form. Create a click event for your new Command Button and paste the code from post #10 in the click event procedure. Save the form.

Now test the button on your new form by opening the form in Form view and clicking the button.

[ssanfu]

The first problem is the IIF() statements is incorrect.

IIf(cmbo_query_categorization = "non standard", Me!date_received = WorkdaysTest() + 5, Me!date_received = WorkdaysTest() + 2)

You cannot have the equals sign in the clause - the result returned is the result of comparing "Me!date_received" with "WorkdaysTest() + 5" (a boolean result - TRUE or FALSE).

The second problem is that the parameters for the "WorkdaysTest" function are missing. You need to have the "StartDate" and "EndDate"
Is would look something like

IIf(cmbo_query_categorization = "non standard", Me!date_received + WorkdaysTest(Me!date_received,Me!date_received+5) , Me!date_received + WorkdaysTest(Me!date_received,Me!date_received+2))

But I don't think the workdays function is what you want.

Now they asked me to skip the weekends. Now it needs to calculate only working days.

If I understand correctly, if "cmbo_query_categorization" = "non standard", you have "Me!date_received" plus 5 days for some action to happen.
If "Me!date_received" plus 5 days falls on a weekend, you should advance the date to the next weekday (Monday). So you need to determine if "Me!date_received" plus 5 days falls on a Saturday or Sunday, not determine the number of week days between two dates.

Paste the following code into a standard module

Public Function SkipWeekends(pDate As Date) As Integer
    '    Debug.Print Weekday(pDate)
    Select Case Weekday(pDate)
        Case 1         'Sunday - add 1 day
            SkipWeekends = 1  
        Case 7         'Saturday - add 2 days
            SkipWeekends = 2  
        Case Else     'weekday - add 0 days
            SkipWeekends = 0
    End Select

End Function

Then use

Private Sub cmbo_query_categorization_AfterUpdate()
      Me.expected_resolution_time = IIf(Me.cmbo_query_categorization = "non standard", CDate(Me!date_received) + 5 + SkipWeekends(CDate(Me!date_received) + 5), CDate(Me!date_received) + 2 + SkipWeekends(CDate(Me!date_received) + 2))
End Sub

I had to add the CDate() function because I was using unbound text boxes to test.


NOTE: you don't have to add ".Value" because the value property is the default property.

[ItsMe]

Yeah, I think I misunderstood the objective and the Select Case is the way to go.

[megatronixs]

Hi Ssanfu,

That works like crazy :-) thanks.
I was just to blind to see it all. I come from excel and to get used to diferent aproach of access vba, it is kind of hard to get to it.

Thanks both of you, you saved me plenty hours to finish something really hard for me.

Greetings.