Results 1 to 3 of 3
  1. #1
    brianb is offline Novice
    Windows XP Access 2003
    Join Date
    Feb 2011
    Posts
    8

    Time minus one for hour.

    I'm trying to write a query that uses the time in another table which is set to EST and convert it to CST or MST depending on what the region is in another field in the same table.



    Here is what I came up with but it dones't work. But basically I want if SPC2.ReceivingSite is STL, IRV, DAL then I want my new field to TimeLocal to be SPC2.CustCallBackTime(EST) - 1 and then -2 if the site is TSN or PHX

    What I have below does not seem to work. Thx for any help.

    Code:
    TimeLocal: IIf([SPC2]![ReceivingSite] In ('STL','DAl','IRV'), TimeSerial(Hour([SPC2]![CustCallBackTime(EST)]-1),Minute([SPC2]![CustCallBackTime(EST)]), Second([SPC2]![CustCallBackTime(EST)])), 
    IIf([SPC2]![ReceivingSite] In ('PHX','TSN'), TimeSerial(Hour([SPC2]![CustCallBackTime(EST)]-2),Minute([SPC2]![CustCallBackTime(EST)]), Second([SPC2]![CustCallBackTime(EST)])), [SPC2]![CustCallBackTime(EST)]))

  2. #2
    William McKinley is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Jul 2010
    Posts
    59
    If the field you are subtracting is a Date/Time, have you tried using DateAdd?


    Code:
    DateAdd("h",-1,[SPC2]![CustCallBackTime(EST)])

  3. #3
    brianb is offline Novice
    Windows XP Access 2003
    Join Date
    Feb 2011
    Posts
    8
    Quote Originally Posted by William McKinley View Post
    If the field you are subtracting is a Date/Time, have you tried using DateAdd?


    Code:
    DateAdd("h",-1,[SPC2]![CustCallBackTime(EST)])

    looks like that will work. Thanks a lot.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 03-02-2011, 01:43 PM
  2. Round up time half an hour
    By JBM18 in forum Queries
    Replies: 2
    Last Post: 12-09-2010, 11:56 AM
  3. Replies: 1
    Last Post: 04-15-2010, 02:07 AM
  4. [Access 2003] Displaying 24 Hour Time
    By Trainman in forum Access
    Replies: 4
    Last Post: 03-18-2009, 06:34 PM
  5. Replies: 1
    Last Post: 11-11-2006, 08:00 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums