summing time

[ldappa]

Hi,

I have looked on the message boards but cannot find this. I have a query
that has a field Tspt which is created from =timemin
datediff("n",intime,outtime). I have put Tspt on a report and would like to
total this column but get data type mismatch when using sum(tspt).
What would be the appropriate way, I have tried numerious senerios with no
sucess.
Thanks,Lorna

 

[Duane Hookom]

You should be able to Sum() any numeric field in a report's record source. If
you are getting a type mismatch error message, it suggests your field is
text. Is your tspt field displayed left or right aligned in the datasheet
view? It should be right-aligned based on your comments.

If the field is left-aligned, please provide the SQL view of your query.

 

[ldappa]

the tspt field is actually -Tspt: Round([TimeMin]/60,2) and if right
justified.

 

[ldappa]

SELECT timetrk.STUDENTID, timetrk.LOGDATE, inout.LOGDATE, inout.INTIME,
inout.OUTTIME, Round([TimeMin]/60,2) AS Tspt,
IIf(IsNull([intime]),"0",TimeValue([INTIME])) AS intimev,
IIf(IsNull([outtime]),"0",TimeValue([OUTTIME])) AS outtimev,
IIf(IsNull([outtimev]),"?",DateDiff("h",[INTIME],[OUTTIME])) AS TimeHr,
IIf(IsNull([outtimev]),"?",DateDiff("n",[INTIME],[OUTTIME])) AS TimeMin,
DateDiff("s",[INTIMEv],[OUTTIMEv]) AS TimeSEC, timetrk.TIMESPENT,
inout.INTIMEA, inout.OUTTIMEA, DateDiff("h",[INTIMEw],[OUTTIMEw]) AS
TimeHra, IIf(IsNull([INTIMEA]),"0",TimeValue([inTIMEa])) AS intimew,
IIf(IsNull([OUTTIMEA]),"0",TimeValue([OUTTIMEa])) AS outtimew,
inout.INTIMEB, inout.OUTTIMEB, inout.INTIMEC, inout.OUTTIMEC, timetrk.TUTOR,
timetrk.COURSE, timetrk.REASON, timetrk.TUTOR1, timetrk.COURSE1,
timetrk.TIMESPENT1, timetrk.REASON1, INTAKE.LNFN, INTAKE.ENTRYDATE,
INTAKE.ENTRYTIMEI, INTAKE.ENTRYTIMEO
FROM (timetrk LEFT JOIN inout ON (timetrk.LOGDATE = inout.LOGDATE) AND
(timetrk.STUDENTID = inout.STUDENTID)) LEFT JOIN INTAKE ON timetrk.STUDENTID
= INTAKE.STUDENTID;

 

[Duane Hookom]

IMO, your IIf() are wrong. They might return either a number or a string. I
try to avoid this by returning Null rather than the string.

IIf(IsNull([outtimev]),Null,DateDiff("n",[INTIME],[OUTTIME])) AS TimeMin,
This still may cause errors so you might want to filter out all records with
no outtimev or use 0 instead of Null.

Then wrap the IIf() inside Val() to convert the result of your IIf() to a
number.

--
Duane Hookom
Microsoft Access MVP

SELECT timetrk.STUDENTID, timetrk.LOGDATE, inout.LOGDATE, inout.INTIME,
inout.OUTTIME, Round([TimeMin]/60,2) AS Tspt,
IIf(IsNull([intime]),"0",TimeValue([INTIME])) AS intimev,
IIf(IsNull([outtime]),"0",TimeValue([OUTTIME])) AS outtimev,
IIf(IsNull([outtimev]),"?",DateDiff("h",[INTIME],[OUTTIME])) AS TimeHr,
IIf(IsNull([outtimev]),"?",DateDiff("n",[INTIME],[OUTTIME])) AS TimeMin,
DateDiff("s",[INTIMEv],[OUTTIMEv]) AS TimeSEC, timetrk.TIMESPENT,
inout.INTIMEA, inout.OUTTIMEA, DateDiff("h",[INTIMEw],[OUTTIMEw]) AS
TimeHra, IIf(IsNull([INTIMEA]),"0",TimeValue([inTIMEa])) AS intimew,
IIf(IsNull([OUTTIMEA]),"0",TimeValue([OUTTIMEa])) AS outtimew,
inout.INTIMEB, inout.OUTTIMEB, inout.INTIMEC, inout.OUTTIMEC, timetrk.TUTOR,
timetrk.COURSE, timetrk.REASON, timetrk.TUTOR1, timetrk.COURSE1,
timetrk.TIMESPENT1, timetrk.REASON1, INTAKE.LNFN, INTAKE.ENTRYDATE,
INTAKE.ENTRYTIMEI, INTAKE.ENTRYTIMEO
FROM (timetrk LEFT JOIN inout ON (timetrk.LOGDATE = inout.LOGDATE) AND
(timetrk.STUDENTID = inout.STUDENTID)) LEFT JOIN INTAKE ON timetrk.STUDENTID
= INTAKE.STUDENTID;