report

[royaloba]

hello guys,
how can i sum the total time in report footer? when i use function sum
i got this following error data type mismatch in function object function1... is there any on how to do it.?:icon_sad:
thanks. regards

[QVeen72]

Hi,

What report u r using? CR or Data Report or Excel report..?

Regards
Veena

[royaloba]

hello veena,
thanks for your quick reponse, i'm using data report. im trying to group the data, my only problem is how to average the time.. im working my time and attendance and im on the final stage of my program.

sql = "Select * from tbltimecard Where Date>='" & Format(dtFrom.Value, "YYYY-MM-DD") & "' And Date<='" & Format(dtTo.Value, "YYYY-MM-DD") & "' And Late>'" & "00:00:00" & "'" & "Order By LastName, Date"
        With cmd
            .ActiveConnection = conn
            .CommandType = adCmdText
            .CommandText = " SHAPE {" & sql & "}  AS cmdGroup Compute cmdGroup BY 'LastName'"
            .Execute
        End With
        With rs_timecard
            .ActiveConnection = conn
            .CursorLocation = adUseClient
            .Open cmd
        End With
        sumLate = CDate(sumLate) + CDate(rs_timecard!Late)
        MsgBox sumLate
        With rptLate 'this for group header
            Set .DataSource = rs_timecard
            .DataMember = ""
            With .Sections("section2").Controls
                .Item("txtName").DataField = "LastName"
                .Item("lblFrom").Caption = Format(dtFrom.Value, "MMM-DD-YYYY")
                .Item("lblTo").Caption = Format(dtTo.Value, "MMM-DD-YYYY")
                .Item("lblCompany").Caption = rs_company!CompanyName
            End With
            With .Sections("Section1").Controls 'this for group details
                .Item("Text1").DataMember = "cmdGroup"
                .Item("Text1").DataField = "Day"
                .Item("Text2").DataMember = "cmdGroup"
                .Item("Text2").DataField = Format("Date", "MMM-DD-YYYY")
                .Item("Text3").DataMember = "cmdGroup"
                .Item("Text3").DataField = "Schedule"
                .Item("Text4").DataMember = "cmdGroup"
                .Item("Text4").DataField = Format("TimeIn", "hh:mm" & " " & "AM/PM")
                .Item("Text5").DataMember = "cmdGroup"
                .Item("Text5").DataField = Format("TimeOut", "hh:mm" & " " & "AM/PM")
                .Item("Text6").DataMember = "cmdGroup"
                .Item("Text6").DataField = Format("Late", "HH:mm")
            End With
            .Refresh
            .Show 1
        End With

[jireh]

An error occur when you try to sum a data type that is not a number. Try to look the data type of your datafield for the the time. when it is in text then you'll get that error data type mismatch.

[royaloba]

hello jireh
i already change the dataformat of that field to time, but still same error occurs

[QVeen72]

Hi,

Use This Query To get Total Sum of Time:

Select Format(Sum(TimeField),'hh:mm') From MyTable

This will give in Hrs:Mins Format

REgards
Veena

[jireh]

Ok Correct me if I a wrong, as what I see in your code s tat you have to get the number of hours first by getting the total time consumed in (timeout - timein), right? Once you get the total number of hours of course it must be a number or integer and not an Hour, right again? therefore you can add now the total number of hours.
because actually we can not aggregate a function that is not a number or integer, that's why you get an error datatype mistmatch, just let me know if there still a problem,
the datatype for that total number of hours cosumed must be an Integer or number.

regards:

[royaloba]

Hello Venna,
i'm using mysql, Select Format(sum(Late,'hh:mm') From tbltimecard
error in 'hh:mm'
Regards

[QVeen72]

Hi,

Sorry that was for access.

Try this from MySQL:

Select SEC_TO_TIME(SUM(TIME_TO_SEC(TimeField))) From MyTable

Regards
Veena