基于用户定义参数的 IIf 函数
我正在尝试根据输入表单的参数开发一个查询。我想确保即使用户选择不输入参数（例如开始和结束日期）或者用户只输入一半参数（例如用户只输入开始但不输入结束日期或结束日期但不是开始日期）。这就是我想要的：
如果开始日期和结束日期为空，则返回所有日期，
否则，如果开始日期为空，则返回小于或等于给定结束日期的日期，
否则，如果结束日期为空，则返回大于或等于给定开始日期的日期，
否则，返回给定开始日期和结束日期之间的所有日期
我想出的内容似乎没有评估超出第一个正确的语句，我不确定为什么。
IIf(IsNull([forms]![Volunteer_Hours_By_Person]![Volunteer_Hours_By_Person_Start_Date]
And [forms]![Volunteer_Hours_By_Person]![Volunteer_Hours_By_Person_End_Date]),
[Volunteer_Date],
IIf(IsNull([forms]![Volunteer_Hours_By_Person]![Volunteer_Hours_By_Person_Start_Date]),
<= [forms]![Volunteer_Hours_By_Person]![Volunteer_Hours_By_Person_End_Date],
IIf(IsNull([forms]![Volunteer_Hours_By_Person]![Volunteer_Hours_By_Person_End_Date]),
>= [forms]![Volunteer_Hours_By_Person]![Volunteer_Hours_By_Person_Start_Date],
(Between [forms]![Volunteer_Hours_By_Person]![Volunteer_Hours_By_Person_Start_Date]
And [forms]![Volunteer_Hours_By_Person]![Volunteer_Hours_By_Person_End_Date]))))
I'm trying to develop a query based on parameters entered into a form. I want to make sure that the query will still work even if the user chooses not to enter parameters (ex. start and end date) or if the user only enters half of the parameters (ex. user only enters start but not end date or end but not start date). This is what I want:
If start date and end date is null, then return all dates,
else, If start date is null, then return dates less than or equal to the given end date,
else, If end date is null, then return dates greater than or equal to the given start date,
else, Return all dates between the given start and end date
What I've come up with doesn't seem to evaluated beyond the first true statement and I'm not sure why.
IIf(IsNull([forms]![Volunteer_Hours_By_Person]![Volunteer_Hours_By_Person_Start_Date]
And [forms]![Volunteer_Hours_By_Person]![Volunteer_Hours_By_Person_End_Date]),
[Volunteer_Date],
IIf(IsNull([forms]![Volunteer_Hours_By_Person]![Volunteer_Hours_By_Person_Start_Date]),
<= [forms]![Volunteer_Hours_By_Person]![Volunteer_Hours_By_Person_End_Date],
IIf(IsNull([forms]![Volunteer_Hours_By_Person]![Volunteer_Hours_By_Person_End_Date]),
>= [forms]![Volunteer_Hours_By_Person]![Volunteer_Hours_By_Person_Start_Date],
(Between [forms]![Volunteer_Hours_By_Person]![Volunteer_Hours_By_Person_Start_Date]
And [forms]![Volunteer_Hours_By_Person]![Volunteer_Hours_By_Person_End_Date]))))
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
知道了！
Got it!