Subtracting time without date diff

[ahuvas]

Hi I have two fields one which represents the time participants went to sleep, one when they woke up however I dont have any dates - it refers to what they do usually. Is it possible to minus sleeptime from waketime to determine duration of time in bed without dates? Or do I assign dummy dates to use date diff function?

 

[neileg]

Acces stores date/time data as a decimal number where the integer part represents the date and the fraction is the time. So if you have just entered time data, then the date part defaults to 0 (which is 31 December 1899).

So, if your sleep time is 20:00 and your waketime is 07:00 then you would need to add 1 to the waketime to make this the next day and then use DateDiff to find the difference. Trouble is that if sleeptime is 20:00 and waketime is 23:30, then this won't work. Can you make any assumptions about the times?

 

[ahuvas]

There are really three options:

1. THey go to sleep before 12am and wakeup after 12am
2. They go to sleep after 12am and wake up after 12am but before 12pm
3. They go to sleep after 12am and wakeup after 12pm
4. They go to sleep after 12pm and wakeup before 12am if they were a shift worker for example.

Can I test each of the two fields before telling Access how to calculate the time?

 

[neileg]

Three options? Looks like four to me!

I think my logic is correct, but don't do this without testing. If you add 1 to the waketime, then do the DateDiff, then test the result. If the value is greater than 1, subtract 1 and that is the time difference. If it is less than 1, that is the time difference.

 

[ahuvas]

haha I konw it was 4 but it I was too tired to try and work it out