visual-studio Getdate() 等效于 Jet / Access 数据库。需要上个月的记录
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2136552/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Getdate() equivalent for Jet / Access database. Need last month records
提问by chupeman
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:
我正在阅读其他发布的问题，并找到了许多示例来检索上个月的记录。我正在使用 Visual Studio 2008 查询生成器从 Access mdb 中检索记录，当我输入以下查询时，它向我显示了 getdate 不是有效函数的错误：
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?
从 mdb 中提取上个月记录的正确 sql 查询是什么？
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.
任何帮助表示赞赏。
采纳答案by Arvo
WHERE
DATEDIFF('m', [Transaction Date], DATE()) = 1
回答by Fitzchak Yitzchaki
The Getdate() equivalent in access is Now().
Getdate() 在访问中的等价物是 Now()。
回答by Kevin Ross
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
然后，您可以像这样在 SQL 查询中使用此函数
SELECT tblFoo.*
FROM tblFoo
WHERE (((Created_date) Between Common_dates_SQL('Start_last_month') And Common_dates_SQL('END_last_month')));
回答by Fionnuala
The zeroth day of the month is the last day of the previous month, this works in both Jet SQL and VBA.
该月的第 0 天是上个月的最后一天，这适用于 Jet SQL 和 VBA。
End of last month:
上月底：
DateSerial(Year(Date()),Month(Date()),0)
Start of last month:
上个月的开始：
DateSerial(Year(Date()),Month(Date())-1,1)
