Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2004
    Posts
    6

    Unanswered: date criteria in Access 97 queries: last month

    I need to select all days of previous month as a criteria in some queries that must be run every month. I tried :

    Between DateSerial(Year(Date()),Month(Date())-1,1) And DateSerial(Year(Date()),Month(Date()),1)-1

    but it doesn't work.
    Can you help me ?

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    whatever today is, pull out the DAY of the month, and subtract that number of days from today, and bingo, you have the last day of the previous month:
    DATEADD("d",-DAY(DATE()),DATE())

    then add 1 day back to this to get the first day of the current month:
    DATEADD("d",-DAY(DATE())+1,DATE())

    now subtract 1 month to get first day of previous month:
    DATEADD("m",-1,DATEADD("d",-DAY(DATE())+1,DATE()))

    now you can write the BETWEEN clause:

    BETWEEN
    DATEADD("m",-1,DATEADD("d",-DAY(DATE())+1,DATE()))
    AND
    DATEADD("d",-DAY(DATE()),DATE())
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Apr 2004
    Posts
    6
    Thanks for the suggestion, but I still have a problem.

    Here is the error message I get, using your string:
    [IBM][CLI dirver][DB2] SQL0181N The string representation of datetime value is out of range. SQLSTATE=22007 (#181)

    (Data are in DB2 tables, with an ODBC link in Access)

    Any idea ?

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    any idea? yes, use db2 syntax, not access syntax

    between
    current_date - day(current_date) days + 1 day - 1 month
    and
    you do this part

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

Posting Permissions

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