Return All Record if Check Box is Blank

Y

yator

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
 
J

John Spencer

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
 
Y

yator

Thanks John. works great!

John Spencer said:
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
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
Click to expand...
.
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Retrieve all record if check box = 0 (Not checked) 5
IIF statement in criteria not working 1
Blank fields 3
Criteria to return all records if selection from form is null 3
Check box and make table query 1
Query criteria - exclude data and return all records if null 3
If statement that returns all entries or one 2
Check Box Use As Criteria 6

Top