Getdate () for the Jet / Access database. Last month records needed

Question

I read other posted questions and found many examples for getting records from last month. I use the Visual Studio 2008 query builder to retrieve records from Access mdb, and when I enter the following query, it shows 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 retrieve records last month from mdb?

This is a query that I have, but it gives me records from this month, and is also needed 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

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

Answer 2

the equivalent of Getdate () in access is Now ().

Answer 3

I tend to create a custom function in access to the development of the beginning and end of next month and other common dates. The following is an example of a function with the beginning of the next month and the end of the next month.

 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 lasts longer and includes quarters / years and other things that ask me

Then you can use this function in your SQL query, for example

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

Answer 4

Zero 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) 

Beginning of the last month:

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