469,312 Members | 2,437 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,312 developers. It's quick & easy.

Query on Yes/No to show all records

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
4 9621
8,800 Expert 8TB
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
    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
    15. Set qdf = CurrentDb.QueryDefs("qryEmployees")
    17. qdf.SQL = "SELECT * FROM Employees WHERE " & strBuild
    19. DoCmd.OpenQuery "qryEmployees", acViewNormal, acReadOnly
  6. I hope this is what you are looking for.
Nov 7 '11 #2
32,173 Expert Mod 16PB
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
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
32,173 Expert Mod 16PB
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.

Similar topics

1 post views Thread by (Pete Cresswell) | last post: by
4 posts views Thread by mugen | last post: by
15 posts views Thread by Joachim | last post: by
reply views Thread by suresh191 | last post: by
reply views Thread by harlem98 | last post: by
1 post views Thread by Geralt96 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.