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; (25078.13, « »)

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...

    • 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:


      then add 1 day back to this to get the first day of the current month:


      now subtract 1 month to get first day of previous month:


      now you can write the BETWEEN clause:





      #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


      current_date - day(current_date) days + 1 day - 1 month


      you do this part


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