Results 1 to 11 of 11
  1. #1
    Join Date
    Oct 2003
    Posts
    233

    Unanswered: getting the last date of prior month

    is there a function that will return me 10/31 if run today, 11/30 if run in dec...

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    dateadd("d",-day(date())-1,date())

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Oct 2003
    Posts
    233
    Quote Originally Posted by r937
    dateadd("d",-day(date())-1,date())


    That gave me 10/30/07 so i changed to
    DateAdd("d", -day(Date), Date)

    can you explain this to me pls?

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    aaaarghh, of course!! the extra -1 was not necessary!! <slaps forehead>

    why don't you give the explanation a try -- look up how the DAY() function works, and then look up how the DATEADD() function works
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Oct 2003
    Posts
    233
    Quote Originally Posted by r937
    aaaarghh, of course!! the extra -1 was not necessary!! <slaps forehead>

    why don't you give the explanation a try -- look up how the DAY() function works, and then look up how the DATEADD() function works

    ok, how can i use this in a qry as a criteria?

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    select sum(amount) as YTD_to_last_month
      from invoices
     where payment_date >  dateadd("d",-datepart("y",date()),date())
       and payment_date <= dateadd("d",-day(date()),date())
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    uh oh, that won't work in january :blush:

    sorry

    well, you get the idea... i hope...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    oh wait -- YTD_to_last_month is null in january! there is no month previous to january in the same year!
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    Subtract 1 day from the 1st day of the current month and year

    Code:
    DateAdd("d", -1, cDate(Month(Date) & "/1/" & Year(Date)))
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  10. #10
    Join Date
    Jan 2007
    Location
    California, USA
    Posts
    520
    I have found this to be very helpful for the last day of a month. Show will be the last day of last month.
    =DateSerial(Year(Date(), Month(Date), 0)
    The zero in the Day parameter causes DateSerial to produce the last day of the month before the month identified with the Year and Month parameters.

  11. #11
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    I've used the same expression Vic showed with success for finding the last day of the previous month.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

Posting Permissions

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