Results 1 to 4 of 4
  1. #1
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Updating Dates (Access2000, SR1)

    Use the following syntax :
    DoCmd.RunSQL "UPDATE MainDB SET MainDB.NextScheduledUseDate = DateAdd('d',15,[MainDB].[Date]) WHERE MainDB.Rotation_ID = 2"
    Francois

  2. #2
    New Lounger
    Join Date
    Jun 2001
    Posts
    15
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Updating Dates (Access2000, SR1)

    Thanks, Francois!

    That made it work perfectly.

    Merci beaucoup!
    Robert

  3. #3
    New Lounger
    Join Date
    Jun 2001
    Posts
    15
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Updating Dates (Access2000, SR1)

    Here's a mystery for me.

    I have two dates in a file, "DATE" and "NextScheduledUseDate" along with a rotation field. The rotation can be WEEKLY (key=6), BI_WEEKLYy (key=2), or QUARTERLY (key=7).

    I want to be able to automatically increase the Next ScheduledUseDate for a record according to its rotation.

    I set up a macro and converted it to a module. Here's the code:

    '------------------------------------------------------------
    ' macroUpdateNUD_UpdateNUD
    '
    '------------------------------------------------------------
    Function macroUpdateNUD_UpdateNUD()
    On Error GoTo macroUpdateNUD_UpdateNUD_Err

    DoCmd.RunSQL "UPDATE MainDB SET MainDB.NextScheduledUseDate = [MainDB].[Date] + Day(15) WHERE MainDB.Rotation_ID = 2", 0

    DoCmd.RunSQL "UPDATE MainDB SET MainDB.NextScheduledUseDate = [MainDB].[Date] + Day(8) WHERE MainDB.Rotation_ID = 6", 0

    DoCmd.RunSQL "UPDATE MainDB SET MainDB.NextScheduledUseDate = [MainDB].[Date] + Day(90) WHERE MainDB.Rotation_ID = 7", 0

    macroUpdateNUD_UpdateNUD_Exit:
    Exit Function

    macroUpdateNUD_UpdateNUD_Err:
    MsgBox Error$
    Resume macroUpdateNUD_UpdateNUD_Exit

    End Function


    The results I am getting are just WAAAAAY off.

    Can anyone help me code this accurately?

    Thanks,
    Robert

  4. #4
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Updating Dates (Access2000, SR1)

    If you are just adding days to a date, you don't even need DateAdd. For example, to add 8 days to MainDB.Date, all you need is:
    MainDB.Date + 8

    BTW, you should get out of the habit of using "Date" or any other such fieldnames that conflict with Access constants or functions. In the long run, you will save yourself some aggravation.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

Posting Permissions

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