I have a query that searches saved records and creates a report based on the record(s). Some of the fields are searchable either independently or in association with other parts of the saved record (e.g., one could search the ID, location, and/or whether or not police were notified). However, I run into problems when searching by date.
I have fields for the user to input Start Date and End Date of their desired date range. When ONE or NEITHER field are filled, the search pulls up all records AFTER the Start Date, BEFORE the end date, or ALL the records. When BOTH fields are filled, the search pulls up a record where all fields are blank (which does not exist in the table).
Each searchable field uses the same criteria in the Query:
Like Nz([field that you're searching],"*")
But the date range uses a modified version (sorry if it's SUPER clunky):
Like Nz(([Data_Input_Table].[Day_Current])>=[Forms]![Search_Form]![Start_Date_Lookup_text] And ([Data_Input_Table].[Day_Current])<=[Forms]![Search_Form]![End_Date_Lookup_text],"*")
Ideally, I'd like the user to search by NEITHER, ONE, or BOTH Start Date and End Date.
Please help!
You can't use Like on dates. Try this:
[Data_Input_Table].[Day_Current] >= Nz([Forms]![Search_Form]![Start_Date_Lookup_text], [Data_Input_Table].[Day_Current]) And [Data_Input_Table].[Day_Current] <= Nz([Forms]![Search_Form]![End_Date_Lookup_text], [Data_Input_Table].[Day_Current])