If statement to show all True and False records

[Snowflake68]

I would really appreciate some help with the correct syntax for a query to show all records if checkbox is ticked on form. Basically if the checkbox is unticked (false) then show only records that are false but if the checkbox is ticked (true) then to show ALL records regardless on the value of the field.

The field type is a Yes/No field and I have tried the following but it will only return either true or false records depending on whether the checkbox is ticked or not. I want to show all records when the checkbox is true.

IIf([Forms]![frmSearchItemSelection]![chkMSPM]=True,<=0,0)

I can confirm that when I just enter the criteria for <=0 it returns all records so why doesnt it work in the above if statement?


This is the SQL for the MS Access query.

SELECT [0_Cntrl_ItemSelection].MStreamPMerch, [0_Cntrl_ItemSelection].Item_No_
FROM 0_Cntrl_ItemSelection
WHERE ((([0_Cntrl_ItemSelection].MStreamPMerch)=IIf([Forms]![frmSearchItemSelection]![chkMSPM]=True,([0_Cntrl_ItemSelection].MStreamPMerch)<=0,0)));

 

[CJ_London]

try

...WHERE MStreamPMerch=[Forms]![frmSearchItemSelection]![chkMSPM] OR [Forms]![frmSearchItemSelection]![chkMSPM]=True

Note : starting a query (or table or field) name with a number is a bad idea and can result in unexplicable errors

 

[Snowflake68]

try

...WHERE MStreamPMerch=[Forms]![frmSearchItemSelection]![chkMSPM] OR [Forms]![frmSearchItemSelection]![chkMSPM]=True

Note : starting a query (or table or field) name with a number is a bad idea and can result in unexplicable errors

OMG why didn't I think of that? Think I stayed up far too late last night that I just couldn't see the wood for the trees. Thank you so much for your help CJ_London.