Query Subform Driving Me Baty

[dynamictiger]

This is driving me batty, but very difficult to describe, so I apologise in advance for the length of this post.

I have a table called tblPoolParameters. This table has the following fields:
Construction
Sanitiser
pHControl
Parameter
TargetValue
UpperLimit
LowerLimit
ReportType

Of these Contruction, sanitizer, pHcontrol and report type are all lookup fields on foreign tables.

For each of the lookup fields I have added the word All using the following method to the combo box.

UNION
SELECT 0,”ALL”
FROM tblwhatever

This has added the ability to specify a parameter to apply to a specific construction type or all construction types, for example.

Following all this I constructed a form for the user. This includes the ability to step through each parameter and add the appropriate target, upper and lower values, as required.

The user may elect to select ALL or, say, a specific construction type, report or so on.

Finally I added a subform that is NOT LINKED to the main form. Rather it is query driven with the following SQL.

SELECT tblPoolParameters.ParameterID, tblPoolParameters.Construction, tblPoolParameters.Sanitiser, tblPoolParameters.pHControl, tblPoolParameters.TestParameter, tblPoolParameters.TargetValue, tblPoolParameters.UpperLimit, tblPoolParameters.LowerLimit, tblPoolParameters.ReportType
FROM tblPoolParameters
WHERE (((tblPoolParameters.Construction)=0) AND ((tblPoolParameters.Sanitiser)=0) AND ((tblPoolParameters.pHControl)=0) AND ((tblPoolParameters.ReportType)=0)) OR (((tblPoolParameters.Construction) Like [Forms]![frmPoolParameters]![Construction] & "*") AND ((tblPoolParameters.Sanitiser) Like [Forms]![frmPoolParameters]![Sanitiser] & "*") AND ((tblPoolParameters.pHControl) Like [Forms]![frmPoolParameters]![pHControl] & "*") AND ((tblPoolParameters.ReportType) Like [Forms]![frmPoolParameters]![ReportType] & "*"))
WITH OWNERACCESS OPTION;

However, this is no achieving the desired outcome. To be more specific if I set up two examples:

Example1 Example2 Example3 Example4 Example5
Construction ALL ALL Concrete Concrete Concrete
Sanitiser ALL ALL Liquid Liquid Liquid
pHControl ALL ALL ALL ALL All
ReportType ALL ALL Standard ALL Stain

The subform returns example1 and example 2 if we are focused on either of the master forms for these examples. This is correct.

Example3 subform shows Example1, Example2 and Example 3 but not Example4 this is incorrect. As Example4 is the same construction and sanitizer it should be returned as well.

Likewise Example4 subform returns Example1 and Example2 but not Example3 this again is incorrect for the reasons sited above.

The reporttype is the determining factor as to whether the values should show or not. A standard reports parameters would include all records with the word ALL in the reporttype and any with Standard as the criteria as well.

Not being overly familiar with SQL I do not know if I need to replace the AND’s in the statement with OR, or if I need to fabricate a union query or???

 

[Pat Hartman]

Your conditions are incorrect. You need to change the where clause to follow the following form:

(Forms!frmPoolParameters!FieldName = 0 OR TableName.FieldName = Forms!frmPoolParameters!FieldName) AND ....

The parentheses are critical as are the usage of OR for the conditions within a set of parens and AND to connect the sets of contitions.