Returning correct rows in a join/nested query

[goneaccessing]

First off I have two tables that are set up similiar to what you see below (of course different columns and data):




Next I have a form that allows you to search by an Order date:


And now what I want the query to return is the following: If the date matches ANY of the OrderDate values in the Orders table then it will spit back the Customer information (Customer.*) who made that order and ALL other rows for that particular customer in the Orders table. Perhaps most importantly I can't pass the name of the customer or a date range or anything else, I want to do this using just the date variable from the form so no hard coding to fix the issue.

My current query more or less looks like this:
SELECT Customer.*, Orders.*
FROM Customer LEFT JOIN Orders ON Customer.CustomerID = Orders.CustomerID
WHERE Orders.OrderDate=[Forms]![DateSearch].[OrderDateInputBox];

I have a report that builds out fine but it will only spit back the matching customer(s) from the Customers table and (here is the problem) ONLY the MATCHING rows from the Orders table. Make sense?

THANKS FOR ANY HELP YOU CAN PROVIDE

[pbaldy]

Shot in the dark, but try

WHERE Orders.CustomerID IN(SELECT CustomerID FROM Orders WHERE OrderDate = [Forms]![DateSearch].[OrderDateInputBox])

[goneaccessing]

Shot in the dark, but try

WHERE Orders.CustomerID IN(SELECT CustomerID FROM Orders WHERE OrderDate = [Forms]![DateSearch].[OrderDateInputBox])

You Sir are a fine American and SQL Hero. That worked. I knew it would be much easier once it was solved. Thank you again!

[pbaldy]

Glad it helped. It occurs to me that you could also make the subquery a stand-alone query, and then join to it instead of using it in the WHERE clause. It would probably be more efficient if there are a lot of records.

[pbaldy]

Oh, and welcome to the site by the way!

[goneaccessing]

Oh, and welcome to the site by the way!

Thanks very much... I hope that this thread helps others in need and that I can continue to rely on all the knowledge here, its been great so far.