Cannot Run Multi-table Parameter Query with criteria specified in form!

[tim_tims33]

Hi,



I've created a farm database, with a form (frmSearch) that will allow user to filter data. The form comprises of combo box and list box etc... for the user to input their own criteria. The subform below has a datasource. The data source is based on a query (qContractionSearch) which is basically a parameter query with 3 tables. The problem is, however, that it won't work with 3 tables... but will work if data source comprises just one table.

See the farm database attachment...and go to frmSearch...then go to Contraction tab. (The Cattle tab filter works fine-it only has a single-table datasource).

Note: i have a requery macro which runs whenever the user clicks 'search'.

Any help with this would be appreciated a lot. Very frustrating to get filter 'Contraction' data to work.

many thanks

t

[aytee111]

This query only checks illness and breed, you will need to add the other criteria.

Copy and paste it into a query and note the following: on the criteria line each one listed is an "and", and the second query line is an "or". So for each of the criteria listed on the form any or all can be blank or can have a value. You will need a criteria line for each of the possibilities where a criteria may be blank or not where all of them are mentioned.

SELECT tblCONTRACTION.TagID, tblCONTRACTION.IllnessID, tblILLNESS.IllnessDescription, tblCOW.Breed, [forms]![frmsearch]![cboillness] AS Expr1, [Forms]![frmSearch]![cboBreedContraction] AS Expr2
FROM tblILLNESS INNER JOIN (tblCOW INNER JOIN tblCONTRACTION ON tblCOW.TagID = tblCONTRACTION.TagID) ON tblILLNESS.IllnessID = tblCONTRACTION.IllnessID
WHERE (((tblILLNESS.IllnessDescription)=[forms]![frmsearch]![cboillness]) AND ((tblCOW.Breed)=[Forms]![frmSearch]![cboBreedContraction]) AND (([forms]![frmsearch]![cboillness]) Is Not Null) AND (([Forms]![frmSearch]![cboBreedContraction]) Is Not Null)) OR (((tblILLNESS.IllnessDescription)=[forms]![frmsearch]![cboillness]) AND (([forms]![frmsearch]![cboillness]) Is Not Null) AND (([Forms]![frmSearch]![cboBreedContraction]) Is Null)) OR (((tblCOW.Breed)=[Forms]![frmSearch]![cboBreedContraction]) AND (([forms]![frmsearch]![cboillness]) Is Null) AND (([Forms]![frmSearch]![cboBreedContraction]) Is Not Null));

[tim_tims33]

Thank you for your response. However, the SQL you provide is not a solution. If you carefully look at the database, frmSearch, and then go to the 'Contraction Tab' you will see that other controls exist (Cured? and Further Treatment?) that allow the user further refinement. Your solution does not take these controls into account...and this is where the problem lies.

Have been spending too much time on this, as i now think it's not possible?

Thanks

[aytee111]

Please read my first statement where it says that this is only a partial list of criteria, you will have to add any others you may need. You can use this SQL statement to see how to do it - more lines of criteria need to be added.