SQL 代码效率和编译问题与 Microsoft Access

Question

有没有办法以更有效的方式设置此代码？我正在尝试过滤几个不同的标准，甚至很难编译代码。所有 IS NULL 的原因是我想让它在没有输入信息的情况下只接受所有数据。

有人对如何优化此代码有任何提示吗？特别是WHERE部分。是否有我可以使用的 if then 语句？还是索引？

SELECT [Table Material Label].Serial, [Table Material Label].[Date Recieved], [Table Material Label].MaterialDescription, [Table Material Label].MaterialCode, [Table Material Label].Supplier, 
[Table Material Label].[Lot Number], [Table Material Label].Weight, [Table Material Label].Quantity, [Table Material Label].[Purchase Order Number], [Table Material Label].[Received By], [Table Material Label].[Checked in By], [Table Material Label].[Total Weight]
FROM [Table Material Label]
WHERE (([Table Material Label].[Date Recieved])>=[Forms]![Report Generator]![Text6] 
AND ([Table Material Label].[Date Recieved])<=[Forms]![Report Generator]![Text7]) 
AND ([Table Material Label].MaterialDescription = [Forms]![Report Generator]![repMaterial] 
OR [Forms]![Report Generator]![repMaterial] IS NULL)
AND ([Table Material Label].MaterialCode = [Forms]![Report Generator]![repItem] 
OR [Forms]![Report Generator]![repItem] IS NULL)
AND ([Table Material Label].[Lot Number] = [Forms]![Report Generator]![repLot] 
OR [Forms]![Report Generator]![repLot] IS NULL)
AND ([Table Material Label].Weight = [Forms]![Report Generator]![repWeight] 
OR [Forms]![Report Generator]![repWeight] IS NULL)
AND ([Table Material Label].Quantity = [Forms]![Report Generator]![repQuantity] 
OR [Forms]![Report Generator]![RepQuantity] IS NULL)
AND ([Table Material Label].[Purchase Order Number] = [Forms]![Report Generator]![repPurchaseOrder] 
OR [Forms]![Report Generator]![repPurchaseOrder] IS NULL)
AND ([Table Material Label].[Received By] = [Forms]![Report Generator]![repRecBy] 
OR [Forms]![Report Generator]![repRecBy] IS NULL)
AND ([Table Material Label].[Checked in By] = [Forms]![Report Generator]![repCheckBy] 
OR [Forms]![Report Generator]![repCheckBy] IS NULL)
AND ([Table Material Label].[Total Weight] = [Forms]![Report Generator]![repTotalWeight] 
OR [Forms]![Report Generator]![repTotalWeight] IS NULL)
AND ([Table Material Label].Supplier = [Forms]![Report Generator]![Supp] 
OR [Forms]![Report Generator]![Supp] IS NULL)

ORDER BY [Table Material Label].[Date Recieved], [Table Material Label].MaterialDescription, [Table Material Label].MaterialCode, [Table Material Label].Supplier;

Answer 1

你的WHERE代码是最简单的，至少我不知道任何更简单的解决方案。每个条件有两个条件，如果需要显示空条件的所有记录，则这是最小条件。此代码只有一个问题 - 您应该以 SQL 文本格式保留查询。如果您切换到查询生成器，它将使完全混乱，几乎不可读。由于SQL文本并不复杂，运行此查询应该没有任何问题，它不会被编译。当然，您应该WHERE子句中的每个字段都有索引，以获得更好的性能。