mysql - 在 Append Into 语句中返回日期的语法

Question

在 Access 2010 数据库的搜索表单上使用以下代码会按日期返回搜索结果，但预览时报表为空。搜索表单按发票编号返回搜索结果，预览时报告非常完美。我认为错误出现在“附加到”行中，与日期格式有关，并且无法找出将日期搜索结果附加到临时表的正确语法。建议或解决方案将不胜感激。

 'Search for a specific date on date field
If IsDate(Me.TxtSearch) Then 'DATE SEARCH WORKS
SearchDate = Format(Me.TxtSearch.Value, Shortdate)
Task = "SELECT * FROM [Invoices] WHERE ((InvoiceDate Like ""*" & SearchDate & "*""))"
Me.RecordSource = Task
Me.TxtSearch.BackColor = vbWhite
Else
strsearch = Trim(Me.TxtSearch)
strsearch = Me.TxtSearch.Value
Task = "Select * FROM [Invoices] WHERE ((InvoiceNo Like ""*" & strsearch & "*"") OR (InvoiceDate Like ""*" & SearchDate & "*""))"
Me.RecordSource = Task
Me.TxtSearch.BackColor = vbWhite
'Append data matching keyword from the search result into the Temp Invoices table
strAppend = "INSERT INTO [Invoices_Temp] SELECT * FROM [Invoices] WHERE ((InvoiceNo Like ""*" & strsearch & "*"") OR (InvoiceDate Like #" & SearchDate & "#))"
'strAppend = "INSERT INTO [Invoices_Temp] SELECT * FROM [Invoices] WHERE ((InvoiceNo Like ""*" & strsearch & "*"") AND (InvoiceDate Like ""*" & SearchDate & "" * "))"
DoCmd.RunSQL strAppend
DoCmd.SetWarnings True

Answer 1

您需要一个正确的日期字符串表达式格式，Like 不适用于日期:

SearchDate = Format(Me!TxtSearch.Value, "yyyy\/mm\/dd")
Task = "SELECT * FROM [Invoices] WHERE (InvoiceDate = #" & SearchDate & "#)"