Query on Yes/No to show all records

[David Scott]

Hi all.

I have run into a problem with my database. It seems something that should be easy to do but although I've searched a lot I can't find a solution.

I have a query that takes the filtering values from a form. In the top part I have a combobox for each field which I want to filter and in the bottom part the result from the query. This way when a user selects a value in the combo the query auto updates and shows the results applying the filter in "real time".

The problema is with a Yes/No field. With all the other fields the default value is "*" so I can see all the records until I select a filter in the form. However in the yes/no field I can't select an "*" value. I'm forced to see all the records with a Yes value in that field or the ones with a No but never all of them.

It's there an easy way I can tell the query to show all the fields with Yes OR all the fields with No OR all the fields whatever the value?

Thank you very much in advance!

[ADezii]

You could create a Query showing ALL Records, then modify the SQL building a Criteria String, then Open the Query. Let's suppose that:

1. I have an Employees Table named Employees.
2. I have a [Retired] (Yes/No) Field in the Employees Table.
3. I have a Combo Box on the Form (cboRetired) displaying the following 3 Options:
   1. (ALL) - ALL Employees, retired or not.
   2. Yes - Retired Employees only
   3. No - Employees who are not Retired.
4. The following Code will modify the SQL of qryEmployees to show either of the 3 Options above, or all Employees should none be selected.
5. Other Criteria, of course, can be build into strBuild.
   

   Dim strBuild As String
   Dim qdf As DAO.QueryDef
    
   Select Case Me![cboRetired]
     Case "(All)"
       strBuild = "[Retired] = True Or [Retired] = False"
     Case "Yes"
        strBuild = "[Retired] = True"
     Case "No"
        strBuild = "[Retired] = False"
     Case Else
       strBuild = "[Retired] = True Or [Retired] = False"
   End Select
    
   Set qdf = CurrentDb.QueryDefs("qryEmployees")
    
   qdf.SQL = "SELECT * FROM Employees WHERE " & strBuild
    
   DoCmd.OpenQuery "qryEmployees", acViewNormal, acReadOnly

6. I hope this is what you are looking for.

[NeoPa]

Essentially No. There is no way of using a wildcard or equivalent with numbers.

You can get around it by checking if the control is actually even set in your filter, but you'd need to handle that in your design.

[David Scott]

Thank you for your answers.

Finally I could solve it using a simple "trick". Maybe it's not so elegant but it works like a charm. :)

In my query I applied the val() function to the boolean field. As you now as a result it gives back "0" if the boolean is False and "-1" if it's True. The good thing is that when converted to a value it lets me search using "*".

Then I changed the form so when the user selects a True value the query searches for "-1". When he selects a False value the query searches for "0". At last if he selects "All" the query searches for "*" (this is also the default value). As I said before it works perfectly and gives the exact result I needed.

Thank you again for your help. Both answers made me think about it and find this solution.

[NeoPa]

Good thinking David.

With that in mind, and avoiding the call of a function for each record (as your current solution does), thus making it process more efficiently, you might like to try an alternative of :

WHERE ([YNField] Between True And False)

Your solution got me thinking in the right direction ;-)