criteria, checkboxes, and/or

B

bicyclops

I have a matrix of checkboxes on a continuous form.
I would also like to use checkboxes in the header to specify parameter
criteria in the form's query. The rows that appear on the form should match
the criteria checkbox contents. If the box is (triple state) grey, the
criteria should be 'everything'.

This works when using the criteria line for example,

Like [Forms]![FrmAssyStatus2]![CheckSales] & "*"

I need to add about a dozen or so of these criteria, and they should work in
an OR fashion. The grey state of any criteria checkbox would basically mean
that that column is unfiltered. But when I add the criteria to the query
matrix as OR's, all records are displayed no matter what checkboxes are on.

I'm guessing the & "*" parts of the criteria are messing this up, but I
don't know any other way to work it. I'm sure there must be another way.

Thanks in advance.
 
J

John Vinson

I have a matrix of checkboxes on a continuous form.
I would also like to use checkboxes in the header to specify parameter
criteria in the form's query. The rows that appear on the form should match
the criteria checkbox contents. If the box is (triple state) grey, the
criteria should be 'everything'.
Click to expand...

Try using criteria such as

([Fieldname] = [Forms]![formname]![chkbox] OR
[Forms]![formname]![chkbox] IS NULL)

instead.

John W. Vinson[MVP]
 
B

bicyclops

John- I'm guessing the syntax you gave was SQL; I was using the query builder
so I omitted the [fieldname]= part of the statement.

Anyway this works fine when used in an AND fashion (criteria for all fields
on one line) but I need to use it in OR fashion (criteria cascasded downward
on successive lines), and then it only works in certain situations.

For example, when the 2nd field is the only field turned on, all records are
returned instead of just records for the 2nd field.

It seems like it should be a simple solution & that we're close, but it
turns out to be elusive. Any further input is appreciated.

John Vinson said:
I have a matrix of checkboxes on a continuous form.
I would also like to use checkboxes in the header to specify parameter
criteria in the form's query. The rows that appear on the form should match
the criteria checkbox contents. If the box is (triple state) grey, the
criteria should be 'everything'.
Click to expand...

Try using criteria such as

([Fieldname] = [Forms]![formname]![chkbox] OR
[Forms]![formname]![chkbox] IS NULL)

instead.

John W. Vinson[MVP]
Click to expand...
 
J

John Vinson

Anyway this works fine when used in an AND fashion (criteria for all fields
on one line) but I need to use it in OR fashion (criteria cascasded downward
on successive lines), and then it only works in certain situations.

For example, when the 2nd field is the only field turned on, all records are
returned instead of just records for the 2nd field.
Click to expand...

It sounds like you have a criterion or criteria on some other fields.
If so you must put that criterion on ALL of the OR lines.

It might help if you opened your query in SQL view and posted the SQL
text here. There's clearly something amiss with the query; if I could
see the SQL I might be able to figure out what!

John W. Vinson[MVP]
 
B

bicyclops

John- I originally had other criteria, but have gone to a nested query. The
other query takes care of the initial criteria, then this query would handle
the checkbox criteria. I'll paste both below.

Here is the query we've been talking about:
SELECT test1.OrderID, test1.DetailsKeyID, test1.SubQty,
test1.AssyShipmentID, test1.PPComplete, test1.WorkTypeID, test1.StatusID,
TblAssyShipments.PPSales, TblAssyShipments.[PPP&V],
TblAssyShipments.PPKitInv, TblAssyShipments.PPCCam,
TblAssyShipments.PPMydataQueue, TblAssyShipments.PPMydataProcess,
TblAssyShipments.PPManualQueue, TblAssyShipments.PPManualProcess,
TblAssyShipments.PPAOI, TblAssyShipments.PPSMTInspec,
TblAssyShipments.PPVCDQueue, TblAssyShipments.PPVCDProcess,
TblAssyShipments.PPRework, TblAssyShipments.PPElecTest,
TblAssyShipments.PPFinalInspec, TblAssyShipments.PPPfitDepan,
TblAssyShipments.PPShipping
FROM test1 INNER JOIN TblAssyShipments ON test1.AssyShipmentID =
TblAssyShipments.AssyShipmentID
WHERE (((TblAssyShipments.PPSales)=[Forms]![FrmAssyStatus2]![CheckSales] Or
[Forms]![FrmAssyStatus2]![CheckSales] Is Null)) OR
(((TblAssyShipments.[PPP&V])=[Forms]![FrmAssyStatus2]![CheckPPPV] Or
[Forms]![FrmAssyStatus2]![CheckPPPV] Is Null));

Here is the other query referred to in the above:
SELECT TblOrders.OrderID, TblOrderDetails.DetailsKeyID,
TblAssyShipments.SubQty, TblAssyShipments.AssyShipmentID,
TblAssyShipments.PPComplete, TblWorkActions.WorkTypeID, TblOrders.StatusID
FROM TblWorkActions INNER JOIN (TblOrders INNER JOIN (TblOrderDetails INNER
JOIN TblAssyShipments ON TblOrderDetails.DetailsKeyID =
TblAssyShipments.OrderDetailID) ON TblOrders.OrderID =
TblOrderDetails.[Order#ID]) ON TblWorkActions.ActionID =
TblOrderDetails.ActionID
WHERE (((TblAssyShipments.PPComplete)=False) AND
((TblWorkActions.WorkTypeID)=1) AND ((TblOrders.StatusID)=1));

Thanks again for the help.
 
Top