using a VBA Module result in a SELECT Query

[ricardusmaximus]

I'm running a SELECT Querry that has a Expression in it [Turnaround days]that calls a VB Function that returns the number of Workdays within a specified time period. The querry works fine except when I try to use a HAVING Clause to limit the results to >= six days. The error that returns" The expression is typed incorrectly or too complex to be evaluated. (It works fine except when I want to place criteria on it.)

I can ORDER BY the [Turnaround days] and even use it in other expressions, but I just can't apply criteria to it in a HAVING or WHERE Clause.

SELECT DISTINCTROW [Consultant Sub-Category_Sub].Activity_Nr, [Consultant Sub-Category_Sub].[Initial Assignment]
   , [Consultant Sub-Category_Sub].Activity_Role, [Consultant Sub-Category_Sub].Activity_Status, [Consultant Sub-Category_Sub].Activity_Received
   , [Consultant Sub-Category_Sub].Activity_Start, [Consultant Sub-Category_Sub].Activity_End
   , Timings.Category, Timings.[Sub-Category]
   , Count(*) AS [Count Of Consultant Sub-Category_Sub]
   , Workdays([Activity_Received],[Activity_End])
      AS [Turnaround days]
   , Timings.[Total Time], [Consultant Sub-Category_Sub].On_Hold_Start
   , Timings.Comments
   , Int([Activity_End]-[Activity_Received]) AS [Previous calc Turnaround days]
FROM Timings INNER JOIN [Consultant Sub-Category_Sub] 
   ON Timings.Activity_Nr = [Consultant Sub-Category_Sub].Activity_Nr
GROUP BY [Consultant Sub-Category_Sub].Activity_Nr, [Consultant Sub-Category_Sub].[Initial Assignment]
   , [Consultant Sub-Category_Sub].Activity_Role, [Consultant Sub-Category_Sub].Activity_Status
   , [Consultant Sub-Category_Sub].Activity_Received, [Consultant Sub-Category_Sub].Activity_Start
   , [Consultant Sub-Category_Sub].Activity_End, Timings.Category
   , Timings.[Sub-Category], Timings.[Total Time], [Consultant Sub-Category_Sub].On_Hold_Start
   , Timings.Comments, Int([Activity_End]-[Activity_Received])
HAVING (((Timings.Category)="questions") 
   AND ((Workdays([Activity_Received],[Activity_End]))
      >=6));
 

[zmbd]

Open your query in SQL view and run from there... often the line with the error will be pointed to you.

[ricardusmaximus]

Workdays As Turnarounddays. The function works. I just can't put any criteria on it. Does anyone know how it should be written??

[ricardusmaximus]

Thank you. I fixed this by using an INTO Clause to save the results of the query that made use of the User Defined Function [Turnaround days]. The function returns a number in the same way that an expression would return a number. I can do operations on the expression in the querry, but not so with the user returned values. The expression in the query is [Previous calc Turnaround days].

SELECT DISTINCTROW [Consultant Sub-Category_Sub].Activity_Nr, [Consultant Sub-Category_Sub].[Initial Assignment]
   , [Consultant Sub-Category_Sub].Activity_Role, [Consultant Sub-Category_Sub].Activity_Status, [Consultant Sub-Category_Sub].Activity_Received
   , [Consultant Sub-Category_Sub].Activity_Start, [Consultant Sub-Category_Sub].Activity_End, Timings.Category, Timings.[Sub-Category]
   , Count(*) 
      AS [Count Of Consultant Sub-Category_Sub]
   , Workdays([Activity_Received],[Activity_End]) 
      AS Turnaround_days
   , Timings.[Total Time], [Consultant Sub-Category_Sub].On_Hold_Start
   , Timings.Comments
   , Int([Activity_End]-[Activity_Received]) 
      AS [Previous calc Turnaround days] 
INTO tblTest
FROM Timings 
   INNER JOIN [Consultant Sub-Category_Sub] 
      ON Timings.Activity_Nr = 
         [Consultant Sub-Category_Sub].Activity_Nr
GROUP BY [Consultant Sub-Category_Sub].Activity_Nr
   , [Consultant Sub-Category_Sub].[Initial Assignment], [Consultant Sub-Category_Sub].Activity_Role
   , [Consultant Sub-Category_Sub].Activity_Status, [Consultant Sub-Category_Sub].Activity_Received
   , [Consultant Sub-Category_Sub].Activity_Start, [Consultant Sub-Category_Sub].Activity_End
   , Timings.Category, Timings.[Sub-Category], Timings.[Total Time]
   , [Consultant Sub-Category_Sub].On_Hold_Start, Timings.Comments
HAVING (((Timings.[Sub-Category])='General Questions' 
      Or (Timings.[Sub-Category])='Compliance Testing' 
      Or (Timings.[Sub-Category])='Conference Calls' 
      Or (Timings.[Sub-Category])='Operational Issues' 
      Or (Timings.[Sub-Category])='PPA' 
      Or (Timings.[Sub-Category])='Plan Document Interpretation' 
      Or (Timings.[Sub-Category])='Plan Administration Questions or Errors' 
      Or (Timings.[Sub-Category])='Controlled Group/Ownership' 
      Or (Timings.[Sub-Category])='Loan And/Or Corrections')
   AND (([Consultant Sub-Category_Sub].On_Hold_Start)
       Is Null));

[zmbd]

Sorry, I re-read your OP just now, I was completely off the mark. Let me take a closer look at your original code.

BTW: I'm not a fan of temporary tables, they can bloat your database something fierce if you have a lot of data moving in and out or deleting them....

[zmbd]

Try replacing your having clause with this one:

HAVING ((Timings.Category ="questions") AND ((Workdays([Activity_Received],[Activity_End])>=6));

[ricardusmaximus]

generates the same issues. I tested a simple function that returns the sqaure of an integer. Works in the SELECT portion of the Query, but not in the HAVING Clause.

[zmbd]

Hmm... you've got something funky going on... this is from one my production databases:

HAVING (((zturnaround([timeline].[est_startdate],[timeline].[est_enddate]))<=30));
 

and it pulls... well... a lot of data.

Have you tried removing the (Timings.Category ="questions") AND leaving only the function in the HAVING clause?

[NeoPa]

Why don't you try stripping the problem down to its simplest form and posting the SQL of that, its results and why they aren't correct in here for us to consider.

Wading through mountains of SQL which is complicated by more than just volume is not a good way to work (That's actually one of the most important debugging techniques you'll ever learn as well).

Don't use GROUP BY unless it doesn't fail unless you do. Keep it as simple as you can do it.