Getdate() equivalent for Jet / Access database. Need last month records.

Question

Hello,

I was reading other questions posted and found many examples to retrieve last month records. I am using Visual Studio 2008 query builder to retrieve records from an Access mdb and when I enter the following query it is displaying me an error that getdate is not a valid function:

where [Transaction Date]     
   between dateadd(mm, datediff(mm, 0, dateadd(MM, -1, getdate())), 0)
       and dateadd(ms, -3, dateadd(mm, datediff(mm, 0, dateadd(MM, -1, getdate())) + 1, 0))

What is the correct sql query to extract last month records from an mdb?

This is a query I have, but it is giving me records from this month also amd just need last month:

  SELECT
   [Product Code], [Description One], [Transaction Number], Quantity, [Sales Value], Cost, [Transaction Date], [Transaction Time], Department, [Type Code], Cashier, [Computer Name], [Customer Code]
  FROM
   [Product History] 
  WHERE
   ([Transaction Date] >= DATEADD('m', - 2, NOW()))

Any help is appreciated.

Answer 1

The Getdate() equivalent in access is Now().

Answer 2

I tend to make a custom function in access to work out the start and end of next month and other common dates. Here is a sample of the function with the start of next month and end of next month defined

Public Function Common_dates_SQL(strCommon_date As String) As Date
On Error GoTo Error_trap:

Select Case strCommon_date

    Case "Start_Last_Month"
        Common_dates_SQL = Date - ((DateDiff("d", DateValue("01/" & DatePart("m", Date) & "/" & DatePart("yyyy", Date)), Date)) + 1)

    Case "End_Last_Month"
    Common_dates_SQL = (Date - ((DateDiff("d", DateValue("01/" & DatePart("m", Date) & "/" & DatePart("yyyy", Date)), Date)) + 1)) - (DatePart("d", Date - ((DateDiff("d", DateValue("01/" & DatePart("m", Date) & "/" & DatePart("yyyy", Date)), Date)) + 1)) - 1)

End Select
DoCmd.Hourglass False
Exit Function

Error_trap:
DoCmd.Hourglass False
MsgBox "An error happened in sub Common_dates, error description " & Err.Description, vbCritical, "FRapps"

End Function

The full function goes on for a lot longer and include quarters/years and other things that I get asked for

You can then use this function in your SQL query like this

SELECT tblFoo.*
FROM tblFoo
WHERE (((Created_date) Between Common_dates_SQL('Start_last_month') And Common_dates_SQL('END_last_month')));

Answer 3

WHERE
    DATEDIFF('m', [Transaction Date], DATE()) = 1

Answer 4

The zeroth day of the month is the last day of the previous month, this works in both Jet SQL and VBA.

End of last month:

 DateSerial(Year(Date()),Month(Date()),0)

Start of last month:

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