Filter form based on multiple criteria

[ekta]

I am using the code below to filter my form.

DoCmd.OpenForm "frmBusinessOppurtunity_Main", , , "Capture_Lead = '" & Me!CaptureLead & "' AND Opportunity_Type = '" & Me!Type & "' AND Opportunity_Name = '" & Me!Name & "'"

It works fine but I have to enter all 3 criteria's to make it work.I changed AND to OR. Now it will work if I just enter any one criteria. It should work if I enter just 1, any 2 or all 3 criteria's.
What needs to be done to make this work???

Thanx in advance
Ekta

 

[Jack Cowley]

It is difficult to do what you want the way you are trying to do it because of all the possible combinations of criteria. I suggest you use the method shown in this article as it will allow you to use none or any combination of criteria. The code may look a bit intimidating, but once you get the hang of it it is a very flexible solution to multiple criteria.

In the code you will want to base your form on the query that is created and instead of opening the query, as is done in the code, open your form.

hth,
Jack

 

[ekta]

Jack

I am reading the article right now.. will let u know how it goes

Thanx again

 

[Jack Cowley]

You are welcome. If you run into a problem just let us know....

Jack

 

[ekta]

Jack:

I am a littlt confused. In this example they r running the query on button click and that is where they added the code.

In my case I will open the form but then where will I add all the query.

I have one form where the users will enter their criteria. I created a query and based my form on that query. I don't understand where to enter the code to filter the form.


Ekta

 

[Jack Cowley]

Ekta -

The last lines of code in the article are these:

Set QD = db.CreateQueryDef("Dynamic_Query", _
"Select * from orders " & (" where " + Mid(where, 6) & ";"))
DoCmd.OpenQuery "Dynamic_Query"

What you want to do is change the current Record Source of your form to Dynamic_Query. Then change the line of code that now reads:

DoCmd.OpenQuery "Dynamic_Query"

To:

DoCmd.OpenForm "PutYourFormNameHere"

Now when you open your form you will see the record(s) filtered by the criteria selected by the user.

Jack

 

[ekta]

Thanx Jack..works PURRRRFECT...

 

[Jack Cowley]

Congratulations Ekta! I am glad you got it working and I am glad I was able to assist you.

Jack

 

[ekta]

Jack..I have one more question

I am filtering my form based on 3 criteria's. 2 are combo boxes and the third is a text field. For the text field it only works if I type the entire text. I want it to work if I type just part of the text.
For example lets take 'technical support'. If I just type Technical it should give me the results.

Ekta

 

[Jack Cowley]

Ekta -

You must have an error in your code that comes before the 'Set QD...' line of code. You should have only 4 lines of Where clauses in your code and the first one should read: Where = Null.

In the code you sent you me:

'[CaptureLead] = 'Baker' AND [Opportunity_Name] = Technical Support AND [Capture_Lead] = 'Baker' AND [Opportunity_Name] = 'Technical Support'

you should only see:

[Capture_Lead] = 'Baker' AND [Opportunity_Name] = 'Technical Support'

Also, you have to be very careful in using a Text box for data entry in this case as someone can easily make a typo and they will get an error or the wrong data.

Let me know if this helps you.

Jack

 

[ekta]

Jack:

I fixed that error..I forgot to put single quotes for the third field. It works fine now.

You are right about users doing the data entry. For this field I cannot create a combo box. It's a memo field and users want to search by this field. That's why I was asking if they could just enter the first couple of words and get the result. Right now it only displays the result if they enter the entire text.

Ekta

 

[Jack Cowley]

Ekta -

Look at the code in the article. It shows you have to use the '*' wildcard character and 'Like' so you can look for *MyFile* and find the stuff you want without having to type in the exact phrase....

Good luck!

Jack

 

[ekta]

If Left(Me![Name], 1) = "*" Or Right(Me![Name], 1) = "*" Then
where = where & " AND [Opportunity_Name] like '" + Me![Name] + "'"
Else
where = where & " AND [Opportunity_Name] = '" + Me![Name] + "'"
End If

I did take a look at that. If I type file* or *file* it gives me empty record.


Ekta

 

[ekta]

Figured it out. At the beginning of the code there are these 3 lines

where = Null
where = where & " AND [ShipCountry]= '" + Me![Ship Country] + "'"
where = where & " AND [CustomerID]= '" + Me![Customer Id] + "'"
where = where & " AND [EmployeeID]= " + Me![Employee Id]


I added them to my code with the fields on my form. Because of this my criteria looked something like this
'Baker' AND 'Baker'
='*flow*' And Like '*flow*'
When I commented these lines out, the query criteria just shows 'Mancini' and even the '*' works just fine.

I don't understand why these lines were shown in the code?

Ekta

 

[Jack Cowley]

Ekta -

The code is 'sample code' so they put in all the possibilities so you can see how to use the difference syntax. It is meant as a guide and not the exact code.

I am glad you have it working and continued success!

Jack

 

[ekta]

oh i c. The lines were not commented so I thought they have to be included in the code. Not very good with programming but I try. I am still learning.
Neways..it works gr8 now.

Thanks for your help again
Ekta

 

[Jack Cowley]

Ekta -

Sometimes you kinda have to read between the lines with Microsoft.... Just keep trying and you will be a coding expert any day now....

I'm glad I was able to help.

Jack

 

[Zako]

I made that MS code work in the Northwind database... What I would like to know is.. Can that query be assigned to lets say... a subform.. and when the query runs it just populates the form the subform is attached to. I really dont want to have new windows opening up in my users database if at all possible.


thanks

 

[Jack Cowley]

If you are asking if you can select criteria in a subform and then have the 'answers' displayed it the main form here is my answer. Reverse the situation. Put the criteria in the main form and then use the query as the Record source for the subform. A simple requery of the subform will display the data selected in the main form.

hth,
Jack

 

[Zako]

Ok Jack, I decided to use the method found in:
http://support.microsoft.com/default.aspx?scid=kb;EN-US;209645

Instead of the other one because it is simpler.

I have created the Query with the correct search criteria, I have created the form to enter search criteria for the fields I want to be searchable. I have a button that "runs" the query and it opens as a datasheet. I did not make a macro to run the query since that seemed redundant.

What I do not know how to do is, run the query without it opening on the screen, but have it populate a subform.