MSDN Workdays Function results cannot be averaged

[COS_T]

I've spent many hours searching forums for some conversation/solutions about this, but with no luck so here goes:

I started with the MSDN basic VBA code for Weekdays and Workdays functions to give me the number of workdays between two dates.

(see:
http://msdn.microsoft.com/en-us/library/dd327646(office.12).aspx
)

I changed the appropriate fields to match my database (startdate = Date_recd; enddate= ePAF_COSApp), and have created the corresponding tblHolidays. In every "date" instance, these are formatted as date/time.

The results in the query are correct. For example:
WrkDays-RecdtoCOSApp: Workdays([Date_recd],[ePAF_COSApp])
Date_recd: 9/1/09
ePAF_COSApp: 9/16/09
9/7/09 was a holiday
11 workdays

I need both the number of workdays and average of workdays across a given criteria.

Using the query results for the calculated workdays ([qryreceived-COSAPP-HRAPP_1]), I created a separate simple query that calculates the average workdays for each form type being processed:

SELECT [qryreceived-COSApp-HRApp_1].eForm_Type, Avg([qryreceived-COSApp-HRApp_1].[WrkDays-RecdtoCOSApp]) AS [AvgOfWrkDays-RecdtoCOSApp]
FROM [qryreceived-COSApp-HRApp_1]
GROUP BY [qryreceived-COSApp-HRApp_1].eForm_Type;

Problem: I get a "data type mismatch in criteria expression" error. The problem is with "WrkDays" ... it's like it's not reading the number as an integer.

I'm convinced that it has to do with the Workdays VBA coding regarding the strWhere and Holiday count... but can't seem to put my finger on exactly what's wrong.

I could be way off base, and appreciate any advice.

COS_T

 

[the_net_2.0]

so you're viewing a query that this function runs on every record? and the value it is outputting your claiming is not the right type?

if that is the case, put a break pt in the function right before the last line (e.g. - the line that issues the return value). open the 'watch window' in the editor. add the variable that's in question to the list and when you get to the break point, look at what the window is telling you under the 'type' column.

that should help shed light more than anything.

 

[COS_T]

Thanks for the response...

Yes and yes. The type is integer.

Just to ensure that integer wasn't causing a problem with the average, I changed Workdays, nWeedkays, and nHolidays to Double... re-running the check you recommended now shows the type as double.

I quadruple error checked the function, and there are no problems with it... it's providing the output as type:double and in the calculation query, it provides the expected (correct) results.

So let me re-pose this question, thinking there's something wrong with the query where I'm requesting the average (I'm simplifying the language of this query for better readability):

Using the query results for the calculated workdays ([qryCalcWkdays]), I created a separate, simple query that calculates the average workdays for each form type being processed:

SELECT [qryCalcWkdays].eForm_Type, Avg([qryCalcWkdays].[WrkDays]) AS [AvgOfWrkDays]
FROM [qryCalcWkdays]
GROUP BY [qryCalcWkdays].eForm_Type;

Error: "data type mismatch in criteria expression"

Oddly, I can write an expression to add or subtract... by row - it's just whenever I want to use any of the "totals" functions (average, sum, min, max, etc...) that I get the data type mismatch error.

I'm not certain what additional information to provide that may help with troubleshooting... please ask... and thanks for any continued advice...

 

[the_net_2.0]

cos,

your problem is complicated so if you want to upload a sample that has the same problem I, or others will take a look.

I do know a few things that cause this error. Firstly, concating two different numeric data types can throw this error on rare occasions. Secondly, your expression in the query looks like this:

expr1-expr2: (calculation)

there may be an issue with the NAME of the field there, because there is a dash in it, which might be misinterpreted by the system. I would change it now. Thirdly, you have not posted the function you're using, so further analysis of this is really at its end.

upload a file, or at least post the code that you're calling and we'll see if we can help you out.

 

[COS_T]

Net 2.0,

Thank you for your request and I apologize for taking so long to reply... work took me in another direction for a while. I'd like to provide a simplified version of my DB to post and get feedback, but I can honestly say this won't be something I'll be able to do anytime soon, given the time it will take to clean of private information before posting.

Unfortunately, the need to provide output was pressing, so I created a good query, exported it to excel, and ran date analyses/averages in excel. It's frustrating that something so simple in excel is so difficult (with having to code) in access.

Again, thanks for taking the time to provide feedback.
T.