Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 151
  • Last Modified:

SQL Searches Within VB

I have a form with 5 search fields on it.  I also have a search button to search on 1 or multiple fields.

The problem is, how do I create one SQL search that can accomodate this without having to write a routine for every possible search combination?

Any input would be appreciated.

Thanks in Advance.
0
Baddogi
Asked:
Baddogi
  • 6
  • 3
1 Solution
 
TimCotteeHead of Software ServicesCommented:
Build a statement something like this:

Dim strSQL As String
strSQL = Iif(Text1.Text <> "","Field1 = '" & Text1.Text & "'")
strSQL = Iif(Text1.Text <> ""," AND ","") & Iif(Text2.Text <> "","Field2 = '" & Text2.Text & "'")
strSQL = Iif(Text1.Text <> "" And Text2.Text <> ""," AND ","") & Iif(Text3.Text <> "","Field3 = '" & Text3.Text & "'")

And so on until you get to five.

Then your statement will be correctly built for any combination of fields.
0
 
yongyihCommented:
You have to use SQL statement.
e.g.

statement like "select * from table1, table2, table3 where searchfield = '" & txtsearchfield1.text & "'"
something like this..

u use ADO or DAO?
0
 
BaddogiAuthor Commented:
Thanks Much.  I'll give that a try....
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
BaddogiAuthor Commented:
2 Problems -  

I got the statement built but there are 2 issues now.

1.  I get a argument not optional on
    strsql=IIF(********

2.  Am I Correct by writing the SQL as:
    Select strsql * from DB


0
 
TimCotteeHead of Software ServicesCommented:
You would use it like:

"Select * From MyTable Where " & strSQL

as for the missing argument, I would need to see the line to determine which bit is missing.
0
 
BaddogiAuthor Commented:
Thanks - The SQL statement should work ok - Here is the block of code for the other:  Do you see anything wrong?

choice = IIf(txtsearchNDC.Text <> "", "[ndc_code]='" & txtsearchNDC.Text & "")
choice = IIf(txtsearchNDC.Text <> "", " AND ", "") & IIf(txtPrevNDCSearch.Text <> "", "[prev_ndc_code] = '" & txtPrevNDCSearch.Text & "")
choice = IIf(txtsearchNDC.Text <> "" And txtPrevNDCSearch.Text <> "", " AND ", "") & IIf(txtmfgsearch.Text <> "", "[mfg_name] = '" & txtmfgsearch.Text & "")
choice = IIf(txtsearchNDC.Text <> "" And txtPrevNDCSearch.Text <> "" And txtmfgsearch.Text <> "", "AND", "") & IIf(txtprodnamesearch.Text <> "", "[prod_name]='" & txtprodnamesearch.Text & "")
choice = IIf(txtsearchNDC.Text <> "" And txtPrevNDCSearch.Text <> "" And txtmfgsearch.Text <> "" And txtgenericsearch.Text <> "", "And", "") & IIf(txtgenericsearch.Text <> "", "[gcr_name] = '" & txtgenericsearch.Text & "")
0
 
BaddogiAuthor Commented:
Thanks - The SQL statement should work ok - Here is the block of code for the other:  Do you see anything wrong?

choice = IIf(txtsearchNDC.Text <> "", "[ndc_code]='" & txtsearchNDC.Text & "")
choice = IIf(txtsearchNDC.Text <> "", " AND ", "") & IIf(txtPrevNDCSearch.Text <> "", "[prev_ndc_code] = '" & txtPrevNDCSearch.Text & "")
choice = IIf(txtsearchNDC.Text <> "" And txtPrevNDCSearch.Text <> "", " AND ", "") & IIf(txtmfgsearch.Text <> "", "[mfg_name] = '" & txtmfgsearch.Text & "")
choice = IIf(txtsearchNDC.Text <> "" And txtPrevNDCSearch.Text <> "" And txtmfgsearch.Text <> "", "AND", "") & IIf(txtprodnamesearch.Text <> "", "[prod_name]='" & txtprodnamesearch.Text & "")
choice = IIf(txtsearchNDC.Text <> "" And txtPrevNDCSearch.Text <> "" And txtmfgsearch.Text <> "" And txtgenericsearch.Text <> "", "And", "") & IIf(txtgenericsearch.Text <> "", "[gcr_name] = '" & txtgenericsearch.Text & "")
0
 
TimCotteeHead of Software ServicesCommented:
I think I missed it out when I typed it in to start with:

choice = IIf(txtsearchNDC.Text <> "", "[ndc_code]='" & txtsearchNDC.Text & "","")
choice = IIf(txtsearchNDC.Text <> "", " AND ", "") & IIf(txtPrevNDCSearch.Text <> "", "[prev_ndc_code] = '" & txtPrevNDCSearch.Text & "","")
choice = IIf(txtsearchNDC.Text <> "" And txtPrevNDCSearch.Text <> "", " AND ", "") & IIf(txtmfgsearch.Text <> "", "[mfg_name] = '" & txtmfgsearch.Text & "","")
choice = IIf(txtsearchNDC.Text <> "" And txtPrevNDCSearch.Text <> "" And txtmfgsearch.Text <> "", "AND", "") & IIf(txtprodnamesearch.Text <> "", "[prod_name]='" & txtprodnamesearch.Text & "","")
choice = IIf(txtsearchNDC.Text <> "" And txtPrevNDCSearch.Text <> "" And txtmfgsearch.Text <> "" And txtgenericsearch.Text <> "", "And", "") & IIf(txtgenericsearch.Text <> "", "[gcr_name] = '" & txtgenericsearch.Text & "","")


The IIF function has three arguments: Iif(TestCondition,TrueValue,FalseValue)

I missed out the false value in the second Iif in each case.
0
 
BaddogiAuthor Commented:
The argument error went away but now the Select statement isn't working.  Incorrect near "where".  Don't I have to specify a field where [field] = ????
0
 
BaddogiAuthor Commented:
I figured it out.....  The correct SQL should be:

"SELECT * from [Database] Where" & _
sqlStr & "'"


Thanks Again For Everyone's Help On This.

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 6
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now