Solved Cast date to and AVG data mismatch (1 Viewer)

[chizzy42]

Hi Hope all is well, I'm hoping someone can give me an explanation please to what I'm doing wrong here. I've got a query that I was using to calculate the days open on an unclosed entry. I then found a database from pat hartman that excluded weekends https://www.access-programmers.co.uk/forums/threads/sample-date-functions.238821/ so thought id try it in case I ever have to cut out weekends.
I can get the days open ok from the query below

SELECT tblRma.registered, tblRma.dateremoved, businessdays([registered],[dateremoved]) AS days, tblRma.removed
FROM tblRma
WHERE (((tblRma.removed)=True));

I then assumed i would have to cast the results as double as the initial type was date?

SELECT CDbl(qryDaysNoWeekend.days) AS Expr1
FROM qryDaysNoWeekend;

So now I thought the data was in double type, I then tried to average it

SELECT AVG(qryDaysOpen.Expr1)
FROM qryDaysOpen;

...then got a data type mismatch in criteria expression...bit stumped why I can't take an average after casting to a numeric. If anyone can give me a pointer please much appreciated

thanks

 

[CJ_London]

the decimal part of the double is the time element, if you only want the days then try cLng rather than cDbl

and perhaps try

CLng(1 * qryDaysNoWeekend.days)

 

[chizzy42]

Hi CJ_London, thanks for the reply i tried the above and still get a data mismatch.

 

[MajP]

You have to use square brackets around the field name used in an expression. Access will automatically do that in QBE, but if you really copied that from the SQL window or you are doing that via code then try:
cdbl([Days])

This statement does not make sense

I then assumed i would have to cast the results as double as the initial type was date?

buisnessDays returns an integer not a date.

 

[chizzy42]

Hi , thanks to both to you for your time, when i sifted through the data after the first query to find the days open i noticed the last two entries didn't have a removed date entered and there were to error entries the the days column., looks like that didnt help. Noticed also two Days values had -2 values for an entry entered and closed in the same day others seem ok, need to have a look at that...have a good day to you both