mysql - SQL code efficiency & compiling issues with Microsoft Access

Question

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.

Anyone got any tips for how I can optimize this code? Specifically the WHERE section. Is there a if then statement that I could use? Or an Index?

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

Your code for WHERE is simplest, at least I don't know any simpler solutions. You have two conditions per each criteria, this is minimum if you need to show all records for empty criteria. Only one problem with this code - you should keep the query in SQL text format. If you switch to query builder, it will make total mess, almost unreadable. As SQL text it's not complicated, you should not have any problems with running this query, it won't be compiled. Of course you should have indexes for each field in WHERE clause for better performance.