SQL to search multiple fields

[Miethr]

I have a search form, and it has 2 combo boxes, and 3 textboxes. I would like one of the textboxes to search 10 different fields for the inputted data. Along with that search all of the others must be able to be used to narrow down the criteria. How can I do this with SQL? Right now I have a select distinct, where, and I use And to include all 5 parameters. What can I do to use the one box to search numerous fields and still return options when coupled with the other search parameters. Thanks



Riley

 

[Miethr]

anybody??

Thanks
RILEY

 

[KeithG]

You will have to build your SQL statement dynamically when the user click a the Retrieve button on your form. Search for a post called Database From query and you should see some examples.

 

[Miethr]

What do you mean by dynamically? I found nothing under Database From Query, if you had the thread I would appreciate. My apologies for being a rook when it comes to this stuff.

thanks

RILEY

 

[KeithG]

I will try to find the thread and post a link for you.

 

[KeithG]

Below is the link to the thread

http://www.access-programmers.co.uk/forums/showthread.php?t=108701

 

[Miethr]

Kieth,

I have seen this thread before, and I think I am mis-stating my question. I am using text boxes and combo boxes. I have the combo boxes working fine, and I can search one field with my text box, but I want to search 10 different fields with the text box and continue only searching 1 field with each combo box....does that clear it up?

 

[KeithG]

So basically the records returned will have a value equal to the combo box and one of the ten fields is equal to the text box? If so this is easy can you post an example of your db

 

[Miethr]

Ok, I hope this is as easy as you say, but the text box is a wildcard search and I am trying to do this in SQL. I will post what I can of the database as it is a lot of internal info.

I have cut it down, but I think there is more than enough to work with, frmSearchCriteriaMain is the one you need, and the only query is written in SQL. If you look in the table you will see that I have substitutes 1 through 10 and in the search form I have the substitute text box. I would like this text box to search all of those fields and return the data. let me know if you have any questions. and if this requires some programming I will probably need it laid out for me. Thanks for all the help.

RILEY

 

[KeithG]

Look at the sample, is this what you wanted? I only made the form search substitute1 - substitute5 but you can add the other fields. Hit the search button I made in the middle of the form after you enter your criteria. You made need to set a reference To DAO object library.

 

[Miethr]

that's something like it, but It will not narrow down the search based on the combo box input, or the steel type. Looking at the code I do not understand the

If Len(Me.cmbGroup11) >= 1 Then
If Len(strWhere) >= 9 Then

statements. What are the numbers there for? A little explanation of the code would go a long ways. Thanks for all your help already

 

[Miethr]

KeithG

I have this working now, thanks a lot for your help. I am still struggling with a couple things.
-Right now I want it to be able to not error when nothing is entered in any of the text boxes or combo boxes. Right now it returns a where error.
-A reset button for those boxes that resets everything also would be nice, so you can hit it and it queries all the data because we haven't filtered any of it
-also it will allow me to search the substitutes like i wanted, but if you search for A1011, and then try to narrow it to just group 3 AWS D14.3 it still gives me a file that has a group 4 in the 14.3 slot. It would be best not to have that. Just an example, i am sure I have others that will cause the same problem


any help?

Thanks again
RILEY

 

[Miethr]

Hoping to get some feedback?

 

[Miethr]

Please help?!

I am still in dire straits. Now my search engine will not requery if I select one criteria get a group and then select another specification within the selected group.

also, i can not reset the query to start a new one how can I get this to happen? please help, I need to get this thing working soon

Riley