Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2004

    Unanswered: help with Time calculations within access.

    Ive recently been working on a system by which a number of time calculations need to be carried out..

    it monitors people entering a room, and leaving .. simple enough. all people who could possibly enter are given a unique number, lets say 1.

    if someone comes into the room at 10am and leaves at 11am using the calculation which i created, as a standard [time out]-[time in] calculation it records their time as an hour (baring in mind ive multiplied by 60 and 24 thus converting to minutes etc) -- person 1 has been in an hour that day.

    the problem comes if the same person enters the room later that day, say 1pm and leaves at 2pm.. because i have created a calculation which is very simple it begins tryin to calculate beetween 10am and 11am, 10am and 2pm, 1pm and 11am and so on, thus creating negative values.. and generally incorrect total time spent by that person, i.e... 2 hours.

    if anyone has any suggestions they would be greatly appreciated.. ideally an easy solution which can be created by a simple calculation.. although i think it may mean that i will have to give each visit a unique identifier thus rethinking the whole thing.

    below is a brief example of what happens, in this case the upper and lower entries are the only ones i want, i.e. in at 11.52 leave at 12.52, in at 19.06 leave at 20.06 .. 2 hours in total.

    Card Number TIME IN TIME OUT time spent
    1 11:52 12:52 60.1500000001397
    1 19:06 12:52 -373.783333338797
    1 11:52 20:06 494.033333337866
    1 19:06 20:06 60.099999998929

    any help would be greatly appreciated, richy.
    Last edited by richyinnewcastl; 08-04-04 at 15:27.

  2. #2
    Join Date
    Feb 2004
    you will also need to add fields for Date-In and Date-Out. Factor the DateDiff between the two and multiply by 24 hours and add it in to your calculation. This will eliminate the negative numbers.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts