MS Access date range search leaving out records that should be within date range

Question

I have a lookup form with text boxes for commodity, start date and end date

The commodity section has criteria here

    `[Forms]![Traceability_F]![Material]`       

also an OR criteria for if the text box is blank

    `[Forms]![Traceability_F]![Material] is null`       

The date range criteria is

    `Between [Forms]![Traceability_F]![StartDate] And [Forms]![Traceability_F]![EndDate]`    

also an OR criteria for if date range is blank

    `([Inventory Usage].[Date Used]) Between [Forms]![Traceability_F]![StartDate] And [Forms]![Traceability_F]![EndDate]`         

When the search button is clicked and nothing is entered into the text boxes the query opens with all records. This is what I wanted

When the search button is clicked and nothing is in the commodity text box the query shows up blank. Not what I wanted. I was trying to get it to show all records for that date range

When commodity and date range is inputted the query shows the commodity but the dates of the commodity are sometimes not in the date range or the query omits records that should be within the date range. For example the table that is being pulled from has records from 9/26/22-10/14/22 when those dates are inputted a few records in that range do not show up. Records from 10/11,10/13 do not show up.

Clearly something is wrong with the date range function of my form. I would like to be able to enter a date range and see all records entered in that date range. Also when a commodity is entered along with the date range I want to be able to see all records of that specific commodity in that date range.

The commodity section has criteria shown in image 3. Having trouble entering it in this section.

Any help would be greatly appreciated. I have attached an image of my form and can add in the SQL information but I am new to SQL.

Thanks, Kyle

    [search form][1]
    [SQL for Query][2]
    [Query Design][3]



   [1]: https://i.stack.imgur.com/4WdsY.png
   [2]: https://i.stack.imgur.com/a82rx.png
   [3]: https://i.stack.imgur.com/P6Bkg.png

Answer 1

Try to declare the form fields as DateTime in the query SQL:

Parameters
    [Forms]![Traceability_F]![StartDate] DateTime,
    [Forms]![Traceability_F]![EndDate] DateTime;
Select ... <snip>