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
|
|||
|
|||
Return All Record if Check Box is Blank
How would I return a subset of records if a form check box is checked but
return all records if the check box is blank. The formcheck box is: [Forms]![frm_switchboard]![chk_rndm] The field [RandomMarker] can have the values of "Y" or Null only. The following SQL pulls the correct data when the check box is checked, but I recive an "OpenForm action was canceled" when the box is blank. How would I modify the query to pull all results ([RandomMarker]="Y" or Null) when the box is not checked? SELECT tbl_tt_trans.TransNo, tbl_tt_trans.Fac, tbl_tt_trans.CODE, tbl_tt_trans.RandomMarker, tbl_tt_trans.UseDT, tbl_tt_trans.TransNo FROM tbl_tt_trans WHERE (((tbl_tt_trans.CODE)=[Forms]![frm_switchboard]![swCode]) AND ((tbl_tt_trans.RandomMarker)=[Forms]![frm_switchboard]![chk_rndm]) AND ((tbl_tt_trans.PtType) Not Like "O")) ORDER BY tbl_tt_trans.UseDT; thanks for the help |
#2
|
|||
|
|||
Return All Record if Check Box is Blank
SELECT tbl_tt_trans.TransNo, tbl_tt_trans.Fac, tbl_tt_trans.CODE,
tbl_tt_trans.RandomMarker, tbl_tt_trans.UseDT, tbl_tt_trans.TransNo FROM tbl_tt_trans WHERE tbl_tt_trans.CODE)=[Forms]![frm_switchboard]![swCode] AND (tbl_tt_trans.RandomMarker=IIF([Forms]![frm_switchboard]![chk_rndm],"Y",Null) OR [Forms]![frm_switchboard]![chk_rndm] = False) AND tbl_tt_trans.PtType) "O" ORDER BY tbl_tt_trans.UseDT; John Spencer Access MVP 2002-2005, 2007-2009 The Hilltop Institute University of Maryland Baltimore County yator wrote: How would I return a subset of records if a form check box is checked but return all records if the check box is blank. The formcheck box is: [Forms]![frm_switchboard]![chk_rndm] The field [RandomMarker] can have the values of "Y" or Null only. The following SQL pulls the correct data when the check box is checked, but I recive an "OpenForm action was canceled" when the box is blank. How would I modify the query to pull all results ([RandomMarker]="Y" or Null) when the box is not checked? SELECT tbl_tt_trans.TransNo, tbl_tt_trans.Fac, tbl_tt_trans.CODE, tbl_tt_trans.RandomMarker, tbl_tt_trans.UseDT, tbl_tt_trans.TransNo FROM tbl_tt_trans WHERE (((tbl_tt_trans.CODE)=[Forms]![frm_switchboard]![swCode]) AND ((tbl_tt_trans.RandomMarker)=[Forms]![frm_switchboard]![chk_rndm]) AND ((tbl_tt_trans.PtType) Not Like "O")) ORDER BY tbl_tt_trans.UseDT; thanks for the help |
#3
|
|||
|
|||
Return All Record if Check Box is Blank
SELECT tbl_tt_trans.TransNo, tbl_tt_trans.Fac, tbl_tt_trans.CODE,
tbl_tt_trans.RandomMarker, tbl_tt_trans.UseDT, tbl_tt_trans.TransNo FROM tbl_tt_trans WHERE tbl_tt_trans.CODE)=[Forms]![frm_switchboard]![swCode] AND (tbl_tt_trans.RandomMarker=IIF([Forms]![frm_switchboard]![chk_rndm],"Y",Null) OR [Forms]![frm_switchboard]![chk_rndm] = False) AND tbl_tt_trans.PtType) "O" ORDER BY tbl_tt_trans.UseDT; John Spencer Access MVP 2002-2005, 2007-2009 The Hilltop Institute University of Maryland Baltimore County yator wrote: How would I return a subset of records if a form check box is checked but return all records if the check box is blank. The formcheck box is: [Forms]![frm_switchboard]![chk_rndm] The field [RandomMarker] can have the values of "Y" or Null only. The following SQL pulls the correct data when the check box is checked, but I recive an "OpenForm action was canceled" when the box is blank. How would I modify the query to pull all results ([RandomMarker]="Y" or Null) when the box is not checked? SELECT tbl_tt_trans.TransNo, tbl_tt_trans.Fac, tbl_tt_trans.CODE, tbl_tt_trans.RandomMarker, tbl_tt_trans.UseDT, tbl_tt_trans.TransNo FROM tbl_tt_trans WHERE (((tbl_tt_trans.CODE)=[Forms]![frm_switchboard]![swCode]) AND ((tbl_tt_trans.RandomMarker)=[Forms]![frm_switchboard]![chk_rndm]) AND ((tbl_tt_trans.PtType) Not Like "O")) ORDER BY tbl_tt_trans.UseDT; thanks for the help |
#4
|
|||
|
|||
Return All Record if Check Box is Blank
SELECT tbl_tt_trans.TransNo, tbl_tt_trans.Fac, tbl_tt_trans.CODE,
tbl_tt_trans.RandomMarker, tbl_tt_trans.UseDT, tbl_tt_trans.TransNo FROM tbl_tt_trans WHERE tbl_tt_trans.CODE)=[Forms]![frm_switchboard]![swCode] AND (tbl_tt_trans.RandomMarker=IIF([Forms]![frm_switchboard]![chk_rndm],"Y",Null) OR [Forms]![frm_switchboard]![chk_rndm] = False) AND tbl_tt_trans.PtType) "O" ORDER BY tbl_tt_trans.UseDT; John Spencer Access MVP 2002-2005, 2007-2009 The Hilltop Institute University of Maryland Baltimore County yator wrote: How would I return a subset of records if a form check box is checked but return all records if the check box is blank. The formcheck box is: [Forms]![frm_switchboard]![chk_rndm] The field [RandomMarker] can have the values of "Y" or Null only. The following SQL pulls the correct data when the check box is checked, but I recive an "OpenForm action was canceled" when the box is blank. How would I modify the query to pull all results ([RandomMarker]="Y" or Null) when the box is not checked? SELECT tbl_tt_trans.TransNo, tbl_tt_trans.Fac, tbl_tt_trans.CODE, tbl_tt_trans.RandomMarker, tbl_tt_trans.UseDT, tbl_tt_trans.TransNo FROM tbl_tt_trans WHERE (((tbl_tt_trans.CODE)=[Forms]![frm_switchboard]![swCode]) AND ((tbl_tt_trans.RandomMarker)=[Forms]![frm_switchboard]![chk_rndm]) AND ((tbl_tt_trans.PtType) Not Like "O")) ORDER BY tbl_tt_trans.UseDT; thanks for the help |
#5
|
|||
|
|||
Return All Record if Check Box is Blank
Thanks John. works great!
"John Spencer" wrote: SELECT tbl_tt_trans.TransNo, tbl_tt_trans.Fac, tbl_tt_trans.CODE, tbl_tt_trans.RandomMarker, tbl_tt_trans.UseDT, tbl_tt_trans.TransNo FROM tbl_tt_trans WHERE tbl_tt_trans.CODE)=[Forms]![frm_switchboard]![swCode] AND (tbl_tt_trans.RandomMarker=IIF([Forms]![frm_switchboard]![chk_rndm],"Y",Null) OR [Forms]![frm_switchboard]![chk_rndm] = False) AND tbl_tt_trans.PtType) "O" ORDER BY tbl_tt_trans.UseDT; John Spencer Access MVP 2002-2005, 2007-2009 The Hilltop Institute University of Maryland Baltimore County yator wrote: How would I return a subset of records if a form check box is checked but return all records if the check box is blank. The formcheck box is: [Forms]![frm_switchboard]![chk_rndm] The field [RandomMarker] can have the values of "Y" or Null only. The following SQL pulls the correct data when the check box is checked, but I recive an "OpenForm action was canceled" when the box is blank. How would I modify the query to pull all results ([RandomMarker]="Y" or Null) when the box is not checked? SELECT tbl_tt_trans.TransNo, tbl_tt_trans.Fac, tbl_tt_trans.CODE, tbl_tt_trans.RandomMarker, tbl_tt_trans.UseDT, tbl_tt_trans.TransNo FROM tbl_tt_trans WHERE (((tbl_tt_trans.CODE)=[Forms]![frm_switchboard]![swCode]) AND ((tbl_tt_trans.RandomMarker)=[Forms]![frm_switchboard]![chk_rndm]) AND ((tbl_tt_trans.PtType) Not Like "O")) ORDER BY tbl_tt_trans.UseDT; thanks for the help . |
#6
|
|||
|
|||
Return All Record if Check Box is Blank
Thanks John. works great!
"John Spencer" wrote: SELECT tbl_tt_trans.TransNo, tbl_tt_trans.Fac, tbl_tt_trans.CODE, tbl_tt_trans.RandomMarker, tbl_tt_trans.UseDT, tbl_tt_trans.TransNo FROM tbl_tt_trans WHERE tbl_tt_trans.CODE)=[Forms]![frm_switchboard]![swCode] AND (tbl_tt_trans.RandomMarker=IIF([Forms]![frm_switchboard]![chk_rndm],"Y",Null) OR [Forms]![frm_switchboard]![chk_rndm] = False) AND tbl_tt_trans.PtType) "O" ORDER BY tbl_tt_trans.UseDT; John Spencer Access MVP 2002-2005, 2007-2009 The Hilltop Institute University of Maryland Baltimore County yator wrote: How would I return a subset of records if a form check box is checked but return all records if the check box is blank. The formcheck box is: [Forms]![frm_switchboard]![chk_rndm] The field [RandomMarker] can have the values of "Y" or Null only. The following SQL pulls the correct data when the check box is checked, but I recive an "OpenForm action was canceled" when the box is blank. How would I modify the query to pull all results ([RandomMarker]="Y" or Null) when the box is not checked? SELECT tbl_tt_trans.TransNo, tbl_tt_trans.Fac, tbl_tt_trans.CODE, tbl_tt_trans.RandomMarker, tbl_tt_trans.UseDT, tbl_tt_trans.TransNo FROM tbl_tt_trans WHERE (((tbl_tt_trans.CODE)=[Forms]![frm_switchboard]![swCode]) AND ((tbl_tt_trans.RandomMarker)=[Forms]![frm_switchboard]![chk_rndm]) AND ((tbl_tt_trans.PtType) Not Like "O")) ORDER BY tbl_tt_trans.UseDT; thanks for the help . |
#7
|
|||
|
|||
Return All Record if Check Box is Blank
Thanks John. works great!
"John Spencer" wrote: SELECT tbl_tt_trans.TransNo, tbl_tt_trans.Fac, tbl_tt_trans.CODE, tbl_tt_trans.RandomMarker, tbl_tt_trans.UseDT, tbl_tt_trans.TransNo FROM tbl_tt_trans WHERE tbl_tt_trans.CODE)=[Forms]![frm_switchboard]![swCode] AND (tbl_tt_trans.RandomMarker=IIF([Forms]![frm_switchboard]![chk_rndm],"Y",Null) OR [Forms]![frm_switchboard]![chk_rndm] = False) AND tbl_tt_trans.PtType) "O" ORDER BY tbl_tt_trans.UseDT; John Spencer Access MVP 2002-2005, 2007-2009 The Hilltop Institute University of Maryland Baltimore County yator wrote: How would I return a subset of records if a form check box is checked but return all records if the check box is blank. The formcheck box is: [Forms]![frm_switchboard]![chk_rndm] The field [RandomMarker] can have the values of "Y" or Null only. The following SQL pulls the correct data when the check box is checked, but I recive an "OpenForm action was canceled" when the box is blank. How would I modify the query to pull all results ([RandomMarker]="Y" or Null) when the box is not checked? SELECT tbl_tt_trans.TransNo, tbl_tt_trans.Fac, tbl_tt_trans.CODE, tbl_tt_trans.RandomMarker, tbl_tt_trans.UseDT, tbl_tt_trans.TransNo FROM tbl_tt_trans WHERE (((tbl_tt_trans.CODE)=[Forms]![frm_switchboard]![swCode]) AND ((tbl_tt_trans.RandomMarker)=[Forms]![frm_switchboard]![chk_rndm]) AND ((tbl_tt_trans.PtType) Not Like "O")) ORDER BY tbl_tt_trans.UseDT; thanks for the help . |
Thread Tools | |
Display Modes | |
|
|