Urgent help needed to "Filter" the data...

Hi,

Can somebody modify this code so that I can filter the data by "Customer Name" "Sales Person" & "City".

The following is the code:
Note: Here in this code "fname" & "lname" or the names of Option Buttons. And Text9 is the Text Box where user can enter the text to filter.

VB Code:

Private Sub Text9_Change()
rs.Close
If fname.Value = True Then
    rs.Open "Select * from dept where fname like '" & Text9 & "%'", db
    rs.Requery
Else
    rs.Open "Select * from dept where lname like '" & Text9 & "%'", db
    rs.Requery
End If
 
If rs.RecordCount <> 0 Then
    show_rec
Else
    clear_txt
End If
End Sub

Thanks in advance for any help.

sweetie_2005





Edit: Added [vbcode][/vbcode] tags and indenting for clarity. - Hack

First, fname and lname should not be the names of option buttons. The buttons should be called something like optfname and optlname.

Second, are you saying that in addition to being option button names, fname and lname are also fields in your database? Thats confusing.

You want to query on Customer Name, Sales Person and City. What db fields holds this information? Do you want individual queries on each of the three, or one query based on all three?

Quote:

---

Originally Posted by Hack

First, fname and lname should not be the names of option buttons. The buttons should be called something like optfname and optlname.

Second, are you saying that in addition to being option button names, fname and lname are also fields in your database? Thats confusing.

You want to query on Customer Name, Sales Person and City. What db fields holds this information? Do you want individual queries on each of the three, or one query based on all three?

---

------------------------------------------------------------------------

"fname" & "lname" or table fields names
and fname=first name, lname=last name
now i can filter data using fname or lname depends upon chosing the option button.

Now I dont need to filter data using "fname" or "lname" instead of that I would like to filter data using "customer name" "sales person" & "city" these are the names given in table and the same names assigned to option buttons.

plz help.

Quote:

---

Originally Posted by sweetie_2005

------------------------------------------------------------------------

"fname" & "lname" or table fields names
and fname=first name, lname=last name
now i can filter data using fname or lname depends upon chosing the option button.

Now I dont need to filter data using "fname" or "lname" instead of that I would like to filter data using "customer name" "sales person" & "city" these are the names given in table and the same names assigned to option buttons.

plz help.

---

How is customer name stored? In what field is it stored?

How is sales person name stored? In what field is it stored?

Same with city?

How do I select a customer name?
How do I select a sales person name?
How do I select a city?

VB Code:

Private Sub Text9_Change()
rs.Close
If fname.Value = True Then
    rs.Open "Select * from dept where fname like '" & Text9 & "%'", db
    rs.Requery
Else
    rs.Open "Select * from dept where lname like '" & Text9 & "%'", db
    rs.Requery
End If
 
If rs.RecordCount <> 0 Then
    show_rec
Else
    clear_txt
End If
End Sub

Friend, just let me know here in the above code is "IF" & "ELSE " because only two options are there suppose if i have four options in which i can select anyone OPTION how the could would be.

VB Code:

Private Sub Text9_Change()
rs.Close
If fname.Value = True Then
    rs.Open "Select * from dept where fname like '" & Text9 & "%'", db
    rs.Requery
 
[B]'ACTUALLY I NEED THE CODE HERE TO CHOOSE ANOTHER TWO OPTION'[/B]
 
Else
    rs.Open "Select * from dept where lname like '" & Text9 & "%'", db
    rs.Requery
End If

Please use [vbcode][/vbcode] tags when posting code. It makes it so much easier to read.

Quote:

---

Originally Posted by sweetie_2005

Friend, just let me know here in the above code is "IF" & "ELSE " because only two options are there suppose if i have four options in which i can select anyone OPTION how the could would be.

---

Then, put four option buttons on your form.

Also, why are you running your query from the change event of a textbox???

At the very least, you should be running your query from the click event of one of your option buttons. If you do that, you don't need an If/Else.

Dear Friend, Could you tell me the following code is correct which i added in between with bold letters. The code is ok. but i need more options so that i need to add more options:

Private Sub Text9_Change()
rs.Close
If fname.Value = True Then
rs.Open "Select * from dept where fname like '" & Text9 & "%'", db
rs.Requery

If Cutomer.Value = True Then
rs.Open "Select * from dept where customer like '" & Text9 & "%'", db
rs.Requery

If city.Value = True Then
rs.Open "Select * from dept where city like '" & Text9 & "%'", db
rs.Requery

Else
rs.Open "Select * from dept where lname like '" & Text9 & "%'", db
rs.Requery
End If

If rs.RecordCount <> 0 Then
show_rec
Else
clear_txt
End If
End Sub

You didn't answer my question.

Why are you doing this from the change event of your textbox?

Quote:

---

Originally Posted by Hack

You didn't answer my question.

Why are you doing this from the change event of your textbox?

---

----------------------------------------------------------------------

Actually i want to modify the following project (and create new database) according to my requirements.

That's why i need it.

Regards,

sweetie_2005