Results 1 to 6 of 6
  1. #1
    Star Lounger
    Join Date
    Dec 2007
    Posts
    98
    Thanks
    0
    Thanked 0 Times in 0 Posts

    TimeValue vs Target Time (2003)

    Field 1 is "SENT_TIME"--- 9/30/2008 10:42:39 AM
    Field 2---SentTimeOnly: TimeValue([SENT_TIME])
    Field 3 WhenSort: IIf([SentTimeOnly]>TimeSerial(9,50,0),3,IIf([SentTimeOnly]>TimeSerial(9,30,0),2,1))......This is the target time
    Field 4 When: IIf([SentTimeOnly]>TimeSerial(9,50,0),"Late",IIf([SentTimeOnly]>TimeSerial(9,30,0),"Last","Prior"))

    There used to be data for all the Field 1 so my formula above worked fine. My boss revised the database where if the time in Field 1 is on-time then there no data for the Field 1 so now I get "#Error" for Field 2, 3, 4. Since I have to include the on-time %, how can I modify my formula to accommodate this change. Thank you for sharing your brain power.

  2. #2
    Star Lounger
    Join Date
    Dec 2007
    Posts
    98
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: TimeValue vs Target Time (2003)

    I copied and pasted your formula but I don't know why the system changed this #0:00# to #12:00:00 AM# and it gives me this message " Syntax error (comma) in query expression 'TimeValue(Nz(([SENT_TIME],#12/30/1899#)))'.

    Thank you...u r up awfully late

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: TimeValue vs Target Time (2003)

    Edited by HansV to correct typo

    Try

    SentTimeOnly: TimeValue(Nz([SENT_TIME],#0:00#))

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: TimeValue vs Target Time (2003)

    Sorry, it was air code and I had included one opening parenthesis too many.

    It should have been TimeValue(Nz([TIME_SENT],#0:00#)), or equivalently TimeValue([TIME_SENT],#12:00:00 AM#))

    You don't have to worry about the #12:00:00 AM# (or #12/30/1899#), they amount to the same as #0:00#.

  5. #5
    Star Lounger
    Join Date
    Dec 2007
    Posts
    98
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: TimeValue vs Target Time (2003)

    The first formula wants me to "Enter Parameter Value" for SENT_TIME and the second formula gives me "The expression you entered has a function containing the wrong number of arguments.

  6. #6
    Star Lounger
    Join Date
    Dec 2007
    Posts
    98
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: TimeValue vs Target Time (2003)

    Oops...please disregard my previous message. I just tried the formula that you revised and it works beautifully. Many...dahnk!!!

Posting Permissions

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