Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2009
    Posts
    4

    Unanswered: Future Time Calculation

    I have three fields that I would like to use to calculate a future time.

    1. j_sysdate =Now()
    2. j_eta =[number of minutes entered in the field]
    3. j_timeofarrival [j_sysdate + j_eta]

    I would like to take the time from j_sysdate add the minutes entered from j_eta to come up with a value to be stored in j_timeofarrival. (1+2=3)

    I have been working with the functions is the default value field, but have not been able to come up with a working formula.

    Any ideas?

  2. #2
    Join Date
    Sep 2002
    Location
    South Wales
    Posts
    580
    Use dateadd

    j_timeofarrival = DATEADD("h", nz(j_eta,0), j_sysdate)
    Windows Server 2003-8 / Terminal Services / SQL 2000 / Access 2003 / Office 2003-7 / Exchange 2003-7 / Blackberry Enterprise Server / AutoCAD / Lambert And Butler / Red Bull

  3. #3
    Join Date
    Mar 2009
    Posts
    4
    Would I enter this expression into the default value of j_timeofarrival? Also what does the "nz" reference?

  4. #4
    Join Date
    Sep 2002
    Location
    South Wales
    Posts
    580
    You would never STORE this in your database as it can always be calculated - this would be used to display on a report or form.

    The NZ ensures that if no data is in j_eta, it doesn't try to add 'Null' (nothing not even zero) to a date - it will always be at least 0.
    Windows Server 2003-8 / Terminal Services / SQL 2000 / Access 2003 / Office 2003-7 / Exchange 2003-7 / Blackberry Enterprise Server / AutoCAD / Lambert And Butler / Red Bull

Posting Permissions

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