Results 1 to 5 of 5
  1. #1
    Star Lounger
    Join Date
    Jun 2002
    Location
    Johannesburg, Gauteng, South Africa
    Posts
    59
    Thanks
    2
    Thanked 0 Times in 0 Posts

    End of Month (2003)

    Hi, I need some code to emulate the Excel function EOMONTH. I have a field called "Date" which I would like to auto-populate based on the previous record's date. I have tried Date = DateAdd("m", 1, OldDate) where OldDate is the Date of the previous record. This works fine if this month has less days than last month but not if it has more. For example in May it will give me 30 May 2007 which I manually have to change to 31 May 2007. June will be correct at 30 June 2007 but July will again be incorrect at 30 July 2007 instead of 31 July 2007.

    Any help will, as usual, be greatly appreciated.

    Thanks

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: End of Month (2003)

    Try

    DateSerial(Year([OldDate]), Month([OldDate]) + 1, 1)

  3. #3
    Star Lounger
    Join Date
    Jun 2002
    Location
    Johannesburg, Gauteng, South Africa
    Posts
    59
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: End of Month (2003)

    Thanks for the quick reply Hans, but that gives me 01 May 2007 instead of 31 May 2007 - The value of OldDate is 30 April 2007 (30/04/2007).

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: End of Month (2003)

    Sorry, my mistake. It should have been

    DateSerial(Year([OldDate]), Month([OldDate]) + 2, 0)

  5. #5
    Star Lounger
    Join Date
    Jun 2002
    Location
    Johannesburg, Gauteng, South Africa
    Posts
    59
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: End of Month (2003)

    Thanks Hans, works like a charm

Posting Permissions

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