Send Multiple Criteria from a form to Query

[Ezeasy]

I enter text into an unbound control on a form that is referred to in the criteria section of a query. And from this, I can change the criteria of the query each time.The name of the control on the form (called Formtype) is Controlname. And I refer to this control in the criteria section of the query as: [Forms]![Formtype]![Controlname]PROBLEM: I can only enter 1 criteria at a time. For example, the query works if I enter "A" on the form to find all records with type "A". However, I want to be able to enter multiple criteria on the form such as: "A Or B" to find two types. The above example would not work and the query would not return any records. It seems as if Access does not evaluate the text first before passing it to the query, so that it literally searches for the text "A Or B" in the table and not "A" or "B" as I intended.Does anyone have any idea on how to solve this? Any suggestions would be very much appreciated.Thanks in advance

 

[BillPower]

Hi Ezeasy, it is possible to do this, can you post the SQL statement in your query. Make sure it is exactly what you want to use in your form, as this function, until you understand how it works, won't be easy to modify.

 

[Ezeasy]

Hi billpower,
Here is what I have like I said I like to enter more than one type on a form.
SELECT tAccount.Acc_number, tAccount.Pt_type, tAccount.Dis_date, tAccount.DRG_code, tAccount.MDC
FROM tAccount
WHERE (((tAccount.Pt_type) Like [Forms]![Formtype]![Controlname] & "*&quot);

 

[allanon]

This will not work very well with only one textbox. If you enter

A or B

Then your search will look like this:
SELECT tAccount.Acc_number, tAccount.Pt_type, tAccount.Dis_date, tAccount.DRG_code, tAccount.MDC
FROM tAccount
WHERE (((tAccount.Pt_type) Like "A or B" & "*&quot);

As you stated above, this just will not work. You now have a couple of options here:

1) you could have multiple textboxes that you enter one search criteria in each one. Then you build your SQL string based on if there is a value in these boxes.

2) you could have a multi-select listbox where the user would hilight the values that they want and then you build the SQL string based on what they have selected.

3) have a bunch of check boxes that represent search criteria and build your SQL string based on which boxes are checked.

Note: option 2 and 3 assume static search criteria. Only option 1 would allow variable search criteria.

With that said you could do many different variants of options 1. EG: you could have a single textbox with a list box beside it. A user enters a value into the textbox, presses a button, then the value is added to the listbox. You then build your SQL based on what is in the listbox.

If you think about it there are many ways you could do them.

If you need some help post it here and I am sure someone will help.

 

[Ezeasy]

Allanon,
I just know the basics of Access if you can show me how to setup option 2 that is the way to go have a multi-select listbox where the user would highlight the values that they want and then you build the SQL string based on what they have selected.
Thanks in advance.

 

[allanon]

Ok. I created a little demo for you with my data:

1) create a dummy query in the query view. Mine is qryDummy. I just did a select * on a table. Doesn't matter which table since it is just a query to hold our results later.

2) create a form with two objects on it. A list box. I called mine lstMyList. Make sure you set the multi select to Extended. This will allow the user to do multiple selection the same as any windows list (using cntl or shift)

I placed 5 different values in the Row Source. My Value list looks like this:

"TRNSFR";"DIRDEP";"DIRMED";"SALARY";"DIRDAY"

You will have to choose your own value list.

Make sure the Row Source Type is value list.

Set up a command button. Mine is called cmdList.

3) While your form is in design mode press the code button and paste this code:

Private Sub cmdList_Click()
    Dim strSQL As String
    Dim strWhere As String
    Dim varItem As Variant
    Dim qry As DAO.QueryDef
    
    strWhere = ""
    strSQL = "SELECT * FROM All_Firms "
    If Me.lstMyList.ItemsSelected.Count > 0 Then
        For Each varItem In Me.lstMyList.ItemsSelected
            If Len(strWhere) = 0 Then
                strWhere = "WHERE "
            Else
                strWhere = strWhere & "OR "
            End If
            strWhere = strWhere & "transaction_type like " & Chr(34) & Me.lstMyList.Column(0, varItem) & "*" & Chr(34) & " "
        Next varItem
    End If
    Set qry = CurrentDb.QueryDefs("qryDummy")
    qry.SQL = strSQL & strWhere
    qry.Close
End Sub

4) change this line:

strSQL = "SELECT * FROM All_Firms " to select from your table

5) change this line:

strWhere = strWhere & "transaction_type like " & Chr(34) & Me.lstMyList.Column(0, varItem) & "*" & Chr(34) & " "

to start with your field name instead of transaction_type.

6) run the form. select whatever you want and then click the button. What happens then is that the qryDummy that we set up will now display the results of your qruery. You can now use that query to create reports, forms, anything that you would use any other query for. Have fun.

PS. you do not have to type the values into the listbox. You can attach the listbox to a table and have them do selection based on a lookup table.

 

[Ezeasy]

allonon,
thanks for your help but I'm getting an error on line
Dim qry As DAO.QueryDef - user defined type not defined

 

[allanon]

You have to ensure that you have the Microsoft DAO 3.51 or 3.6 library selected in the references. Open a code window and click tools/references and find one of them and select it.

 

[Ezeasy]

Hi allanon,
one more error - Run time error '3131':
Syntax error in From clause.

when i select Debug line with - qry.SQL = strSQL & strWhere is highlighted.

thanks again for your help...............

 

[allanon]

just before the line type this:

debug.print strsql & strwhere

This will print the sql to the debug window.

If you do not know where to find this click

view/debug window when you have a code window open. post the statement displayed there.

 

[Ezeasy]

I added the line of code debug......
I hope this is what your looking for
"SELECT * FROM tAccount; "

 

[allanon]

Hmmm, a simple SELECT statement should not cause a problem. Is you database small? if so sent it to me and I will have a quick look:

jdyck@wcb.mb.ca

I am assuming then when you see it in the debug window there are no quotes around it.

Do not include the ";" when you are building your sql statement. Access does not need it.

 

[Ezeasy]

Thanks for all your help it works great just what I needed..........