Help with Query

[Vergy39]

I have a database that keeps track of the number of days an inquery is with an employee. I use a function called fNetWorkdaysDecimal that counts the number of days between 2 dates. This has been working great. Unfortunately, the dept now has a research team that will take an inquirey from an employee for a time period to do some research. I need to subtract this time from the total days. I have added the columns "SentForResearch" and "BackFromResearch" I created the qry called Qry_ResearchDays, but get errors. The problem is not all issues are sent for research. I tried adding a NullToZero" function, but failed miserably. Any assistance is greatly appreciated. I have attached a copy of the test database. "tblIssues houses the main data.

Thanks
David V

 

[CBrighton]

Try something like:

NumOfDaysWithEmployee: DateDiff("d",[StartDate],[EndDate]) - DateDiff("d",[SentForResearch],[BackFromResearch])

StartDate & EndDate should be replaced with your field names. This should calculate the number of days between the two specified dates then minus the number of days it was away with researchers.

EndDate could also be replaced with Date() or Now() if you will always want to search up to today's data.

 

[Vergy39]

Thanks CBrighton. This seems to work, however, I need the result to be in decimal form. This is returning whole days, when actually they only have it for partial. But I do appreciate the help and will try to make this work.

Thanks

 

[Vergy39]

Oh, and I forgot to mention that the calculation cannot include weekends. That is why I use that fNetWorkdaysDecimal function that is on the database. Again, I do appreciate your help.

Thanks
David V.