Microsoft Access Help
 
Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
 
 
User Name:
Password:
Remember me
Iron Speed
Go Back   ASP Free Forums > Database > Microsoft Access Help
Reload this Page

Time Calculation

Discuss Time Calculation in the Microsoft Access Help forum on ASP Free.
Time Calculation Microsoft Access forum discussing questions and answers for the Access database. MS Access is used to handle small to medium database needs.

Reply
Add This Thread To:
  Del.icio.us   Digg   Google   Spurl   Blink   Furl   Simpy   Y! MyWeb 
« Previous Thread | Next Thread »  
Thread Tools Search this Thread Rate Thread Display Modes
 
Unread ASP Free Forums Sponsor:
Free Web 2.0 Code Generator! Generate data entry and reporting .NET Web apps in minutes. Quickly create visually stunning, feature-rich apps that are easy to customize and ready to deploy. Download Now!
  #1  
Old May 8th, 2008, 02:12 AM
vepaccess vepaccess is offline
Registered User
ASP Free Newbie (0 - 499 posts)
  
Join Date: May 2008
Posts: 7 vepaccess User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 6 h 2 m 48 sec
Reputation Power: 0
Unhappy Time Calculation
Hi Everyone,

I am importing records via excel from another program. The problem is some
fields has different values then usual. See below please.

Importdate Name Checkin is Checkout is
13.04.2008 Alice 21-23 04:52
13.04.2008 Alex 20:48 04*52
13.04.2008 Max 01:17 10:24
13.04.2008 Micheal 13:35 22:45
13.04.2008 George 18:50 02*30
13.04.2008 Alexandra 22-50 05:00
13.04.2008 Jen 18-55 02:25
14.04.2008 Alice 21-35 05:10
14.04.2008 Alex 20:30 04*55
14.04.2008 Max 01:01 10:05
14.04.2008 Micheal
14.04.2008 George 18:45 02*05
14.04.2008 Alexandra 22-30 04:55
14.04.2008 Jen 18-51 02:22

The Importdates arent always the Checkin or Checkout dates. If the Checkin
time has a " - " sign instead of " : ", that means it is from previous day of
import date. If the Checkout time has a " * " sign instead of " : " , that
means it is from the next day of importdate.

First Question is a query to convert those times as below in "Checkin should be"
and "Checkout should be":

Importdate Name Checkin is Checkout is Checkin should be Checkout should be
13.04.2008 Alice 21-23 04:52 12.04.2008 21:23 13.04.2008 04:52
13.04.2008 Alex 20:48 04*52 13.04.2008 20:48 14.04.2008 04:52
13.04.2008 Max 01:17 10:24 13.04.2008 01:17 13.04.2008 10:24
13.04.2008 Micheal 13:35 22:45 13.04.2008 13:35 13.04.2008 22:45
13.04.2008 George 18:50 02*30 13.04.2008 18:50 14.04.2008 02:30
13.04.2008 Alexandra 22-50 05:00 12.04.2008 22:50 13.04.2008 05:00
13.04.2008 Jen 18-55 02:25 12.04.2008 18:55 13.04.2008 02:25
14.04.2008 Alice 21-35 05:10 13.04.2008 21:35 14.04.2008 05:10
14.04.2008 Alex 20:30 04*55 14.04.2008 20:30 15.04.2008 04:55
14.04.2008 Max 01:01 10:05 14.04.2008 01:01 14.04.2008 10:05
14.04.2008 Micheal
14.04.2008 George 18:45 02*05 14.04.2008 18:45 15.04.2008 02:05
14.04.2008 Alexandra 22-30 04:55 13.04.2008 22:30 14.04.2008 04:55
14.04.2008 Jen 18-51 02:22 13.04.2008 18:51 14.04.2008 02:22
15.04.2008 Micheal 13:30 22:40 15.04.2008 13:30 15.04.2008 22:40

The second question is a query to calculate the difference in Hours between Checkout should be (current record) and Checkin should be (the next record from same person where Checkin exists). As in “Datediff1”. With the “next record” I mean the next or may be previous record from same Name with the first next date of the date of current record Checkin should be.

I just cant make a start. Any help would be greatly appreciated.
Regards,
vepaccess
Reply With Quote
  #2  
Old May 8th, 2008, 10:35 AM
rpeare rpeare is offline
Contributing User
ASP Free Newbie (0 - 499 posts)
  
Join Date: Jan 2008
Posts: 467 rpeare User rank is First Lieutenant (10000 - 20000 Reputation Level)rpeare User rank is First Lieutenant (10000 - 20000 Reputation Level)rpeare User rank is First Lieutenant (10000 - 20000 Reputation Level)rpeare User rank is First Lieutenant (10000 - 20000 Reputation Level)rpeare User rank is First Lieutenant (10000 - 20000 Reputation Level)rpeare User rank is First Lieutenant (10000 - 20000 Reputation Level)rpeare User rank is First Lieutenant (10000 - 20000 Reputation Level)rpeare User rank is First Lieutenant (10000 - 20000 Reputation Level) 
Time spent in forums: 4 Days 7 m 47 sec
Reputation Power: 117
For the purposes of this example I made your data into a table I called Tbl_Test and made the field names ImportDate, PersonName, CheckinIs, CheckoutIs (note, no spaces in the field names, it's not a good practice to have spaces in any of your database object names)

This Query gets you to the first step:

Code:
SELECT Tbl_Test.ImportDate, Tbl_Test.PersonName, Tbl_Test.CheckinIs, Tbl_Test.CheckoutIs, IIf(IsNull([checkinis]),Null,IIf(InStr([checkinis],"-")>0,DateAdd("d",-1,[importdate]),IIf(InStr([checkinis],"*")>0,DateAdd("d",1,[importdate]),[importdate]))) & " " & IIf(IsNull([checkinis]),Null,IIf(InStr([checkinis],"-")>0,replace([checkinis],"-",":"),IIf(InStr([checkinis],"*")>0,replace([checkinis],"*",":"),[checkinis]))) AS CheckinDate, IIf(IsNull([checkoutis]),Null,IIf(InStr([checkoutis],"-")>0,DateAdd("d",-1,[importdate]),IIf(InStr([checkoutis],"*")>0,DateAdd("d",1,[importdate]),[importdate]))) & " " & IIf(IsNull([checkoutis]),Null,IIf(InStr([checkoutis],"-")>0,replace([checkoutis],"-",":"),IIf(InStr([checkoutis],"*")>0,replace([checkoutis],"*",":"),[checkoutis]))) AS CheckoutDate
FROM Tbl_Test;


For the second part of your question I'm a little lost in your description I don't believe you can do this without some code that cycles through your data. I don't think it can be done with a query.

To take your original example (I added another day)

Code:
Importdate Name       Checkinis  Checkoutis
13.04.2008 Alice      21-30      05:00
14.04.2008 Alice      21-35      05:10
15.04.2008 Alice      21-30      05:00


To:

Code:
Rec Importdate Name        Checkinis Checkoutis Checkinshouldbe  Checkoutshouldbe
1   13.04.2008 Alice       21-30     05:00      12.04.2008 21:23 13.04.2008 04:52
2   14.04.2008 Alice       21-35     05:10      13.04.2008 21:35 14.04.2008 05:10
3   15.04.2008 Alice       21-30     05:00      14.04.2008 21:30 15.04.2008 05:00


Now do you want the difference between the CHECKOUTSHOULDBE in record 1 to be compared against the CHECKINSHOULDBE in record 2 and stored on BOTH records or just the first record or do you want to record something like a TIMETOPREV and a TIMETONEXT, being a time between to the previous record and time to the next record in two separate columns? I'm not terribly clear on how what you want for the second part so if you can modify what I have here and show what you want we can move on.
Comments on this post
vepaccess agrees: Excellent, Thanks
__________________
----------------
If we've helped you and you have solved your problem please post that it's been resolved so we know! The suspense kills me!
Reply With Quote
  #3  
Old May 8th, 2008, 02:58 PM
vepaccess vepaccess is offline
Registered User
ASP Free Newbie (0 - 499 posts)
  
Join Date: May 2008
Posts: 7 vepaccess User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 6 h 2 m 48 sec
Reputation Power: 0
Great, i dont know what to say. Thank you very much for the first query, i have been trying too long to figure out something to do that. Thanks again.

Sorry, Second query needs to give me the TIMETONEXT. Difference between "Checkoutshouldbe" current record and "Checkinshouldbe" next record from the same person. As in example : just one column. difference record 1-2, difference record 2-3, difference record 3-4,...

Been a long time since i wrote in English, sorry.

By the way for the first query you made, i have to give something in return as a little appreciation of your work. Can i donate something somewhere for you? ( It may not cover the work you did but it will make me feel much better, not everybody i asked helped me out in this case). Just let me know by email please.


Quote:
Originally Posted by rpeare
Now do you want the difference between the CHECKOUTSHOULDBE in record 1 to be compared against the CHECKINSHOULDBE in record 2 and stored on BOTH records or just the first record or do you want to record something like a TIMETOPREV and a TIMETONEXT, being a time between to the previous record and time to the next record in two separate columns? I'm not terribly clear on how what you want for the second part so if you can modify what I have here and show what you want we can move on.
Reply With Quote
  #4  
Old May 8th, 2008, 04:00 PM
rpeare rpeare is offline
Contributing User
ASP Free Newbie (0 - 499 posts)
  
Join Date: Jan 2008
Posts: 467 rpeare User rank is First Lieutenant (10000 - 20000 Reputation Level)rpeare User rank is First Lieutenant (10000 - 20000 Reputation Level)rpeare User rank is First Lieutenant (10000 - 20000 Reputation Level)rpeare User rank is First Lieutenant (10000 - 20000 Reputation Level)rpeare User rank is First Lieutenant (10000 - 20000 Reputation Level)rpeare User rank is First Lieutenant (10000 - 20000 Reputation Level)rpeare User rank is First Lieutenant (10000 - 20000 Reputation Level)rpeare User rank is First Lieutenant (10000 - 20000 Reputation Level) 
Time spent in forums: 4 Days 7 m 47 sec
Reputation Power: 117
I don't come here for compensation I come here to learn and to help other folks I'll try to work on the other part and give you an example.
Reply With Quote
  #5  
Old May 8th, 2008, 04:32 PM
sbenj69's Avatar
sbenj69 sbenj69 is offline
Dark Sonic Apprentice :D
Click here for more information. Click here for more information
 
Join Date: Feb 2007
Location: Redneck part of Illinois
Posts: 1,296 sbenj69 User rank is General (90000 - 100000 Reputation Level)sbenj69 User rank is General (90000 - 100000 Reputation Level)sbenj69 User rank is General (90000 - 100000 Reputation Level)sbenj69 User rank is General (90000 - 100000 Reputation Level)sbenj69 User rank is General (90000 - 100000 Reputation Level)sbenj69 User rank is General (90000 - 100000 Reputation Level)sbenj69 User rank is General (90000 - 100000 Reputation Level)sbenj69 User rank is General (90000 - 100000 Reputation Level)sbenj69 User rank is General (90000 - 100000 Reputation Level)sbenj69 User rank is General (90000 - 100000 Reputation Level)sbenj69 User rank is General (90000 - 100000 Reputation Level)sbenj69 User rank is General (90000 - 100000 Reputation Level)sbenj69 User rank is General (90000 - 100000 Reputation Level)sbenj69 User rank is General (90000 - 100000 Reputation Level)sbenj69 User rank is General (90000 - 100000 Reputation Level)sbenj69 User rank is General (90000 - 100000 Reputation Level)  Folding Points: 137454 Folding Title: Super Ultimate Folder - Level 1Folding Points: 137454 Folding Title: Super Ultimate Folder - Level 1Folding Points: 137454 Folding Title: Super Ultimate Folder - Level 1Folding Points: 137454 Folding Title: Super Ultimate Folder - Level 1Folding Points: 137454 Folding Title: Super Ultimate Folder - Level 1Folding Points: 137454 Folding Title: Super Ultimate Folder - Level 1
Time spent in forums: 2 Weeks 3 Days 1 h 46 m 58 sec
Reputation Power: 984
that, and we were all where you are at some point in our programming lives
__________________
Did I help you? If so gimme rep by clicking on the at the top right corner of this post

Madness does not always howl. Sometimes, it is the quiet voice at the end of the day saying, "Hey, is there room in your head for one more?"
Reply With Quote
  #6  
Old May 8th, 2008, 05:22 PM
rpeare rpeare is offline
Contributing User
ASP Free Newbie (0 - 499 posts)
  
Join Date: Jan 2008
Posts: 467 rpeare User rank is First Lieutenant (10000 - 20000 Reputation Level)rpeare User rank is First Lieutenant (10000 - 20000 Reputation Level)rpeare User rank is First Lieutenant (10000 - 20000 Reputation Level)rpeare User rank is First Lieutenant (10000 - 20000 Reputation Level)rpeare User rank is First Lieutenant (10000 - 20000 Reputation Level)rpeare User rank is First Lieutenant (10000 - 20000 Reputation Level)rpeare User rank is First Lieutenant (10000 - 20000 Reputation Level)rpeare User rank is First Lieutenant (10000 - 20000 Reputation Level) 
Time spent in forums: 4 Days 7 m 47 sec
Reputation Power: 117
I do have another question though, what happens to portions of an hour let's say the expected gap is 9 hours 23 minutes what do you want to do with the 23 minutes? round up or round down or do you want the minutes included? what if the expected gap is more than 24 hours how do you want days, weeks or months displayed?
Reply With Quote
  #7  
Old May 8th, 2008, 05:37 PM
vepaccess vepaccess is offline
Registered User
ASP Free Newbie (0 - 499 posts)
  
Join Date: May 2008
Posts: 7 vepaccess User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 6 h 2 m 48 sec
Reputation Power: 0
actually only the ones less than exact 11 hours in hours and minutes are enough to display. all others as "20" or somethingelse bigger than 11. would it be easier then?

Quote:
Originally Posted by rpeare
I do have another question though, what happens to portions of an hour let's say the expected gap is 9 hours 23 minutes what do you want to do with the 23 minutes? round up or round down or do you want the minutes included? what if the expected gap is more than 24 hours how do you want days, weeks or months displayed?
Reply With Quote
  #8  
Old May 8th, 2008, 06:13 PM
vepaccess vepaccess is offline
Registered User
ASP Free Newbie (0 - 499 posts)
  
Join Date: May 2008
Posts: 7 vepaccess User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 6 h 2 m 48 sec
Reputation Power: 0
Thanks, that is nice to hear. Lately I have been stressed a lot with this problem. Finally I have found this forum today with people who likes challenges like myself. And just a few hours later the solution to my problem. It is more than amazing. Thank you all very much. I will also try to help out around here as long as i can, if i can, since I am very often on the net.
Quote:
Originally Posted by sbenj69
that, and we were all where you are at some point in our programming lives
Reply With Quote
  #9  
Old May 8th, 2008, 08:08 PM
rpeare rpeare is offline
Contributing User
ASP Free Newbie (0 - 499 posts)
  
Join Date: Jan 2008
Posts: 467 rpeare User rank is First Lieutenant (10000 - 20000 Reputation Level)rpeare User rank is First Lieutenant (10000 - 20000 Reputation Level)rpeare User rank is First Lieutenant (10000 - 20000 Reputation Level)rpeare User rank is First Lieutenant (10000 - 20000 Reputation Level)rpeare User rank is First Lieutenant (10000 - 20000 Reputation Level)rpeare User rank is First Lieutenant (10000 - 20000 Reputation Level)rpeare User rank is First Lieutenant (10000 - 20000 Reputation Level)rpeare User rank is First Lieutenant (10000 - 20000 Reputation Level) 
Time spent in forums: 4 Days 7 m 47 sec
Reputation Power: 117
Ok, going with your original example use this query:

Code:
SELECT Tbl_Test.ID, Tbl_Test.PersonName, Tbl_Test.ImportDate, Tbl_Test.CheckinIs, Tbl_Test.CheckoutIs, IIf(IsNull([checkoutis]),Null,IIf(InStr([checkoutis],"-")>0,DateAdd("d",-1,[importdate]),IIf(InStr([checkoutis],"*")>0,DateAdd("d",1,[importdate]),[importdate]))) & " " & IIf(IsNull([checkoutis]),Null,IIf(InStr([checkoutis],"-")>0,replace([checkoutis],"-",":"),IIf(InStr([checkoutis],"*")>0,replace([checkoutis],"*",":"),[checkoutis]))) AS CheckoutDate, DMin("[ID]","Tbl_Test","[PersonName] = '" & [personname] & "' and [ImportDate] > #" & [importdate] & "#") AS NextID, IIf(IsNull(DMin("[ID]","Tbl_Test","[PersonName] = '" & [personname] & "' and [ImportDate] > #" & [importdate] & "#")),Null,DLookUp("[importdate]","Tbl_Test","[ID] = " & DMin("[ID]","Tbl_Test","[PersonName] = '" & [personname] & "' and [ImportDate] > #" & [importdate] & "#") & " ")) AS NextImportDate, IIf(IsNull(DMin("[ID]","Tbl_Test","[PersonName] = '" & [personname] & "' and [ImportDate] > #" & [importdate] & "#")),Null,DLookUp("[checkinis]","Tbl_Test","[ID] = " & DMin("[ID]","Tbl_Test","[PersonName] = '" & [personname] & "' and [ImportDate] > #" & [importdate] & "#") & " ")) AS NextImportTime, Format(IIf(IsNull([checkinis]),Null,IIf(InStr([checkinis],"-")>0,DateAdd("d",-1,[importdate]),IIf(InStr([checkinis],"*")>0,DateAdd("d",1,[importdate]),[importdate]))) & " " & IIf(IsNull([checkinis]),Null,IIf(InStr([checkinis],"-")>0,replace([checkinis],"-",":"),IIf(InStr([checkinis],"*")>0,replace([checkinis],"*",":"),[checkinis]))) & ":00","General Date") AS CheckinDate, Format(IIf(IsNull([nextimporttime]),Null,IIf(InStr([nextimporttime],"-")>0,DateAdd("d",-1,[nextimportdate]),IIf(InStr([nextimporttime],"*")>0,DateAdd("d",1,[nextimportdate]),[nextimportdate]))) & " " & IIf(IsNull([nextimporttime]),Null,IIf(InStr([nextimporttime],"-")>0,replace([nextimporttime],"-",":") & ":00",IIf(InStr([nextimporttime],"*")>0,replace([nextimporttime],"*",":") & ":00",[nextimporttime] & ":00"))),"General Date") AS NextDateTime
FROM Tbl_Test
WHERE (((Tbl_Test.CheckinIs) Is Not Null))
ORDER BY Tbl_Test.PersonName, Tbl_Test.ImportDate, Tbl_Test.CheckinIs;


save it as Query_Pre

then make this:

Code:
SELECT Query_Pre.ID, Query_Pre.PersonName, Query_Pre.ImportDate, Query_Pre.CheckinIs, Query_Pre.CheckoutIs, Query_Pre.CheckoutDate, Query_Pre.NextID, Query_Pre.NextImportDate, Query_Pre.NextImportTime, Query_Pre.CheckinDate, Query_Pre.NextDateTime, IIf(Len(Trim([nextdatetime]))=0,Null,DateDiff("s",[checkindate],[nextdatetime])) AS Seconds, Int([seconds]/3600) AS Hours, ([seconds] Mod 3600)/60 AS Mins
FROM Query_Pre;


I didn't know what the final format you wanted was but I gave a few calculated examples, total seconds, an hours and minutes calculation.
Comments on this post
sbenj69 agrees!
vepaccess agrees: helped a lot, thanks
Reply With Quote
  #10  
Old May 9th, 2008, 03:04 AM
vepaccess vepaccess is offline
Registered User
ASP Free Newbie (0 - 499 posts)
  
Join Date: May 2008
Posts: 7 vepaccess User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 6 h 2 m 48 sec
Reputation Power: 0
codes seem to work but getting "#Error" as results ( at both queries).

Syntaxerror in Date '[PersonName] = 'Alice' and [ImportDate] > #16.04.2008#'

I am trying to do it with a subquery now.

Quote:
Originally Posted by rpeare
Ok, going with your original example use this query:

Code:
SELECT Tbl_Test.ID, Tbl_Test.PersonName, Tbl_Test.ImportDate, Tbl_Test.CheckinIs, Tbl_Test.CheckoutIs, IIf(IsNull([checkoutis]),Null,IIf(InStr([checkoutis],"-")>0,DateAdd("d",-1,[importdate]),IIf(InStr([checkoutis],"*")>0,DateAdd("d",1,[importdate]),[importdate]))) & " " & IIf(IsNull([checkoutis]),Null,IIf(InStr([checkoutis],"-")>0,replace([checkoutis],"-",":"),IIf(InStr([checkoutis],"*")>0,replace([checkoutis],"*",":"),[checkoutis]))) AS CheckoutDate, DMin("[ID]","Tbl_Test","[PersonName] = '" & [personname] & "' and [ImportDate] > #" & [importdate] & "#") AS NextID, IIf(IsNull(DMin("[ID]","Tbl_Test","[PersonName] = '" & [personname] & "' and [ImportDate] > #" & [importdate] & "#")),Null,DLookUp("[importdate]","Tbl_Test","[ID] = " & DMin("[ID]","Tbl_Test","[PersonName] = '" & [personname] & "' and [ImportDate] > #" & [importdate] & "#") & " ")) AS NextImportDate, IIf(IsNull(DMin("[ID]","Tbl_Test","[PersonName] = '" & [personname] & "' and [ImportDate] > #" & [importdate] & "#")),Null,DLookUp("[checkinis]","Tbl_Test","[ID] = " & DMin("[ID]","Tbl_Test","[PersonName] = '" & [personname] & "' and [ImportDate] > #" & [importdate] & "#") & " ")) AS NextImportTime, Format(IIf(IsNull([checkinis]),Null,IIf(InStr([checkinis],"-")>0,DateAdd("d",-1,[importdate]),IIf(InStr([checkinis],"*")>0,DateAdd("d",1,[importdate]),[importdate]))) & " " & IIf(IsNull([checkinis]),Null,IIf(InStr([checkinis],"-")>0,replace([checkinis],"-",":"),IIf(InStr([checkinis],"*")>0,replace([checkinis],"*",":"),[checkinis]))) & ":00","General Date") AS CheckinDate, Format(IIf(IsNull([nextimporttime]),Null,IIf(InStr([nextimporttime],"-")>0,DateAdd("d",-1,[nextimportdate]),IIf(InStr([nextimporttime],"*")>0,DateAdd("d",1,[nextimportdate]),[nextimportdate]))) & " " & IIf(IsNull([nextimporttime]),Null,IIf(InStr([nextimporttime],"-")>0,replace([nextimporttime],"-",":") & ":00",IIf(InStr([nextimporttime],"*")>0,replace([nextimporttime],"*",":") & ":00",[nextimporttime] & ":00"))),"General Date") AS NextDateTime
FROM Tbl_Test
WHERE (((Tbl_Test.CheckinIs) Is Not Null))
ORDER BY Tbl_Test.PersonName, Tbl_Test.ImportDate, Tbl_Test.CheckinIs;


save it as Query_Pre

then make this:

Code:
SELECT Query_Pre.ID, Query_Pre.PersonName, Query_Pre.ImportDate, Query_Pre.CheckinIs, Query_Pre.CheckoutIs, Query_Pre.CheckoutDate, Query_Pre.NextID, Query_Pre.NextImportDate, Query_Pre.NextImportTime, Query_Pre.CheckinDate, Query_Pre.NextDateTime, IIf(Len(Trim([nextdatetime]))=0,Null,DateDiff("s",[checkindate],[nextdatetime])) AS Seconds, Int([seconds]/3600) AS Hours, ([seconds] Mod 3600)/60 AS Mins
FROM Query_Pre;


I didn't know what the final format you wanted was but I gave a few calculated examples, total seconds, an hours and minutes calculation.
Reply With Quote
  #11  
Old May 9th, 2008, 07:30 AM
vepaccess vepaccess is offline
Registered User
ASP Free Newbie (0 - 499 posts)
  
Join Date: May 2008
Posts: 7 vepaccess User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 6 h 2 m 48 sec
Reputation Power: 0
I guess I could make it with a subquery. It wont work for all records where datediff Importdates other than 1 is but the Results are correct and enough for me for now. can you check the code if they are also ok.

the first query you made and this one with some of codes from the last query you made has done the job :

SELECT qry_qry1.ID, qry_qry1.ImportDate, qry_qry1.PersonName, qry_qry1.CheckinIs, qry_qry1.CheckoutIS, qry_qry1.CheckinDate, qry_qry1.CheckoutDate, (SELECT MAX(q2.CheckoutDate)
FROM qry_qry1 AS q2
WHERE qry_qry1.ImportDate = q2.ImportDate + "1"
AND qry_qry1.PersonName = q2.PersonName
AND qry_qry1.ID > q2.ID) AS DupeCheckin, IIf(Len(Trim([DupeCheckin]))=0,Null,DateDiff("s",[DupeCheckin],[Checkindate])) AS Seconds, Int([seconds]/3600) AS Hours, ([seconds] Mod 3600)/60 AS Mins
FROM qry_qry1
WHERE (((qry_qry1.CheckinIs)=IIf([CheckinIs]>"0",[CheckinIs],"0")))
ORDER BY qry_qry1.PersonName, qry_qry1.ID, qry_qry1.ImportDate DESC;

Many Thanks !!!

Quote:
Originally Posted by vepaccess
codes seem to work but getting "#Error" as results ( at both queries).

Syntaxerror in Date '[PersonName] = 'Alice' and [ImportDate] > #16.04.2008#'

I am trying to do it with a subquery now.
Reply With Quote
Reply

Viewing: ASP Free Forums > Database > Microsoft Access Help > Time Calculation

« Previous Thread | Next Thread »

Thread Tools  Search this Thread 
Search this Thread:

Advanced Search
Display Modes  Rate This Thread 
Linear Mode Linear Mode
Rate This Thread:


Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox
Forum Jump