Microsoft Access/SQL Mistaken Parameter Box

Question

I am working in MS Access 2010 to be able to run my own queries/reports using the data we store in a proprietary database. I have this query written as two subqueries, each of which runs perfectly on its own. BUT, when I run them together, I get a Parameter Entry box saying that I need to enter a parameter for Query4.company_id. I double checked for misspellings, so that does not seem to be the case. I think there must be a problem in the way my subquery is connected to my main query. I'm very much a newbie, but excited to get any advice you may have! This is my query:

SELECT dbo_Companies.company_id, dbo_Companies.company_name, dbo_Lease.lease_from_dt, dbo_Lease.lease_to_dt, dbo_Lease.lease_status, dbo_Lease.lease_description, dbo_Companies.company_status

FROM dbo_Companies LEFT JOIN dbo_Lease ON dbo_Companies.company_id = dbo_Lease.company_id

WHERE (((dbo_Companies.company_id) IN

(SELECT dbo_Companies.company_id

FROM (dbo_Companies INNER JOIN dbo_CompanyFacilities ON dbo_Companies.company_id = dbo_CompanyFacilities.company_id) INNER JOIN dbo_Facilities ON dbo_CompanyFacilities.facility_id = dbo_Facilities.facility_id

WHERE (((dbo_CompanyFacilities.facility_id)=11 Or (dbo_CompanyFacilities.facility_id)=13 Or (dbo_CompanyFacilities.facility_id)=14 Or (dbo_CompanyFacilities.facility_id)=15 Or (dbo_CompanyFacilities.facility_id)=21 Or (dbo_CompanyFacilities.facility_id)=28 Or (dbo_CompanyFacilities.facility_id)=24 Or (dbo_CompanyFacilities.facility_id)=27 Or (dbo_CompanyFacilities.facility_id)=31 Or (dbo_CompanyFacilities.facility_id)=32 Or (dbo_CompanyFacilities.facility_id)=33 Or (dbo_CompanyFacilities.facility_id)=37) AND (dbo_Companies.company_status = "Active") AND (((dbo_Companies.company_class_id)=1) OR ((dbo_Companies.company_class_id)= 14))))));

Johnny Bones Johnny Bones · Accepted Answer · 2014-05-05T17:22:32

Try this:

SELECT 
  C.company_id, 
  C.company_name, 
  L.lease_from_dt, 
  L.lease_to_dt, 
  L.lease_status, 
  L.lease_description, 
  C.company_status

FROM dbo_Companies C 

LEFT JOIN dbo_Lease L 
  ON C.company_id = L.company_id

WHERE C.company_id IN

(SELECT C2.company_id

FROM dbo_Companies C2 
INNER JOIN dbo_CompanyFacilities CF 
  ON C2.company_id = CF.company_id 
INNER JOIN dbo_Facilities F 
  ON CF.facility_id = F.facility_id

WHERE 
  CF.facility_id IN (11, 13, 14, 15, 21, 28, 24, 27, 31, 32, 33, 37) 
  AND C2.company_status = "Active" 
  AND C2.company_class_id IN (1, 14)
);

To test this, run the second SELECT statement first (the one inside the parens) and see if you get any results. If so, run the entire code block and see if you still get that error. Without knowing anything about the data, it looks like it should run fine.

Microsoft Access/SQL Mistaken Parameter Box

1 Answers