Results 1 to 8 of 8
  1. #1
    znxm0i is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2015
    Location
    Oklahoma
    Posts
    11

    Trouble Creating Expiry Date Expression

    Hello all,

    It's been a very long time since I've tried to work with Access and I'm now having to try and dust off my skills. I have the following issue:

    I have a table that has the following fields:
    Execution Date
    Duration


    Expiry Date

    The user will input a date in the the Execution Date field, and a numeric number in the Duration field. Based on the Execution Date field, and the numeric value in the Duration field, the Expiry Date field should auto-populate accordingly. But I don't understand how to write the expression. Below is what I have so far but it's not giving me the desired outcome because I don't know where to insert Duration into the expression.
    DateSerial(Year([Execution Date])+1,Month([Execution Date])+1,0)

    I would appreciate greatly if anyone could please guide me along the right path on how to fix this. Thank you.

  2. #2
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,861
    I would use the DateAdd() function and get rid of the ExpiryDate field.?

    Calculate as required?
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  3. #3
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Also keep in mind that if Duration is (always) in days you can get the expiry date by a simple addition: ExpiryDate=[ExecutionDate] + [Duration]

    As Welshgasman said, don't store the value if it will be ALWAYS CALCULATED; if you want to "tweak" it afterwards then you'll need to store it.

    Cheers,
    Vlad
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  4. #4
    znxm0i is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2015
    Location
    Oklahoma
    Posts
    11
    Quote Originally Posted by Welshgasman View Post
    I would use the DateAdd() function and get rid of the ExpiryDate field.?

    Calculate as required?
    Thank you Vlad for providing a simple solution but for whatever reason I can't get your advice to work. Here's what I have:

    Expiry Date field with a Data Type of "calculated"
    In the Expression for this field I'm entering: [Expiry Date]=[Execution Date]+[Duration]
    BUT I keep getting an error message that says "the expression cannot be saved because it refers to itself"

    What am I NOT doing?

  5. #5
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,861
    Remove "[Expiry Date]" ?
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  6. #6
    znxm0i is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2015
    Location
    Oklahoma
    Posts
    11
    Quote Originally Posted by znxm0i View Post
    Thank you Vlad for providing a simple solution but for whatever reason I can't get your advice to work. Here's what I have:

    Expiry Date field with a Data Type of "calculated"
    In the Expression for this field I'm entering: [Expiry Date]=[Execution Date]+[Duration]
    BUT I keep getting an error message that says "the expression cannot be saved because it refers to itself"

    What am I NOT doing?

    Vlad,

    I really need to keep the Expiry Date column. I have my data in Excel and was able to get it to work there, so I took a piece of my Excel formula and tried playing around with it in the Access expression and here's what I got to work:

    DateSerial(Year([Execution Date]),Month([Execution Date]),Day([Execution Date])+[Duration]*365)

    Thank you so much for your time. Have a fantastic weekend

  7. #7
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    You too, glad to hear you got it working. The formula you have looks a bit odd, what kind of units is the Duration in?

    What do you get if you have [Execution Date]= Jan 30, 2021 and Duration =3 (days)? I think it would fail as DateSerial(2021,01,33) is not a valid date, so your Duration values would be fractions of a year (1 day=1\365)?

    DateSerial(Year([Execution Date]),Month([Execution Date]),Day([Execution Date])+[Duration]*365)

    I would have thought that using what gasman suggested would have worked for you but I do not know your data.

    Cheers,
    Vlad
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  8. #8
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    would also be interested in knowing what duration represents and the expected outcome.

    for a date of 1/3/2020 (uk format) and duration of 1, the result is 01/03/2021 - same day of month
    for a date of 31/3/2020 and duration of 1, the result is 31/03/2021 - same day of month

    Build in a leap year and a date of 29/02/2020 gives you 28/02/2021, not 29th

    and a date of 1/3/2019 gives you 29/02/2020, not 1st

    make the duration longer, to encompass 2 leap years and the expiry date drops back another day.

    seems to me duration is a number of years in which case you could use the dateadd function - except it is not a function you can use in a calculated field, only in a query or VBA

    Perhaps the impact of leap years don't matter but if you really have to have it in your table try

    DateSerial(Year([Execution Date])+[Duration],Month([Execution Date]),Day([Execution Date]))

    Personally I recommend you don't try to apply excel methodology and layouts to database tables, they are completely different animals.



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

Similar Threads

  1. Replies: 3
    Last Post: 03-05-2019, 06:48 PM
  2. Replies: 4
    Last Post: 07-20-2018, 08:31 AM
  3. Replies: 3
    Last Post: 06-20-2017, 11:05 AM
  4. Replies: 17
    Last Post: 06-17-2014, 08:07 AM
  5. expiry date query help
    By graemespence1 in forum Queries
    Replies: 3
    Last Post: 11-22-2011, 09:23 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