How to get a Proper worktime and overtime calculation?
Using Access 2003 Database

Table1

    Intime Outtime WorkTime OverTime
    
    08:00:00 19:00:00 09:00:00 02:00:00
    09:00:00 18:23:23 09:00:00 00:23:23
    15:00:00    -----     00:00:00 00:00:00
    09:00:00 18:00:00 09:00:00 00:00:00

Intime, Outtime, Work time, overtime data type is text

    From 09:00:00 to 18:00:00 - It will come in Work time
    Before 09:00:00, after 18:00:00 – It will come in overtime

My query.

    SELECT PERSONID, CARDEVENTDATE, INTIME, OUTTIME, (IIF(OUTTIME='No Punchout', '00:00:00', WorkTime)) AS WorkedTime, (IIF(OUTTIME='No Punchout', '00:00:00', OverTime)) as OverdutyTime, Latecome, (IIF(OUTTIME='No Punchout', '', EarlyLeave)) as EarlyLeave From (SELECT PERSONID, CARDEVENTDATE, INTIME, (IIF(OUTTIME=INTIME, 'No PunchOut', OUTTIME)) AS OUTTIME, (Format(IIf(CLng(OutTime) > 180000, CDate('18:00:00'),  CDate(Format(OutTime, '00:00:00'))) - IIf(CLng(InTime) < 90000,  CDate('09:00:00'), CDate(Format(InTime, '00:00:00'))), 'hh:nn:ss'))  As WorkTime,  (Format(IIf(CLng(InTime) < 90000, CDate('09:00:00') -  CDate(Format(InTime, '00:00:00')), 0) + IIf(CLng(OutTime) > 180000,  CDate(Format(OutTime, '00:00:00')) - CDate('18:00:00'), 0), 'hh:nn:ss')) As OverTime,  (Format(IIf(CLng(Intime) > 090000,   CDate(Format(Intime, '00:00:00'))) - IIf(90000,  CDate('09:00:00'), CDate(Format(InTime, '00:00:00'))), 'hh:nn:ss'))  As LateCome, (Format( IIf(180000,  CDate('18:00:00'), CDate(Format(outtime, '00:00:00'))) - IIf(CLng(Outtime) < 180000, CDate(Format(Outtime, '00:00:00'))), 'hh:nn:ss'))  As EarlyLeave 
     (SELECT T_PERSON.PERSONID, T_CARDEVENT.CARDEVENTDATE, MIN(T_CARDEVENT.CARDEVENTTIME) AS INTIME, MAX(T_CARDEVENT.CARDEVENTTIME) AS OUTTIME FROM T_PERSON INNER JOIN T_CARDEVENT ON T_PERSON.PERSONID = T_CARDEVENT.PERSONID GROUP BY T_PERSON.PERSONID, T_CARDEVENT.CARDEVENTDATE))


Above query is Correctly Working in my system.  

But when I use the query in client system, it showing like this…

Table1

    Intime Outtime WorkTime OverTime
    
    08:00:00 19:00:00 09:00:00 AM 02:00:00 AM
    09:00:00 18:23:23 09:00:00 AM 12:23:23 AM
    15:00:00    -----     12:00:00 AM 00:00:00 AM
    09:00:00 18:00:00 09:00:00 AM 12:00:00 AM

    It is displaying 12:00:00 AM Instead of 00:00:00 and also displaying 12:23:23 instead of 00:00:00. 

 

    If it is 00 then it is displaying 12:00:00 AM.


Clients Using Access 2007 Database. There is not Access 2003 or Access 2007 Problem. 
Why worktime and Overtime is displaying like 12:00:00 AM.  I copied the client database and check the database in my System. Work Time and Over Time is correctly displaying.


Please can any one help to solve my problem.

Plzzzz….


How to get a Proper worktime and overtime calculation?
===

Using Access 2003 Database

Table1

    Intime Outtime WorkTime OverTime
    
    08:00:00 19:00:00 09:00:00 02:00:00
    09:00:00 18:23:23 09:00:00 00:23:23
    15:00:00    -----     00:00:00 00:00:00
    09:00:00 18:00:00 09:00:00 00:00:00

Intime, Outtime, Work time, overtime data type is text

    From 09:00:00 to 18:00:00 - It will come in Work time
    Before 09:00:00, after 18:00:00 – It will come in overtime

My query.

    SELECT PERSONID, CARDEVENTDATE, INTIME, OUTTIME, (IIF(OUTTIME='No Punchout', '00:00:00', WorkTime)) AS WorkedTime, (IIF(OUTTIME='No Punchout', '00:00:00', OverTime)) as OverdutyTime, Latecome, (IIF(OUTTIME='No Punchout', '', EarlyLeave)) as EarlyLeave From (SELECT PERSONID, CARDEVENTDATE, INTIME, (IIF(OUTTIME=INTIME, 'No PunchOut', OUTTIME)) AS OUTTIME, (Format(IIf(CLng(OutTime) > 180000, CDate('18:00:00'),  CDate(Format(OutTime, '00:00:00'))) - IIf(CLng(InTime) < 90000,  CDate('09:00:00'), CDate(Format(InTime, '00:00:00'))), 'hh:nn:ss'))  As WorkTime,  (Format(IIf(CLng(InTime) < 90000, CDate('09:00:00') -  CDate(Format(InTime, '00:00:00')), 0) + IIf(CLng(OutTime) > 180000,  CDate(Format(OutTime, '00:00:00')) - CDate('18:00:00'), 0), 'hh:nn:ss')) As OverTime,  (Format(IIf(CLng(Intime) > 090000,   CDate(Format(Intime, '00:00:00'))) - IIf(90000,  CDate('09:00:00'), CDate(Format(InTime, '00:00:00'))), 'hh:nn:ss'))  As LateCome, (Format( IIf(180000,  CDate('18:00:00'), CDate(Format(outtime, '00:00:00'))) - IIf(CLng(Outtime) < 180000, CDate(Format(Outtime, '00:00:00'))), 'hh:nn:ss'))  As EarlyLeave 
     (SELECT T_PERSON.PERSONID, T_CARDEVENT.CARDEVENTDATE, MIN(T_CARDEVENT.CARDEVENTTIME) AS INTIME, MAX(T_CARDEVENT.CARDEVENTTIME) AS OUTTIME FROM T_PERSON INNER JOIN T_CARDEVENT ON T_PERSON.PERSONID = T_CARDEVENT.PERSONID GROUP BY T_PERSON.PERSONID, T_CARDEVENT.CARDEVENTDATE))


Above query is Correctly Working in my system.  

But when I use the query in client system, it showing like this…

Table1

    Intime Outtime WorkTime OverTime
    
    08:00:00 19:00:00 09:00:00 AM 02:00:00 AM
    09:00:00 18:23:23 09:00:00 AM 12:23:23 AM
    15:00:00    -----     12:00:00 AM 00:00:00 AM
    09:00:00 18:00:00 09:00:00 AM 12:00:00 AM

    It is displaying 12:00:00 AM Instead of 00:00:00 and also displaying 12:23:23 instead of 00:00:00. 

 

    If it is 00 then it is displaying 12:00:00 AM.


Clients Using Access 2007 Database. There is not Access 2003 or Access 2007 Problem. 
Why worktime and Overtime is displaying like 12:00:00 AM.  I copied the client database and check the database in my System. Work Time and Over Time is correctly displaying.


Please can any one help to solve my problem.

Plzzzz….

