1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Update a time field with access vba

Discussion in 'Business Applications' started by BoaterJohn, May 2, 2012.

Thread Status:
Not open for further replies.
Advertisement
  1. BoaterJohn

    BoaterJohn Thread Starter

    Joined:
    Aug 2, 2011
    Messages:
    116
    Update a time field
    I have a Table with 2 fields. The 1st field is Labeled "TimeStopped". The 2nd field is Labeled "TimeDiff". I need to subtract the next record of TimeStopped field from the preceeding record TimeStopped field and put the difference in field 2 TimeDiff so I can know how long each stop was.
    Any help in using Vba to do this.
    Thanx,
    john
     
  2. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    The only way I know how to do what you want in a Form is to use VBA using a RecordsetClone to look up the previous record's Timestopped value.
    You can however do it in a Query providing you do do not need to store the value, just display it.
     
  3. BoaterJohn

    BoaterJohn Thread Starter

    Joined:
    Aug 2, 2011
    Messages:
    116
    Thanks for the response, I do need to save the difference in time which will be in min. and secs. ie.... field "TimeStopped" in record one might have 13:14
    field "TimeStopped" in record two might be 14:15
    Then subtract "timestopped" 1 from "timestopped 2 and put answer in field "TimeDiff" using access vba. Thank you
     
  4. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    John, you need to create a recordsetclone using

    dim rs as object
    set rs = me.recordsetclone
    rs.bookmark = me.bookmark

    and then move to the previous record with

    rs.moveprevious

    check that it has the correct Timestopped value using

    msgbox rs.TimeStopped


    If that all works we can do the calculation, this is from memory as my computer with all my data on has died and I am now working on a new one.
     
  5. BoaterJohn

    BoaterJohn Thread Starter

    Joined:
    Aug 2, 2011
    Messages:
    116
    Thanx, OBP, will give it a go and let you know outcome (good i hope)
     
  6. BoaterJohn

    BoaterJohn Thread Starter

    Joined:
    Aug 2, 2011
    Messages:
    116
    is it possible to attach my small access db file so you can see it??
    john
     
  7. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    Yes, ensure it has no personal data in it. Zip it and then use the "Go Advanced" and then "Manage Attachment" buttons.
     
  8. BoaterJohn

    BoaterJohn Thread Starter

    Joined:
    Aug 2, 2011
    Messages:
    116
    OBP,
    file is attached. it is very small, but will grow each day with new records.
    Thanks Much,
    boaterjohn
     

    Attached Files:

  9. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    John, the VBA code can actually do the calculation as you enter the data, would you prefer that?
     
  10. BoaterJohn

    BoaterJohn Thread Starter

    Joined:
    Aug 2, 2011
    Messages:
    116
    (y) That would be great.

    Thanks Much OBP
     
  11. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    Do you want the Time difference shown as Hours or days and hours?
     
  12. BoaterJohn

    BoaterJohn Thread Starter

    Joined:
    Aug 2, 2011
    Messages:
    116
    minutes and sec's (short time) see my property setting in my tables which shows the settings ie.... 16:24 or 04:23 etc...
     
  13. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    But you have days between the dates, what about those?
     
  14. BoaterJohn

    BoaterJohn Thread Starter

    Joined:
    Aug 2, 2011
    Messages:
    116
    that is a error on my part. The most time ever between stops will be 2 hrs at max.
    thanks,
    john
     
  15. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    OK, no problem, but do you need to take overnight in to consideration?
    ie 11:00pm to 01:00am
    or do they stop before that?
     
  16. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Similar Threads - Solved Update field
  1. ozegirl
    Replies:
    6
    Views:
    2,260
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1051681

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice