query based on checkbox to show all records or filter

[waubain]

I have a form with a checkbox (chkEntireList). In the query OnEDList is a Yes/No field. I want if chkEntireList is UNchecked then OnEDList Criteria = 0, but if chkEntireList is checked then return all records. I have tried the following in OnEDList critera box:

IIf([forms]![frmRxApproval]![chkEntireList]=-1,0 Or -1,0)

If chkEntireList is unchecked, then I get the correct records returned, but if it is checked, I only get records with a -1 value and not all records. I thought this should work because if I enter = 0 Or -1 in the criteria box I do get all records.

Is there another way to approach this task.

Thank you in advance.

You don't know what you don't know...

 

[lameid]

In the SQL view your where clause would look something like...

Where (OnEDList = 0 OR [forms]![frmRxApproval]![chkEntireList]=-1)

The problem with this though is that your checkbox is not indexed and therefore will slow your query up which may or may not be an issue depending on your data and hardware.... The alternative is to build your SQL statement in code and only include criteria for OnEDList if the checkbox is unchecked. If you build in code, the next question will be how are you using the query? This is because there are more than one way to use SQL statements in code.

 

[waubain]

lameid,
Thank you for the reply. A combox is based on the query. Typically a user would only want to see the filtered list, but may need to see the entire list. The entire list is less than 200 choices. After posting I changed by Google approach and found this working answer:

Like IIf([forms]![frmRxApproval]![chkEntireList]=-1,"*",0)

but I prefer your answer and will try to build the SQL query in code.

You don't know what you don't know...

 

[lameid]

Another approach.... Change the Rowsource property of your combobox and requery it... You'd have to do this on the afterupdate of the checkbox. You probably should also set it on the form's open event to however the checkbox is defaulted.