Performing selective search?

[logius]

We have a UserForm where clients will search for items in an inventory based on multiple search fields. Depending on which data they supply (search criteria) the program will search for different data in the inventory. In other words:

Enter Search Criteria:
Serial #: 1234567
City: AUSTIN
Address: <not supplied>
Type: D.R.E.
Status: <not supplied>
Sector: 1B

Now, with this data, the program should search for the Serial #, City, Type, and Sector fields, but ignore the Address and Status fields. Likewise, should they leave out/fill in the other fields it would be nice to ignore/include that data in the search.

The problem is the logic. Is the best mechanism to use a bunch of boolean values to check whether the fields are empty or filled in or is there some other built-in means of doing this? It seems ridiculous to have a bunch of If-Then-Else statements cluttering up the code, but if that is the only way to do it, then it'll have to be done. The only other option that comes to mind is a Case statement, but that seems a little too cumbersome as well.

Has anyone run into this issue before?


----------------------------------------
If you are reading this, then you have read too far...

 

[RonRepp]

What is your VBA program in? Excel?

Ron Repp

 

[logius]

Sorry, yes, it's in Excel.

----------------------------------------
If you are reading this, then you have read too far...

 

[mikej336]

I like to build a query with if's like

sqlstr = "SELECT PTID, PTSVNAM, PTSVHSE#, PTSVDPRE, PTSVSTRT, PTSVCOMN, PTSVLOCT, PTSTATUS,"
sqlstr = sqlstr & " SVCID, SVACCT, SVBRCH, SVCOTY,"
sqlstr = sqlstr & " AMNAME, AMNAM2, AMADR1, AMADR2, AMADR3, AMSTAT, AMZIP, AMZIP4, AMACCT, AMBRCH, AMEMAL"
sqlstr = sqlstr & " FROM " & CurrLib1 & ".PLTMAST inner join " & CurrLib2 & ".SRVMAST99"
sqlstr = sqlstr & " on PTID=SVCID"
sqlstr = sqlstr & " inner join " & CurrLib2 & ".ACTMAST"
sqlstr = sqlstr & " on SVACCT=AMACCT AND SVBRCH = AMBRCH"
sqlstr = sqlstr & " WHERE PTSVHSE# >=" & iStartNum & " and PTSVHSE#<=" & iEndNum & " and PTSVSTRT='" & sAddress & "' and PTSTATUS='AC' and PTRECTYP='SL'"

'check to see if there is a direction
If Len(Trim(sDirection)) > 0 Then
sqlstr = sqlstr & " and PTSVDPRE='" & sDirection & "'"
End If

'Check to see if there is anything in the city
If Len(Trim(sCity)) > 0 Then
sqlstr = sqlstr & " and PTSVCOMN='" & sCity & "'"
End If

'add an order by street number
sqlstr = sqlstr & " Order by PTSVHSE#"


hope that helps

Uncle Mike