title: SQL code efficiency & compiling issues with Microsoft Access
body: <p>Is there a way to setup this code in a more efficient way? I'm trying to filter through several different criteria and I'm having a hard time even compiling the code. Also the reason for all the IS NULL is that I want to make it so that if no information is inputted, for it to just accept all data.</p>
  
  <p>Anyone got any tips for how I can optimize this code? Specifically the <strong>WHERE</strong> section. Is there a if then statement that I could use? Or an Index?</p>
  
  <pre><code>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])&gt;=[Forms]![Report Generator]![Text6] 
      AND ([Table Material Label].[Date Recieved])&lt;=[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;
  </code></pre>