Creating multiple IF clauses???

[jedcomyn]

Hi

I am still trying to create a search engine for my database that will allow me to produce results according to any of the input criteria ( Lastname, Currentfirm etc). Having looked at the dynamic query post i have decided to use Vb code to run the searches according to one or many of the inputs.

BUT......

How do you write the code to skip inputs that are zero e.g:

IF [ ],[ ] = 0 Then

and i'm stuck!!!!

pls help am a newbie trying to help out a friend

jed

 

[FoFa]

Typically you try to build the query where clause dynamicaly.
But here is a thought, it runs a tad slower, but if it is not a huge table might not matter. You can use LIKE for all your checks, and those that are not supplied you can use * (wildcard) to return any matches. But if do not wish to basically it is something like this (not true vb code, adjust accordingly).
' Set you Sql variableup with the basics
sSQL = "SELECT Col1, Col2, Col3 From MyTable "
' init a variable to hold your where clause NOTE length is 6
sWhere = "WHERE "
' check first form field to see if a criteria is supplied
If FormField1 has a value then
sWhere = sWhere & "Col4 = '" & FormField1 & "' " ' string example
End If
' check second form field to see if a criteria is supplied
If FormField2 has a value then
' check if we need to install the AND/OR between criteria
IF len(sWhere) > 6
sWhere = sWhere & " AND Col3 = " & FormField2 & " " ' numeric example
ELSE
sWhere = sWhere & " Col3 = " & FormField2 & " "
End If
' Check any other fields like the field2 check above
......
' Check if we have ANY criteria to apply
if len(sWhere) > 6 then
sSQL = sSQL & sWhere
end if

sSQL at this point should contain a valid SELECT statement spaces are thing I miss the most that causes errors, closely followed by quotes around text values and the occasinal #'s around literal dates
msgbox sSQL,,"Testing" ' display the SQL to verify for testing

 

[jedcomyn]

Thanks for your time

Thank you for taking time to look at this prob,

Have actually just created a query in which the criteria are all

LIKE "*" & [Forms]![frmsearchcandidate][cboclient] & "*"

It works but will only find files that have data in all the search catagories.

am debating whether to change.

jed