Filter Query Criteria based on check box

[OasisUnknown]

Hello All,

I am trying to filter a query based off a check box that is on my form.


the field I am trying to filter either has text values in them or they are null

What I am trying to accomplish is if the check box is not checked then just show me all the records. (basically don't filter anything and just show all. nulls and texts included.

when the check box is checked only show me null values and nothing with text in them.


in my field criteria I am trying something like

iif([Forms]![MyForm]![MyCheckbox] = 0, "*", Is Null)

but this is not working in any reguard but if I do a search for some text that is in there such as.

iif([Forms]![MyForm]![MyCheckbox] = 0, "myText", Is Null)

I will get an error because of the is null. it says the expression is to difficult to calculate.

but if I do

iif([Forms]![MyForm]![MyCheckbox] = 0, "myText", "myOtherText")

my query will flip back and forth between the fields with myText and myOtherText as expected.

I am not really sure what the best way to go about this is.

 

[Micron]

With iif([Forms]![MyForm]![MyCheckbox] = 0, "*", Is Null) you are trying to use an operator, not Null.
You could try iif([Forms]![MyForm]![MyCheckbox] = 0, "*", Null), but I'm not sure if that will work. Let us know and maybe we can suggest something else, such as applying a filter or where clause in vba.
Usually, I'd do this by having all the form records loaded, then filtering on checkbox selection and applying a filter (and removing it if the box is deselected).

 

[apr pillai]

Query criteria will not accept IIF() method. You can use a small VBA Routine that runs on the After_Update() event of the check-box. Sample Code is given below for reference:

Private Sub Check38_AfterUpdate()
Dim x As Variant, q As QueryDef
Dim strSQL As String


Set q = CurrentDb.QueryDefs("Query57")
strSQL = "SELECT Employees.[Last Name], Employees.[ZIP/Postal Code] FROM Employees "
x = Me!Check38
Select Case x
       Case True
       strSQL = strSQL & " WHERE ([Last Name] Like '???*');"
       q.SQL = strSQL
      Case False
      q.SQL = strSQL & " WHERE ([Last Name] is Null OR [Last Name]='');"
End Select
CurrentDb.QueryDefs.Refresh
DoCmd.OpenReport "Query57", acViewPreview
      
End Sub

When check-box is unchecked Records with Last-Name empty field will appear on the report. When checked all records except empty Last-Name field records will be displayed on the Report.