I have a form that has a combo box called [Supp] that draws from a table column called [Supplier].
At the same time I am filtering the date associated with the record with a date range [Text6](Starting Date) and [Text7](Ending Date)
I'm trying to make the Supplier filter optional by making it select all Suppliers whenever you leave [Supp] Blank/Null.
The issue is that whenever I leave [Supp] blank, it seems surpass the Date Range filter and selects all avaliable records regardless of date. I think its most likely because I'm using "OR".
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].Supplier)=IIf([forms]![Report Generator]![Supp].[Value]="*",([Table Material Label].[Supplier])='*',([Table Material Label].[Supplier])=[Forms]![Report Generator]![Supp].[Value])))
ORDER BY [Table Material Label].[Date Recieved], [Table Material Label].MaterialDescription, [Table Material Label].MaterialCode, [Table Material Label].Supplier;
To summarize - I'm trying to make a query that filters by both a date range and by supplier. But if the supplier criteria is blank I want it to not filter suppliers. I'm not able to do this without breaking the date range filter.
Any help would be greatly appreciated. Thank you.
Finished Product, thanks to Andre's solution :
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].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;
