By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,071 Members | 1,196 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,071 IT Pros & Developers. It's quick & easy.

Query on Yes/No to show all records

P: 4
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!
Nov 7 '11 #1
Share this Question
Share on Google+
4 Replies


ADezii
Expert 5K+
P: 8,679
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.
    Expand|Select|Wrap|Line Numbers
    1. Dim strBuild As String
    2. Dim qdf As DAO.QueryDef
    3.  
    4. Select Case Me![cboRetired]
    5.   Case "(All)"
    6.     strBuild = "[Retired] = True Or [Retired] = False"
    7.   Case "Yes"
    8.      strBuild = "[Retired] = True"
    9.   Case "No"
    10.      strBuild = "[Retired] = False"
    11.   Case Else
    12.     strBuild = "[Retired] = True Or [Retired] = False"
    13. End Select
    14.  
    15. Set qdf = CurrentDb.QueryDefs("qryEmployees")
    16.  
    17. qdf.SQL = "SELECT * FROM Employees WHERE " & strBuild
    18.  
    19. DoCmd.OpenQuery "qryEmployees", acViewNormal, acReadOnly
  6. I hope this is what you are looking for.
Nov 7 '11 #2

NeoPa
Expert Mod 15k+
P: 31,709
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.
Nov 12 '11 #3

P: 4
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.
Nov 16 '11 #4

NeoPa
Expert Mod 15k+
P: 31,709
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 :
Expand|Select|Wrap|Line Numbers
  1. WHERE ([YNField] Between True And False)
Your solution got me thinking in the right direction ;-)
Nov 16 '11 #5

Post your reply

Sign in to post your reply or Sign up for a free account.