Tags: access, criteria, database, date, microsoft, mysql, oracle, previous, queries, run, select, sql

date criteria in Access 97 queries: last month

On Database » Microsoft Access

1,361 words with 3 Comments; publish: Wed, 05 Dec 2007 04:31:00 GMT; (250140.63, « »)

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 ?

All Comments

Leave a comment...

  • 3 Comments
    • 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())

      #1; Tue, 11 Dec 2007 20:09:00 GMT
    • 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 ?

      #2; Tue, 11 Dec 2007 20:10:00 GMT
    • 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

      ;)

      #3; Tue, 11 Dec 2007 20:11:00 GMT