If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
criteria, checkboxes, and/or
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. |
#2
|
|||
|
|||
On Mon, 12 Sep 2005 11:13:03 -0700, bicyclops
wrote: 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'. Try using criteria such as ([Fieldname] = [Forms]![formname]![chkbox] OR [Forms]![formname]![chkbox] IS NULL) instead. John W. Vinson[MVP] |
#3
|
|||
|
|||
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" wrote: On Mon, 12 Sep 2005 11:13:03 -0700, bicyclops wrote: 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'. Try using criteria such as ([Fieldname] = [Forms]![formname]![chkbox] OR [Forms]![formname]![chkbox] IS NULL) instead. John W. Vinson[MVP] |
#4
|
|||
|
|||
On Tue, 13 Sep 2005 08:02:46 -0700, bicyclops
wrote: 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 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] |
#5
|
|||
|
|||
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. "John Vinson" wrote: On Tue, 13 Sep 2005 08:02:46 -0700, bicyclops wrote: 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 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] |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
sorting more than 3 keys | Brooke | General Discussion | 3 | June 18th, 2005 04:52 AM |
dlookup documentation? | Fredrated | New Users | 1 | May 19th, 2005 11:10 PM |
Query By Form Criteria Problem(s),,maybe AND/OR issue | FerryMary | Running & Setting Up Queries | 1 | May 16th, 2005 07:47 PM |
How to Build Two Queries From One List Box | [email protected] | General Discussion | 0 | February 8th, 2005 04:24 PM |
checkboxes on forms that search criteria | Jon E | Using Forms | 1 | August 11th, 2004 05:48 AM |