在访问查询中的日期间搜索

Question

我正在做我的顶点项目，我正在为一个记录跌倒、虐待和行为事件的设施建立一个数据库。我想让用户能够在日期范围内根据特定的条件进行搜索，但是我在查询方面遇到了一些问题。

此示例用于MRN函数的搜索，其中用户可以输入病人的MRN，以查看记录中的所有事件(跌倒、行为、虐待)。我希望有一个日期搜索集成到MRN搜索。以下是我到目前为止提出的问题：

SELECT DemographicsTable.MRN, DemographicsTable.[First Name], DemographicsTable.[Last Name], [Abuse Table].[Date of Event], [Abuse Table].[Staff Name(s)], [Abuse Table].Unit, [Abuse Table].Shift, [Abuse Table].Location, [Abuse Table].[Other Location], [Abuse Table].Incident, [Abuse Table].[Abuse Type], [Abuse Table].[Other Abuse Type], [Abuse Table].[Unsubstantial/ Substantial], [Abuse Table].Description, [Abuse Table].Medications, [Abuse Table].Diagnoses, [Abuse Table].Behaviors, [Abuse Table].Intervention
FROM DemographicsTable 
INNER JOIN [Abuse Table] 
ON DemographicsTable.MRN = [Abuse Table].MRN
WHERE ((([Abuse Table].[Date of Event]) Between Forms!DMVAForm!frmSearches!searchByMRNForm!txtStartDate And Forms!DMVAForm!frmSearches!searchByMRNForm!txtEndDate Or Forms!DMVAForm!frmSearches!searchByMRNForm!txtStartDate Is Null)) And ((DemographicsTable.MRN)=forms!DMVAForm!frmSearches!searchByMRNForm!cbMRN) Or (((DemographicsTable.MRN)=forms!DMVAForm!frmSearches!searchByMRNForm!cbMRN))
ORDER BY DemographicsTable.MRN;

表单仍然编译，但是它为病人的MRN选择所有记录，而不仅仅是日期之间的记录。我以前在一个简单的搜索中使用了“中间”函数，它起了作用，所以我不知道问题是什么。

谢谢你的帮助!

Answer 1

在两个日期之间工作，Between (dateA and dateB)。如果您有其他的范围，我建议您使用

Where( Between(dateA and dateB) or Between(dateC and dateD) or....)

Answer 2

试试这个：

SELECT 
    DemographicsTable.MRN, 
    DemographicsTable.[First Name], 
    DemographicsTable.[Last Name], 
    [Abuse Table].[Date of Event], 
    [Abuse Table].[Staff Name(s)], 
    [Abuse Table].Unit, 
    [Abuse Table].Shift, 
    [Abuse Table].Location, 
    [Abuse Table].[Other Location], 
    [Abuse Table].Incident, 
    [Abuse Table].[Abuse Type], 
    [Abuse Table].[Other Abuse Type],
    [Abuse Table].[Unsubstantial/ Substantial], 
    [Abuse Table].Description, 
    [Abuse Table].Medications, 
    [Abuse Table].Diagnoses, 
    [Abuse Table].Behaviors, 
    [Abuse Table].Intervention
FROM 
    DemographicsTable 
INNER JOIN 
    [Abuse Table] 
    ON DemographicsTable.MRN = [Abuse Table].MRN
WHERE 
    (([Abuse Table].[Date of Event] Between Forms!DMVAForm!frmSearches!searchByMRNForm!txtStartDate And Forms!DMVAForm!frmSearches!searchByMRNForm!txtEndDate) Or (Forms!DMVAForm!frmSearches!searchByMRNForm!txtStartDate Is Null)) 
    And 
    ((DemographicsTable.MRN=forms!DMVAForm!frmSearches!searchByMRNForm!cbMRN) Or (DemographicsTable.MRN=forms!DMVAForm!frmSearches!searchByMRNForm!cbMRN))
ORDER BY 
    DemographicsTable.MRN;