Results 1 to 6 of 6
  1. #1
    pwyller is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2016
    Posts
    5

    Question Macro to increment a date in an existing field

    I have a table that just contains two dates that are used in a Crystal Reports report, and I need a macro that I can run that will increment those dates or make them the current day's date.

    I've seen a thousand and one macros for adding an incremented date as a new record and this is not what I need



    Thanks!

  2. #2
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    You haven't really provided enough information

    If your table is a single record and you want to increment (or change) the date in a particular field you would do something like

    currentdb.execute ("UPDATE tblMyTestTable SET TestDateField = #" & date() & "#")

    to set the field 'testdatefield' equal to today's date

    dim dValue as date

    dvalue = dlookup("[TestDateField]", "tblMyTestTable")
    currentdb.execute ("UPDATE tblMyTestTable SET TestDateField = #" & dateadd("d", 1, dvalue) & "#")

    to increment the field 'testdatefield' by 1 day

    if your table has more than 1 record I would question the methods you're using.

  3. #3
    pwyller is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2016
    Posts
    5
    Perfect!

    Here's what I have that works:

    Private Sub Command1_Click()


    Dim dValue As Date
    Dim dValue2 As Date


    dValue = DLookup("[StartDate]", "dbo_ReportDates")
    CurrentDb.Execute ("UPDATE dbo_ReportDates SET StartDate = #" & DateAdd("d", 1, dValue) & "#")
    dValue2 = DLookup("[EndDate]", "dbo_ReportDates")
    CurrentDb.Execute ("UPDATE dbo_ReportDates SET EndDate = #" & DateAdd("d", 1, dValue) & "#")


    End Sub

    Now, I need to change this to check to see if the current day is a Friday, and then change the increment on the EndDate to 3.

    Thanks again!

  4. #4
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    So your StartDate will always be the current date and EndDate the next day after StartDate(except for weekends)? If so maybe something like this:

    CurrentDb.Execute ("UPDATE dbo_ReportDates SET StartDate = #" & Date() & "#")

    If Weekday(Date()) = vbFriday then
    CurrentDb.Execute ("UPDATE dbo_ReportDates SET EndDate = #" & DateAdd("d", 3, Date()) & "#")
    Else
    CurrentDb.Execute ("UPDATE dbo_ReportDates SET EndDate = #" & DateAdd("d", 3, Date()) & "#")
    End IF

  5. #5
    pwyller is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2016
    Posts
    5
    Quote Originally Posted by Bulzie View Post
    So your StartDate will always be the current date and EndDate the next day after StartDate(except for weekends)? If so maybe something like this:

    CurrentDb.Execute ("UPDATE dbo_ReportDates SET StartDate = #" & Date() & "#")

    If Weekday(Date()) = vbFriday then
    CurrentDb.Execute ("UPDATE dbo_ReportDates SET EndDate = #" & DateAdd("d", 3, Date()) & "#")
    Else
    CurrentDb.Execute ("UPDATE dbo_ReportDates SET EndDate = #" & DateAdd("d", 3, Date()) & "#")
    End IF
    That works; except I changed the ELSE to the same as the StartDate code

  6. #6
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    Oops!! Yep would be
    Else
    CurrentDb.Execute ("UPDATE dbo_ReportDates SET EndDate = #" & DateAdd("d", 1, Date()) & "#")

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

Similar Threads

  1. Replies: 15
    Last Post: 05-20-2014, 02:10 PM
  2. Auto increment on existing number column
    By lcsgeek in forum Programming
    Replies: 1
    Last Post: 04-15-2013, 11:27 AM
  3. Replies: 1
    Last Post: 08-08-2012, 01:42 PM
  4. Replies: 3
    Last Post: 06-21-2012, 05:25 PM
  5. Replies: 1
    Last Post: 07-07-2010, 04:22 PM

Tags for this Thread

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