Results 1 to 4 of 4

Thread: Date field +1

  1. #1
    Join Date
    Oct 2007
    Posts
    127

    Unanswered: Date field +1

    I need to create an update query.

    I have two fields -

    Operation Date
    Cancelled Date

    I want to create a field or do an update query, if there has been a operation date after 24hrs of the cancellation date

    I have tried starting writing the query -

    IIf([Operation Date]>=([Cancelled Date]+1))

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    you need an update query
    Id suggest using the query designer and pulling up the update option

    generally speaking you are looking for something like...
    Code:
    update <mytable>
    set <mycolumn>=<newvalue>
    where <acolumn>=<something>
    the where claues is important, you need to make certain that you limit the update to the required records. I'd strongly suggest that if you are developing an update query, make sure you don't do it anywhere near live data... make sure you are running on a development db, or back up table.

  3. #3
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    You need the unit of measure for the 1. Use the DateAdd function to add 1 day or 24 hours or whatever unit of measure you want. I think your Where section is going to look like:

    [Operation Date]>=DateAdd("d",1,[Cancelled Date])

    "d" stands for day. You can use it to add a month, a year, etc. Also, use negative numbers to subtract days, month, years, etc.

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    dateadd is almost certainly the "right" way to add a day. however if you are using the default data storage mechanism in Access then adding one to a datetime value has the same effect. (adding 1/24 adds an hour, subtract 1/8600 subtracts one second [1/(60 seconds*60 minutes *24 hours)]

Posting Permissions

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