Hi.

I have the following SQL in qryJobsLinks.

I want to find the records in various tables that contain the value in the Param SearchKey.  I then want records returned from tblJobs that are linked to these records.

SELECT DISTINCT
 tblJobs.JobsAutoInc,
 tblJobs.ManagerName,
 tblJobs.FilePath,
 tblJobs.DateIn
FROM tblJobs
LEFT JOIN tblJobClient ON (tblJobs.JobsAutoInc = tblJobClient.MainAutoInc)
LEFT JOIN tblJobArchitect ON (tblJobs.JobsAutoInc = tblJobArchitect.MainAutoInc)
LEFT JOIN tblJobBCO ON (tblJobs.JobsAutoInc = tblJobBCO.MainAutoInc)
LEFT JOIN tblJobCO ON (tblJobs.JobsAutoInc = tblJobCO.MainAutoInc)
LEFT JOIN tblJobContacts ON (tblJobs.JobsAutoInc = tblJobContacts.MainAutoInc)
LEFT JOIN tblJobContractor ON (tblJobs.JobsAutoInc = tblJobContractor.MainAutoInc)
LEFT JOIN tblJobEngineer ON (tblJobs.JobsAutoInc = tblJobEngineer.MainAutoInc)
LEFT JOIN tblJobHighways ON (tblJobs.JobsAutoInc = tblJobHighways.MainAutoInc)
LEFT JOIN tblJobPlanner ON (tblJobs.JobsAutoInc = tblJobPlanner.MainAutoInc)
LEFT JOIN tblJobQS ON (tblJobs.JobsAutoInc = tblJobQS.MainAutoInc)
WHERE
 tblJobClient.GroupAutoInc =:SearchKey
 OR tblJobArchitect.GroupAutoInc =:SearchKey
 OR tblJobBCO.GroupAutoInc =:SearchKey
 OR tblJobCO.GroupAutoInc =:SearchKey
 OR tblJobContacts.ContactsAutoInc =:SearchKey
 OR tblJobContractor.GroupAutoInc =:SearchKey
 OR tblJobEngineer.GroupAutoInc =:SearchKey
 OR tblJobHighways.GroupAutoInc =:SearchKey
 OR tblJobPlanner.GroupAutoInc =:SearchKey
 OR tblJobQS.GroupAutoInc =:SearchKey

I create the Param SearchKey and allocate the value to it. I have checked this value and it is as expected. Yet every time I get every record from tblJobs returned, which is not correct.

If I change the end of the last 10 lines to   ....... = 5 then I get the correct records returned.

I'm creating the param SearchKey as follows.

 frmContactJobLink.qryJobsLinks.Params.Clear;
 frmContactJobLink.qryJobsLinks.Params.CreateParam(ftInteger, 'SearchKey');
 frmContactJobLink.qryJobsLinks.ParamByName('SearchKey').AsInteger := SearchKey;
 frmContactJobLink.qryJobsLinks.Open;

If I check the query in dbsys it always returns the correct records but of course I'm not using a param, I have to enter a value.

Am I doing something wrong here?  I guess I could create the SQL and allocate the value in code and it should then work.

Thanks.

I think I've managed to sort this thanks.

It appears that the SQL creates 9 parameters all called SearchKey.  I assumed the same one parameter would be used each time.

If I go through the params from 0 to params.count -1 and allocate the same search value to all of them the query will then work.

Thanks.

Robert,

<< I'm creating the param SearchKey as follows. >>

There's no need to do that.  Just use this:

 frmContactJobLink.qryJobsLinks.Prepare;
 frmContactJobLink.qryJobsLinks.ParamByName('SearchKey').AsInteger :=
SearchKey;
 frmContactJobLink.qryJobsLinks.Open;

--
Tim Young
Elevate Software
www.elevatesoft.com