使用 vba access 2007 过滤日期范围
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/24692701/
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
filter a date range using vba access 2007
提问by ahc59
I've got the following problem. In my table is a field named "datum" This field is from a date type. this field is european format (dd/mm/yyyy) I also have a popupform, with two fields named "begindatum" and "einddatum" When I fill in the dates in the popupform, all I want see the are records in the subform I selected in the popupform
我有以下问题。在我的表中有一个名为“datum”的字段，该字段来自日期类型。这个字段是欧洲格式 (dd/mm/yyyy) 我也有一个弹出表单，有两个字段名为“begindatum”和“einddatum”当我在弹出表单中填写日期时，我想看到的都是子表单中的记录我在弹出窗口中选择
Here is my code till so far,
到目前为止，这是我的代码，
CODE
代码
Private Sub cmdFilterAan_Click()
Dim strFilter As String
If begindatum <> "" Then
strFilter = strFilter & " And begindatum=#" & Format(CDate(Me.begindatum), "dd/mm/yyyy") & "#"
End If
If einddatum <> "" Then
strFilter = strFilter & " And einddatum=#" & Format(CDate(Me.einddatum), "dd/mm/yyyy") & "#"
End If
If naam <> "" Then
strFilter = strFilter & " And naam=" & Me.naam
End If
If beroep <> "" Then
strFilter = strFilter & " And beroep=" & Me.beroep
End If
If strFilter <> "" Then
strFilter = Mid(strFilter, 6)
Forms!frmData.FilterOn = True
Forms!frmData.Filter = strFilter
End If
End Sub
The strFilter for "naam" and "beroep" are working fine, but for the date fields its not. I think I'am looking for a BETWEEN statement in VBA code, how to do so I do not know.
“naam”和“beroep”的 strFilter 工作正常，但对于日期字段则不然。我想我正在 VBA 代码中寻找 BETWEEN 语句，我不知道该怎么做。
When I fill in the dates in my popupform nothing happens, as a matter of fact the records in the subform all disapears
当我在弹出窗口中填写日期时，没有任何反应，事实上，子表单中的记录都消失了
Can anyone help me to solve thus problem. thx in advance greetings, ahc59
谁能帮我解决这个问题。谢谢提前问候，ahc59
回答by VBlades
Try passing your dates in mm/dd/yyyy format. Filter seems particular about using that format. So, for instance:
尝试以 mm/dd/yyyy 格式传递您的日期。过滤器似乎特别喜欢使用这种格式。因此，例如：
If begindatum <> "" Then
strFilter = strFilter & " And begindatum=#" & Format(CDate(Me.begindatum), "mm/dd/yyyy") & "#"
End If
If einddatum <> "" Then
strFilter = strFilter & " And einddatum=#" & Format(CDate(Me.einddatum), "mm/dd/yyyy") & "#"
End If
If you want to do between the dates, you can do:
如果你想在日期之间做，你可以这样做：
If begindatum <> "" And einddatum <> "" Then
strFilter = strFilter & " And begindatum >= #" & Format(CDate(Me.begindatum), "mm/dd/yyyy") & "# And einddatum <= #" & Format(CDate(Me.einddatum), "mm/dd/yyyy") & "#"
End If
You can't use BETWEEN here because the dates are in two different fields.
您不能在此处使用 BETWEEN，因为日期位于两个不同的字段中。
回答by Philip D.
Unfortunately in the core acces vba works with US format ("mm/dd/yyyy"), i used on my form to filter dates this code, where [Data] is a field from a table :
不幸的是，在核心 acces vba 中使用美国格式（“mm/dd/yyyy”），我在表单上使用此代码过滤日期，其中 [Data] 是表中的一个字段：
Function Today()
Dim dDate As String
dDate = Format(Date, "mm/dd/yyyy")
strFilter2 = "[Data] = #" & dDate & "#"
Forms!MainForm!SubForm.Form.Filter = strFilter2
Forms!MainForm!SubForm.Form.FilterOn = True
End Function
Function ThisWeek()
dDate = Format(Date - 2, "mm/dd/yyyy")
dDate2 = Weekday(dDate)
dDate3 = Date - dDate2
dDate4 = Format(Date - 1, "mm/dd/yyyy")
dDate5 = Weekday(dDate4)
dDate6 = 7 - dDate5
dDate7 = Date + dDate6
strFilter3 = "[Data] between #" & dDate3 & "# AND #" & dDate7 & "#"
Me.Refresh
Forms!MainForm!SubForm.Form.Filter = strFilter3
Forms!MainForm!SubForm.Form.FilterOn = True
End Function
Function ThisMonth()
dDate = Date - Day(Date) + 1
dDate2 = DateSerial(Year(Date), Month(Date) + 1, 0)
dDate3 = Format(dDate, "mm/dd/yyyy")
dDate4 = Format(dDate2, "mm/dd/yyyy")
strFilter3 = "[Data] between #" & dDate3 & "# AND #" & dDate4 & "#"
Me.Refresh
Forms!MainForm!SubForm.Form.Filter = strFilter3
Forms!MainForm!SubForm.Form.FilterOn = True
End Function
Function ThisYear()
dDate = "1/1/" & Year(Date)
dDate2 = "31/12/ " & Year(Date)
dDate3 = Format(dDate, "mm/dd/yyyy")
dDate4 = Format(dDate2, "mm/dd/yyyy")
strFilter3 = "[Data] between #" & dDate3 & "# AND #" & dDate4 & "#"
Me.Refresh
Forms!MainForm!SubForm.Form.Filter = strFilter3
Forms!MainForm!SubForm.Form.FilterOn = True
End Function
Function All()
'If you have default to show them all
Forms!MainForm!SubForm.Form.FilterOn = False
End Function
