Hi,
I have this problem. I 've created this table:

id time_in time_out day Between_hou
4444 17:00 20:00 20/06/2008
4444 13:04 14:07 22/06/2008 41 hours
4444 16:00 17:06 23/06/2008 26 hours
... ... ... ... ...
... ... ... ... ...
... ... ... ... ...

I cant find a way to calculate the hours in red(see above , [Between_hou] ). This hours is the substraction between the time_ine of the next day and the time_out of day before!

Any ideas???

Thnxs in advance!

To do what you are after, I would first set up a query that sorts the data in order by ID and then by the date field (desc. order). I would then create another query and use 2 Dlookup() functions to pull the last time out and the last date out using the first query. I would then bring the date and time for both the current date and time_in into 1 field and do the same for the last date/time out. From there you can use the datediff() function to do the calculation you need. I have attached an example database that shows the two queries.

To do what you are after, I would first set up a query that sorts the data in order by ID and then by the date field (desc. order). I would then create another query and use 2 Dlookup() functions to pull the last time out and the last date out using the first query. I would then bring the date and time for both the current date and time_in into 1 field and do the same for the last date/time out. From there you can use the datediff() function to do the calculation you need. I have attached an example database that shows the two queries.
thanks very much.. you're the best..
next time you will be my first choice for sure

You're welcome. Good luck with your project.

Hey, i 've tried what you told me but it came up a weird problem which i am on it since yesterday and i cant solve it out. Sometimes the function just dont work, i noticed it happens, usually, when the month is changing. I uploaded a screen shot of that!!

I could not duplicate your problem. I noticed that you are using a dd/mm/yyyy format for the date while I am using a mm/dd/yyyy format. Are you applying the format or is it setting of your computer?

The default format on my pc is dd/mm/YYYY(europe xaxa) ! What can i do to solve this?

I also notice that the function dlookup() just compares two dates (mydate<#"&tbldatetimes.mydate &"#) so if the format is dd/mm/yyy or mm/dd/yyyy its not going to be a problem, isnt it?

If you format the date at the table level it might be an issue, but since it set at the computer level, it should not be an issue. It might be the DLookup causing the problem; let me try a couple of things.

Attached is another version of the database I posted yesterday. I opted to use two custom functions to return the previous date and time instead of the DLookup functions. Could you please try this and see if it takes care of the problem?

Attached is another version of the database I posted yesterday. I opted to use two custom functions to return the previous date and time instead of the DLookup functions. Could you please try this and see if it takes care of the problem?
thanks my friend i will check out and i will post you back

the same problem again.. the problem is the dates which the number of day is below 10
for example 2/7/2009.. dates between 1/7/2009 and 9/7/2009(for example) don't work..

Is the data type of your date field actually set up as date/time or is it text?

Is the data type of your date field actually set up as date/time or is it text?
yes it's set as short date

This is an example of the project you send me with the errors!

I'm at a loss of why it isn't working correctly. When you opened up my most recent database, does it automatically show the dates in the dd/mm/yyyy format? Did you run qryFinalSummary2 (that's the one that uses the custom functions)

Would it be possible for you to zip and post your database (with any sensitive data removed)?

Can i upload 34 mb ??

I uploaded the database! First of all i want to thank you again for your time, i really appreciated!!

I took the frist code you sended but i change some names :

time_out=timeOUT
mydate=day
tblDatetimes=hours

In my database i have a lot of queries but for this problem we only need the two queries with the name qryfinalSumary, qrySortedData(the same names that you gave) and the table hours(in your example you name it tblDatetimes).
I also put some "test" data and when you run the qryfinalSummary you will see the problem!!

Thank you again for this!!

i know this doesnt fix the issue

but the real reason this is always a problem is that access doesnt have a next/previous item

the solution is not to need a next/previous item which means storing on each row all the data needed to compute your answer

so if it is feasible, at the time you store the data the first time, do a lookup to find the previous vlaue, and store that in the saved record. You only have ot do the lookup once, and it saves you pain in the future.

This only becomes an issue if at some point you need to insert data in between rows - as this would affect both of the surrounding rows, and may be more complex.

Thnxs for answering, but how is that possible?

Your database worked perfectly, so that tells me that it has something to do with the date format. I'll have to do some more research to see if I can find the reason and hopefully a fix.

You might want to take a look at these two links. It sounds like you might have to specify the format to get this to work on your end.

Link 1 (http://www.utteraccess.com/forums/showflat.php?Date-Format-Disrupting-Pass-Through-Queries-&Number=1850993)

Link 2 (http://www.microsoft.com/communities/newsgroups/en-us/default.aspx?dg=microsoft.public.access.queries&tid=811bf167-833a-4f1b-8ee1-bb928c6deeda&cat=en_US_d02fc761-3f6b-402c-82f6-ba1a8875c1a7&lang=en&cr=&sloc=en-us&m=1&p=1)

Hey i 've read the links and i try something like that:

LastTimeOut: DLookUp("time_out";"qrySortedData";"format(mydate,'dd/mm/yyyy')<#" & format(tblDateTimes.mydate,'dd/mm/yyyy') & "# and ID=" & tblDateTimes.ID)

Which dont work returns syntax error, the code below(without formatting tblDatetimes.mydate) has no syntax errors.

LastTimeOut: DLookUp("time_out";"qrySortedData";"format(mydate,'dd/mm/yyyy')<#" & tblDateTimes.mydate & "# and ID=" & tblDateTimes.ID)


????

The code works but still the problem exists:

LastTimeOut: DLookUp("time_out";"qrySortedData";"format(mydate,'dd/mm/yyyy')<format([tblDateTimes].[mydate],'dd/mm/yyyy') AND ID=" & [tblDateTimes].[ID])

I am out of ideas!!

I think this link (http://www.utteraccess.com/forums/showflat.php?Cat=&Board=87&Number=1838181&Zf=&Zw=dd%2Fmm%2Fyyyy&Zg=0&Zl=a&Main=1838181&Search=true&where=&Zu=&Zd=j&Zn=&Zt=84&Zs=a&Zy=#Post1838181&Zp=) explains it pretty well, but I just cannot test it from my end.

I cant do it !! I ll do it manually i ll add 2 columns where i ll write the previous dates!
I thank you again for the time you spend for me!!

In one of the links I provided, there was a simple function used to modify dates. I incorporated that function with some modification into the attached database. I used that function along with the CDate() function in the queries and custom functions I created. I also change the mydate field in the table to a medium date to remove ambiguity. Could you please try the query (qryFinalSummary2) in the attached to see if it works on your end.

Xaxa i think you make it personal ee? Sorry but still dont work!


P.S: I have never mentioned that i used access 2007.
P.S: I also tried the date modification but nothing happens.

How is it not working? Same as before? Can you provide a screen shot?

The months is in greek. You can see below the translation:

Ιουν = June
Σεπ = September
Οκτ = October
Νοε = November
Αυγ = August

I'm at a loss to explain the Greek letters! However, I did have on last hope. I had not converted all of the occurences of the date field in the code and queries. So, the attached DB has those changes. If it doesn't work correctly (whether in Greek or not)--I give up.