我正在尝试编写用于MS Access 2010的SQL查询，以选择6个字段的任何组合。我已经将63个可能的组合写入了我的查询中，每个组合都被一个或一个OR分开。在内部，每个人都加入了。此外，每个字段都设置为从用户请求参数。
我遇到的问题是，如果我输入（示例）字段a，b和c的参数，我只会返回a和b的过滤值，而查询显示不符合参数标准的项目。
代码示例：
`SELECT Main.DISPATCHER, Main.DateCall, Main.Status, Main.[Pick Up 20], Main.[Drop Off 20], Main.Shift, Main.Day, Main.Time, Main.[Call Type], Main.[ETA Given], Main.ZONE, Main.Priority, Main.Comments, Main.DODept, Main.ID, Main.PU, Main.DO, Main.[Pick Up], Main.Caller, Main.DropOff
FROM Main
WHERE (Main.DISPATCHER=[Which Dispatcher?Ex Justin]
AND Main.DateCall BETWEEN [Enter Start Date:] AND [Enter Ending Date:]
AND Main.Status=[Status: ACCEPTED or DECLINED?]
AND Main.[Pick Up 20]=[What Pick Up?Use Facility List]
AND Main.[Drop Off 20]=[What Drop Off?-Use Facility List]
AND Main.Shift=[What shift?AM/PM])
OR (Main.DISPATCHER=[Which Dispatcher?Ex Justin]
AND Main.DateCall BETWEEN [Enter Start Date:] AND [Enter Ending Date:]
AND Main.Status=[Status: ACCEPTED or DECLINED?]
AND Main.[Pick Up 20]=[What Pick Up?Use Facility List]
AND Main.[Drop Off 20]=[What Drop Off?-Use Facility List])
OR (Main.DISPATCHER=[Which Dispatcher?Ex Justin]
AND Main.DateCall BETWEEN [Enter Start Date:] AND [Enter Ending Date:]
AND Main.Status=[Status: ACCEPTED or DECLINED?]
AND Main.[Pick Up 20]=[What Pick Up?Use Facility List])
OR (Main.DISPATCHER=[Which Dispatcher?Ex Justin]
AND Main.DateCall BETWEEN [Enter Start Date:] AND [Enter Ending Date:]
AND Main.Status=[Status: ACCEPTED or DECLINED?])
OR (Main.DISPATCHER=[Which Dispatcher?Ex Justin]
AND Main.DateCall BETWEEN [Enter Start Date:] AND [Enter Ending Date:])
OR (Main.DISPATCHER=[Which Dispatcher?Ex Justin]
AND Main.[Pick Up 20]=[What Pick Up?Use Facility List])
OR (Main.DISPATCHER=[Which Dispatcher?Ex Justin]
AND Main.[Pick Up 20]=[What Pick Up?Use Facility List])
OR (Main.DISPATCHER=[Which Dispatcher?Ex Justin]
AND Main.[Drop Off 20]=[What Drop Off?-Use Facility List])
OR (Main.DISPATCHER=[Which Dispatcher?Ex Justin]
AND Main.Shift=[What shift?AM/PM])
OR (Main.DateCall BETWEEN [Enter Start Date:] AND [Enter Ending Date:]
AND Main.Status=[Status: ACCEPTED or DECLINED?])
OR (Main.DateCall BETWEEN [Enter Start Date:] AND [Enter Ending Date:]
AND Main.[Pick Up 20]=[What Pick Up?Use Facility List])
OR (Main.DateCall BETWEEN [Enter Start Date:] AND [Enter Ending Date:]
AND Main.[Drop Off 20]=[What Drop Off?-Use Facility List])
OR (Main.DateCall BETWEEN [Enter Start Date:] AND [Enter Ending Date:]
AND Main.Shift=[What shift?AM/PM])
OR (Main.Status=[Status: ACCEPTED or DECLINED?]
AND Main.[Pick Up 20]=[What Pick Up?Use Facility List])
OR (Main.Status=[Status: ACCEPTED or DECLINED?]
AND Main.[Drop Off 20]=[What Drop Off?-Use Facility List])
OR (Main.Status=[Status: ACCEPTED or DECLINED?]
AND Main.Shift=[What shift?AM/PM])
OR (Main.[Pick Up 20]=[What Pick Up?Use Facility List]
AND Main.[Drop Off 20]=[What Drop Off?-Use Facility List])
OR (Main.[Pick Up 20]=[What Pick Up?Use Facility List]
AND Main.Shift=[What shift?AM/PM]
AND Main.DISPATCHER=[Which Dispatcher?Ex Justin])
OR (Main.DISPATCHER=[Which Dispatcher?Ex Justin]
AND Main.DateCall BETWEEN [Enter Start Date:] AND [Enter Ending Date:]
AND Main.Status=[Status: ACCEPTED or DECLINED?]
AND Main.[Pick Up 20]=[What Pick Up?Use Facility List]
AND Main.Shift=[What shift?AM/PM])
OR (Main.DISPATCHER=[Which Dispatcher?Ex Justin]
AND Main.DateCall BETWEEN [Enter Start Date:] AND [Enter Ending Date:]
AND Main.Status=[Status: ACCEPTED or DECLINED?]
AND Main.[Drop Off 20]=[What Drop Off?-Use Facility List])
OR (Main.DISPATCHER=[Which Dispatcher?Ex Justin]
AND Main.DateCall BETWEEN [Enter Start Date:] AND [Enter Ending Date:]
AND Main.Status=[Status: ACCEPTED or DECLINED?]
AND Main.[Drop Off 20]=[What Drop Off?-Use Facility List]
AND Main.Shift=[What shift?AM/PM])
OR Main.DISPATCHER=[Which Dispatcher?Ex Justin]
AND Main.DateCall BETWEEN [Enter Start Date:] AND [Enter Ending Date:]
AND Main.Status=[Status: ACCEPTED or DECLINED?]
AND Main.Shift=[What shift?AM/PM]
OR (Main.DISPATCHER=[Which Dispatcher?Ex Justin]
AND Main.DateCall BETWEEN [Enter Start Date:] AND [Enter Ending Date:]
AND Main.[Pick Up 20]=[What Pick Up?Use Facility List])
OR (Main.DISPATCHER=[Which Dispatcher?Ex Justin]
AND Main.DateCall BETWEEN [Enter Start Date:] AND [Enter Ending Date:]
AND Main.[Pick Up 20]=[What Pick Up?Use Facility List]
AND Main.[Drop Off 20]=[What Drop Off?-Use Facility List])
OR (Main.DISPATCHER=[Which Dispatcher?Ex Justin]
AND Main.DateCall BETWEEN [Enter Start Date:] AND [Enter Ending Date:]
AND Main.[Pick Up 20]=[What Pick Up?Use Facility List]
AND Main.[Drop Off 20]=[What Drop Off?-Use Facility List]
AND Main.Shift=[What shift?AM/PM])
OR (Main.DISPATCHER=[Which Dispatcher?Ex Justin]
AND Main.DateCall BETWEEN [Enter Start Date:] AND [Enter Ending Date:]
AND Main.[Pick Up 20]=[What Pick Up?Use Facility List]
AND Main.Shift=[What shift?AM/PM])
OR (Main.DISPATCHER=[Which Dispatcher?Ex Justin]
AND Main.DateCall BETWEEN [Enter Start Date:] AND [Enter Ending Date:]
AND Main.[Drop Off 20]=[What Drop Off?-Use Facility List])
OR (Main.DISPATCHER=[Which Dispatcher?Ex Justin]
AND Main.DateCall BETWEEN [Enter Start Date:] AND [Enter Ending Date:]
AND Main.[Drop Off 20]=[What Drop Off?-Use Facility List]
AND Main.Shift=[What shift?AM/PM])
OR (Main.DISPATCHER=[Which Dispatcher?Ex Justin]
AND Main.DateCall BETWEEN [Enter Start Date:] AND [Enter Ending Date:]
AND Main.Shift=[What shift?AM/PM])
OR (Main.DISPATCHER=[Which Dispatcher?Ex Justin]
AND Main.Status=[Status: ACCEPTED or DECLINED?]
AND Main.[Pick Up 20]=[What Pick Up?Use Facility List])
OR (Main.DISPATCHER=[Which Dispatcher?Ex Justin]
AND Main.Status=[Status: ACCEPTED or DECLINED?]
AND Main.[Pick Up 20]=[What Pick Up?Use Facility List]
AND Main.[Drop Off 20]=[What Drop Off?-Use Facility List])
OR (Main.DISPATCHER=[Which Dispatcher?Ex Justin]
AND Main.Status=[Status: ACCEPTED or DECLINED?]
AND Main.[Pick Up 20]=[What Pick Up?Use Facility List]
AND Main.[Drop Off 20]=[What Drop Off?-Use Facility List]
AND Main.Shift=[What shift?AM/PM])
OR (Main.DISPATCHER=[Which Dispatcher?Ex Justin]
AND Main.Status=[Status: ACCEPTED or DECLINED?]
AND Main.[Pick Up 20]=[What Pick Up?Use Facility List]
AND Main.Shift=[What shift?AM/PM])
OR (Main.DISPATCHER=[Which Dispatcher?Ex Justin]
AND Main.Status=[Status: ACCEPTED or DECLINED?]
AND Main.[Drop Off 20]=[What Drop Off?-Use Facility List])
OR (Main.DISPATCHER=[Which Dispatcher?Ex Justin]
AND Main.Status=[Status: ACCEPTED or DECLINED?]
AND Main.[Drop Off 20]=[What Drop Off?-Use Facility List]
AND Main.Shift=[What shift?AM/PM])
OR (Main.DISPATCHER=[Which Dispatcher?Ex Justin]
AND Main.Status=[Status: ACCEPTED or DECLINED?]
AND Main.Shift=[What shift?AM/PM])
OR (Main.DISPATCHER=[Which Dispatcher?Ex Justin]
AND Main.[Pick Up 20]=[What Pick Up?Use Facility List]
AND Main.[Drop Off 20]=[What Drop Off?-Use Facility List])
OR (Main.DISPATCHER=[Which Dispatcher?Ex Justin]
AND Main.[Pick Up 20]=[What Pick Up?Use Facility List]
AND Main.[Drop Off 20]=[What Drop Off?-Use Facility List]
AND Main.Shift=[What shift?AM/PM])
OR (Main.DISPATCHER=[Which Dispatcher?Ex jUSTIN]
AND Main.[Pick Up 20]=[What Pick Up?Use Facility List]
AND Main.Shift=[What shift?AM/PM])
OR (Main.DISPATCHER=[Which Dispatcher?Ex Justin]
AND Main.[Drop Off 20]=[What Drop Off?-Use Facility List]
AND Main.Shift=[What shift?AM/PM])
OR (Main.DateCall BETWEEN [Enter Start Date:] AND [Enter Ending Date:])
OR (Main.DateCall BETWEEN [Enter Start Date:] AND [Enter Ending Date:]
AND Main.Status=[Status: ACCEPTED or DECLINED?]
AND Main.[Pick Up 20]=[What Pick Up?Use Facility List])
OR (Main.DateCall BETWEEN [Enter Start Date:] AND [Enter Ending Date:]
AND Main.Status=[Status: ACCEPTED or DECLINED?]
AND Main.[Pick Up 20]=[What Pick Up?Use Facility List]
AND Main.[Drop Off 20]=[What Drop Off?-Use Facility List])
OR (Main.DateCall BETWEEN [Enter Start Date:] AND [Enter Ending Date:]
AND Main.Status=[Status: ACCEPTED or DECLINED?]
AND Main.[Pick Up 20]=[What Pick Up?Use Facility List]
AND Main.[Drop Off 20]=[What Drop Off?-Use Facility List]
AND Main.Shift=[What shift?AM/PM])
OR (Main.DateCall BETWEEN [Enter Start Date:] AND [Enter Ending Date:]
AND Main.Status=[Status: ACCEPTED or DECLINED?]
AND Main.[Pick Up 20]=[What Pick Up?Use Facility List]
AND Main.Shift=[What shift?AM/PM])
OR (Main.DateCall BETWEEN [Enter Start Date:] AND [Enter Ending Date:]
AND Main.Status=[Status: ACCEPTED or DECLINED?]
AND Main.[Drop Off 20]=[What Drop Off?-Use Facility List])
OR (Main.DateCall BETWEEN [Enter Start Date:] AND [Enter Ending Date:]
AND Main.Status=[Status: ACCEPTED or DECLINED?]
AND Main.[Drop Off 20]=[What Drop Off?-Use Facility List]
AND Main.Shift=[What shift?AM/PM])
OR (Main.DateCall BETWEEN [Enter Start Date:] AND [Enter Ending Date:]
AND Main.Status=[Status: ACCEPTED or DECLINED?]
AND Main.Shift=[What shift?AM/PM])
OR (Main.DateCall BETWEEN [Enter Start Date:] AND [Enter Ending Date:]
AND Main.[Pick Up 20]=[What Pick Up?Use Facility List]
AND Main.[Drop Off 20]=[What Drop Off?-Use Facility List])
OR (Main.DateCall BETWEEN [Enter Start Date:] AND [Enter Ending Date:]
AND Main.[Pick Up 20]=[What Pick Up?Use Facility List]
AND Main.[Drop Off 20]=[What Drop Off?-Use Facility List]
AND Main.Shift=[What shift?AM/PM])
OR (Main.DateCall BETWEEN [Enter Start Date:] AND [Enter Ending Date:]
AND Main.[Pick Up 20]=[What Pick Up?Use Facility List]
AND Main.Shift=[What shift?AM/PM])
OR (Main.DateCall BETWEEN [Enter Start Date:] AND [Enter Ending Date:]
AND Main.[Drop Off 20]=[What Drop Off?-Use Facility List]
AND Main.Shift=[What shift?AM/PM])
OR (Main.Status=[Status: ACCEPTED or DECLINED?])
OR (Main.Status=[Status: ACCEPTED or DECLINED?]
AND Main.[Pick Up 20]=[What Pick Up?Use Facility List]
AND Main.[Drop Off 20]=[What Drop Off?-Use Facility List])
OR (Main.Status=[Status: ACCEPTED or DECLINED?]
AND Main.[Pick Up 20]=[What Pick Up?Use Facility List]
AND Main.[Drop Off 20]=[What Drop Off?-Use Facility List]
AND Main.Shift=[What shift?AM/PM])
OR (Main.Status=[Status: ACCEPTED or DECLINED?]
AND Main.[Pick Up 20]=[What Pick Up?Use Facility List]
AND Main.Shift=[What shift?AM/PM])
OR (Main.Status=[Status: ACCEPTED or DECLINED?]
AND Main.[Drop Off 20]=[What Drop Off?-Use Facility List]
AND Main.Shift=[What shift?AM/PM])
OR (Main.[Pick Up 20]=[What Pick Up?Use Facility List])
OR (Main.[Pick Up 20]=[What Pick Up?Use Facility List]
AND Main.[Drop Off 20]=[What Drop Off?-Use Facility List]
AND Main.Shift=[What shift?AM/PM])
OR (Main.[Drop Off 20]=[What Drop Off?-Use Facility List]
AND Main.Shift=[What shift?AM/PM])
OR (Main.Shift=[What shift?AM/PM]);`
任何帮助/逻辑都将不胜感激。谢谢。
是的，这不是搜索的非常有效的方法。我的意思是，这个：
OR (Main.Status=[Status: ACCEPTED or DECLINED?])
OR (Main.Status=[Status: ACCEPTED or DECLINED?]
AND Main.[Pick Up 20]=[What Pick Up?Use Facility List]
AND Main.[Drop Off 20]=[What Drop Off?-Use Facility List])
在逻辑上等同于此：
OR (Main.Status=[Status: ACCEPTED or DECLINED?])
然后在中间您有很多语句。
[...]
OR Main.DISPATCHER=[Which Dispatcher?Ex Justin]
AND Main.DateCall BETWEEN [Enter Start Date:] AND [Enter Ending Date:]
AND Main.Status=[Status: ACCEPTED or DECLINED?]
AND Main.Shift=[What shift?AM/PM]
OR [...]
这基本上将覆盖您所拥有的所有其他所有内容。所有这些条件都必须满足。
让我们假设，当您不提交字段时，提示的值为null。我们还假设您必须在呼叫日期范围内提交两个日期。要进行完整的搜索，您可以做到这一点：
WHERE (Main.DISPATCHER=[Which Dispatcher?Ex Justin] OR [Which Dispatcher?Ex Justin] IS NULL)
AND (Main.DateCall BETWEEN [Enter Start Date:] AND [Enter Ending Date:] OR [Enter Start Date:] IS NULL OR [Enter Ending Date:] IS NULL)
AND (Main.Status=[Status: ACCEPTED or DECLINED?] OR [Status: ACCEPTED or DECLINED?] IS NULL)
AND (Main.[Pick Up 20]=[What Pick Up?Use Facility List] OR [What Pick Up?Use Facility List] IS NULL)
AND (Main.[Drop Off 20]=[What Drop Off?-Use Facility List] OR [What Drop Off?-Use Facility List] IS NULL)
AND (Main.Shift=[What shift?AM/PM] OR [What shift?AM/PM] IS NULL)