none
Dates RRS feed

  • Question

  • I have two dates on my form, one date is inserted in my split form by the end user: based on the date inserted how can I add 1095 days to a second date for which I want to use to send and e-mail in Outlook? I'll trigger the reminder at 30 days - I just wish the three year anniversary to populate automatically. 

    I've tried for weeks now on and off.

    Sunday, May 26, 2019 4:31 PM

All replies

  • Set the Control Source property of an unbound control in a form or report to:

        =DateAdd("yyyy",3,[DateInserted])

    Or use the same expression to return a computed column in a query.  The expression to return the date 30 days before the third anniversary would be:

        DateAdd("yyyy",3,[DateInserted])-30


    Ken Sheridan, Stafford, England

    Sunday, May 26, 2019 4:57 PM
  • Thank you Ken.

    The scenario is, I have created a database with a split form which has eight different dates when my peers have completed each one of eight mandatory learning e-learning modules. When a date is within thirty days of being out of compliance the back colour turns yellow then on the actual date the back colour turns red. Once the end user inserts the date of completion pf an e-learning module I want the other Date/Time text box to automatically populate minus the thirty days and it is this date I will use to calculate the thirty days so DateAdd is not suitable. In a Query I would use EG datenum:[Date1] – [Date2] in the Criteria Row I would use Between 1 And 30. I do not want the end user having to calculate the other date [they’re would be a training issue].

    I am aware I might have written this too complicated, it’s rather like trying to explain how to tie a shoelace but I could demonstrate how to tie a shoelace better than I could explain.

    Sunday, May 26, 2019 9:30 PM
  • Here are some examples in the debug window with some literal dates.  Date literals have been entered in the ISO standard of YYYY-MM-DD, but are returned in the UK format of dd/mm/yyyy; that merely reflects the settings on my system.

    First, as you asked originally, to add three years to a date:

    ? DateAdd("yyyy",3, #2019-05-26#)
    26/05/2022

    To determine if a date is within 30 days of the third anniversary date:

    ? #2022-04-01# > DateAdd("yyyy",3, #2019-05-26#)-30
    False
    ? #2022-05-01# > DateAdd("yyyy",3, #2019-05-26#)-30
    True

    The DateAdd function can also be used to subtract intervals from a date.  Subtraction is just negative addition, so you simply enter a negative value.  So, to compute the date 30 days prior to the third anniversary:

    ? DateAdd("d",-30,DateAdd("yyyy",3, #2019-05-26#))
    26/04/2022

    Or, using simple date arithmetic:

    ? DateAdd("yyyy",3, #2019-05-26#)-30
    26/04/2022

    It's important that when computing a value which is determined by another value only the source value is stored in a column in a table.  The derived values must be computed by expressions like the above to eliminate any possibility of update anomalies, which would be possible if a derived value were assigned to a column in a base table.

    To conditionally colour the controls in a form is a simple matter of conditional formatting using expressions which return a Boolean TRUE or FALSE, such as that illustrated above to determine if a date is within 30 days of the third anniversary date.  In place of the date literals #2022-04-01# and #2022-05-01# used in my examples, the VBA.Date function would normally be used to return the current date, on the basis of which the conditional formatting would be applied in real time.  To determine when the third anniversary has been reached or exceeded for instance:

    ? VBA.Date >= DateAdd("yyyy",3, #2019-05-26#)
    False

    ? VBA.Date >= DateAdd("yyyy",3, #2016-05-26#)
    True

    When applying the conditional formatting the above must be the first condition, and that to determine if a date is within 30 days of the third anniversary date must be the second.  The second will only come into play if the first evaluates to False.

    Ken Sheridan, Stafford, England

    Sunday, May 26, 2019 11:00 PM