Hello Access Colleagues and hope your Holidays are going well?
I hope one of y'all can provide some insight here because I'm completely flummoxed. I've pasted two queries below (fields, etc. removed for simplification and privacy)
The only difference is that the first (working0 one uses an inner join and the second (non-working) one uses a left join.
SELECT *
FROM Query1 INNER JOIN Query2 ON Query1.CustomerID = Query2.CustomerID
WHERE Query2.WorkOrderID = [forms]![frmHiddenCriteria]![txtCustomerID]
SELECT *
FROM Query1 LEFT JOIN Query2 ON Query1.CustomerID = Query2.CustomerID
WHERE (((Query2.WorkOrderID)=[forms]![frmHiddenCriteria]![txtCustomerID]))
The first query works but doesn't give the desired result. The second query gives me an ODBC--Call error. I thought maybe the issue is that I'm using two queries to form a third query which I know is not good practice, but with this table design, doing this through tables only will require a LOT of tables and I'm going to get that dreaded "ambiguous outer join" error. I swear I've used two small queries to join to a larger query many times before and not had this happen.
So the bottom line... Why would I be getting an ODBC call error or not only dependent on the type of join? This makes no sense to me.
This is on an Access front end to a SQL Server database if it helps.