criteria - 1 query or 4?

[Breanna]

I am using Access 2013 and am pretty new to it.
I have a table that includes employee information.
(employee number, first name, last name, address, phone, emergancy contact, medical, start date, hourly pay, etc. )
I have a search form that has a drop down menu to include 4 different options. these are contact, emergency, company and personal. It also has a button that will open the query based on what was chosen. The button has code that says if dropdown box says contact then open contact query.

When I first set it up I created a query for each of the 4 options. Now that I have learned a little bit more I do not think that I need 4 different queries. Is it possible to have one query with everything and in the criteria section write something like "if drop down box says contact then show, if says anything else then hide"

Does this make sense? I would really like it to work that way I only have one query. (my navigation pane is getting really messy and I am trying to clean it up)

Can someone help me figure out what I would have to write in the criteria section in order to set this up? having on query would really make it a whole lot easier.

Thank you for any and all information! Let me know if I left anything important out.

 

[Minty]

Are you searching for 4 separate things or trying to search a combination of the 4?

If you are comfortable with learning a bit of VBA code this would probably be far easier than trying to make the query do the hard work.

In your code you can construct the SQL query based on the selection made, then use that to open display your results on the existing form ?

 

[Breanna]

All the information on the table can be categorized into the 4 options. So when I search for something I just want one of those options. Does that answer your question?

I did not know you could use VBA like that in a query. I have done a little bit before but would not know where to start for this query. Can I create a query with all the info and have vba code to only show certain columns based on what option was chosen? So like an If Then statement just not in the criteria option?

 

[Minty]

You can't really use VBA in a query, but you can construct a query / record source in SQL using VBA.

So on your form you could do the following in the On_load event; (This is written on the fly - not tested but you should get the idea)

Dim sSql as String

sSql = "Select * FROM tblEmployees" 
Me.RecordSource = sSql

Then on your button code

Dim sSql as String
sSql = "Select * from tblEMployees WHERE "  & me.txtCriteria
Me.RecordSource = sSql

Where me.txtCriteria is you drop down box with your criteria.

Using this method means new queries are added simply by adding an extra line to your criteria drop down control, or coding complicated criteria into the same piece of code.
If this is all double Dutch let us know, I'm sure we can steer you in the right direction.

 

[Minty]

If this makes more sense - this searches for 3 options based on an optiongroup called Find_What, and searches for whatever is in a control called finder

Dim sTable as String
DIm sSql as String

sTable = "YourTable"
sSQL = ""
    
 Select Case Me.Find_What
        Case 1  'Serial Number
            sSQL = "SELECT * FROM " & sTable & " WHERE [Serial Number]='" & Me.finder & "' OR [Old Serial No]='" & Me.finder & "'"
        Case 2  'RMA Number
            sSQL = "SELECT * FROM " & sTable & " WHERE [RMA Number]=" & Me.finder
        Case 3  'PO Number
            sSQL = "SELECT * FROM " & sTable & " WHERE [Order Number]='" & Me.finder & "'"
        
 End Select

Me.RecordSource = sSQL

 

[Breanna]

Ill be honest those did not make sense. Sorry!

the first option you gave had commands for the search form. Do those then make it so that the button creates a new query instead of opening one already set?

Where do you put code on a query then. You mentioned record source. Is this all going into the SQL part of the query?

As I mentioned I am very new. Thanks.

 

[Minty]

Hi, They become the query your form uses for its data. (It's record source is the terminology used)
I'm a bit too busy to go through this in detail at the moment, however the best way to find out would be to make a copy of your form and play with the SQL.

If you use the query builder to make the desired results, then look at the query in SQL view you will see the syntax to use for your button to filter the results.