IIf...Else statement problem in SQL statement

And if need be then use Response.write to review the formatting SQL:

strSQL = "SELECT [tblMembers].[LastName], [tblMembers].[FirstName], [tblMembers].[DOB], " _
& "[tblHousehold].[Address], [tblHousehold].[HomePhone], [tblHousehold].[DaytimePhone], " _
& "(IIf([tblMembers].[LastName]=" & strLastName & ",1,0)+" _
& "IIf([tblMembers].[FirstName]=" & strFirstName & ",1,0)+" _
& "IIf([tblMembers].[DOB]=" & datDOB & ",1,0)+" _
& "IIf([tblHousehold].[Address]=" & strAddress & ",1,0) AS bytHits) " _
& "FROM tblHousehold INNER JOIN tblMembers ON " _
& "[tblHousehold].[HouseholdID]=[tblMembers].[HouseholdId] " _
& "WHERE " _
& "[tblMembers].[LastName]='" & strLastName & "' Or [tblMembers].[FirstName]='" & strFirstName & "' " _
& "Or [tblMembers].[DOB]='" & datDOB & "' Or [tblHousehold].[Address]='" & strAddress & "' "

strSQL = strSQL & " ORDER BY IIf(([tblMembers].[LastName]='" & strLastName & "'),1,0)+IIf(([tblMembers].[FirstName]='" & strFirstName & "'),1,0)+" _
& "IIf(([tblMembers].[DOB]='" & datDOB & "'),1,0)+IIf(([tblHousehold].[Address]='" & strAddress & "'),1,0) DESC;"

Response.write "SQL: " & strSQL

Then see where the missing ' is...

But I believe that would not work in my particular situation.  Please let me know if I am wrong.  

The reason I am using the IIf statment is to rate the matches returned by the SQL statment.
I have up to 4 criteria that are passed via my form (txtLastName, txtFirstName, txtDOB and txtAddress).  All four criteria may be passed with values or perhaps just one, two or three of them will be passed with values.  I want to find records that match any of the criteria (search) values passed to the SQL statement, but I want to order the results based on the closest match to the search criteria.

i.e. if we passed LastName=Smith DOB=03/11/90

Then

Results:

LastName           FirstName             DOB                Address
Smith                  David                 03/11/90         1234 Any Street
Smith                  Carol                 01/12/88         1234 Any Street
Smith                  Frank                06/19/04          3345 Filmore Street
Gaines                 Dean                 03/11/90          5609 Washington Avenue

Does that shed some light on it?

oops, sorry you posted again before I submitted my response to your first response.  Sorry

Firstly you should know that the syntax for using IIF is as follows...

IIF(condition,true,false)

and if nested...

IFF(condition,true,IIF(condition,true,false)

I'm just trying to organise you query using the correct syntax.

At the moment I have you query as...

SELECT FirstName, LastName, DOB, Address, HomePhone, DayTimePhone
FROM tblHousehold INNER JOIN tblMembers ON tblMembers.HouseholdID = tblHousehold.HouseholdID
WHERE FirstName = 'Peter' OR
LastName = 'Roome' OR
DATEDIFF('d',DOB,'11/10/1972') = 0 OR
Address = 'MyAddress'

Can you describe in more detail what you expect the query to do and how you expect them to be sorted - cheers.

I want to be able to rate (sorry for lack of a better word) the search criteria passed to the SQL statment.  The user may enter one, two, three or all four of the criteria to be passed.  I want to be able to show closest matches DESC (i.e. if all 4 search criteria fields are submitted and a record matches all 4 search criteria then list it first), but I do want to show all records that match any of the search criteria fields.  

So if all 4 match list it
and then if another record matches 3 then list it
and if another record matches 2 then list it
and if another record matches 1 then list it...

If only some of you values are passed then build you query accordingly...

sQuery = "SELECT FirstName, LastName, DOB, Address, HomePhone, DayTimePhone " &_
               "FROM tblHousehold INNER JOIN tblMembers ON tblMembers.HouseholdID = tblHousehold.HouseholdID WHERE " &_

If txtFirstName <> "" then
     sQuery = sQuery & "FistName = '" & txtFirstName & "' OR "
End If

If txtLastName <> "" then
     sQuery = sQuery & "LastName = '" & txtLastName & "' OR "
End If

If txtDOB <> "" then
     sQuery = sQuery & "DATEDIFF('d',DOB,'" & txtDOB & "') = 0 OR "
End If

If txtAddress <> "" then
     sQuery = sQuery & "Adrress = '" & txtAddress & "' OR "
End If

sQuery = Left(sQuery,Len(sQuery) - 3) & "ORDER BY LastName ASC, FistName ASC, DOB ASC, Address ASC"

..execute the query

Are you familiar with the CONTAINS clause?  That may works?

Some info:

http://magni.grainger.uiuc.edu/lis450ds/FREETEXT.HTM

But wouldn't I get an error, if say only the Address was passed?  Attaching the OR to the statement would make it balk, right?

alorentz

Not familiar with the contains clause, but I am willing to check it out.

Lord_McFly's answer is probably you're best bet, used it the other day.  The left function gets rid of the OR

I am now getting the dreaded Too few parameters. Expected 1 error.  I know it is passing it because I have debugged the sql statement.  What gives?

strSQL = "SELECT FirstName, LastName, DOB, Address, HomePhone, DayTimePhone " &_
                 "FROM tblHousehold INNER JOIN tblMembers ON tblMembers.HouseholdID = tblHousehold.HouseholdID WHERE "

            If strFirstName <> "" then
                       strSQL = strSQL & "FirstName = '" & strFirstName & "' OR "
            End If

            If strLastName <> "" then
                       strSQL = strSQL & "LastName = '" & strLastName & "' OR "
            End If

            If datDOB <> "" then
                       strSQL = strSQL & "DATEDIFF('d',DOB,'" & datDOB & "') = 0 OR "
            End If

            If strAddress <> "" then
                       strSQL = strSQL & "Address = '" & strAddress & "' OR "
            End If

      strSQL = Left(strSQL,Len(strSQL) - 3) & "ORDER BY LastName ASC, FistName ASC, DOB ASC, Address ASC"

Do reponse.write strSQL to see what it writes so you can debug...

Use ASP to structure your SQL.

So have the basic structure at the end:

strSQL = "SELECT " & selectVar & " WHERE " & whereVar1 & whereVar2 & " ORDER BY " & orderbyVar1 & orderbyVar2 & ascOrDesc



So then before the query you can have code to determine what goes in the variables/SQL.

i.e.
whereVar1 = "[tblMembers].[LastName]='" & strLastName & "'"
whereVar2 = ""

If strFirstName <> "" then
      whereVar2 = " AND firstName = " & strFirstName
End If

@fairon - let's not make this more confusing than necessary... The idea would be to get it to work first as basic as possible.

I see you have made good head way, but i have a couple of comments.

First, if the code you posted is actually what you are using, you have a typo in your Order By statement (FistName  should be FirstName)

Second, you can do away with the need to strip the final or by change the where clause to:

 "FROM tblHousehold INNER JOIN tblMembers ON tblMembers.HouseholdID = tblHousehold.HouseholdID WHERE 1 <> 1 "

And each of the tests for search values would be changed to:

         If strFirstName <> "" then
                    strSQL = strSQL & " OR FirstName = '" & strFirstName & "'"
          End If

This also solves the problem if no search criteria are passed. Otherwise your where clause would be empty

Fairon suggestion is a very good one. A similar method would be replicate the build of the order by clause at the same time you build the where clause. in other words, the following:

         If strFirstName <> "" then
                    strSQL = strSQL & " OR FirstName = '" & strFirstName & "'"
                   strOrderby = strOrderby & ",FirstName "
          End If

So putting it all together, you get something like this:

strOrderBy = " Orderby 1"
strSQL = "SELECT FirstName, LastName, DOB, Address, HomePhone, DayTimePhone " &_
                 "FROM tblHousehold INNER JOIN tblMembers ON tblMembers.HouseholdID = tblHousehold.HouseholdID WHERE 1<>1 "

          If strFirstName <> "" then
                    strSQL = strSQL & " OR FirstName = '" & strFirstName & "'"
                    strOrderby = strOrderby & ", FirstName "
          End If

          If strLastName <> "" then
                    strSQL = strSQL & " OR LastName = '" & strLastName & "'"
                    strOrderby = strOrderby & ", LastName "
          End If

          If datDOB <> "" then
                    strSQL = strSQL & " OR DATEDIFF('d',DOB,'" & datDOB & "') = 0"
                    strOrderby = strOrderby & ", DOB"
          End If

          If strAddress <> "" then
                    strSQL = strSQL & " OR Address = '" & strAddress & "'"
                    strOrderby = strOrderby & ", Address "
          End If

     strSQL = strSQL & strOrderby

I also agree with alorentz. Do a response.write on strSQL after the last statment, but before you attempt to run it.

Then you can copy the entire string and paste it into whatever query engine is avalable for the database you are using.

Good luck

yeah maby you're right...

But I'd always find it easier to program using the scripting language rather than with SQL...

I guess that I should also point out that if the search criteria is empty, you would get no records. If instead you want to return all records and you want to specify a sort order if no records are returned, then you need to set up two defaults, one for the where clause, and one for the orderby clause.

something to the affect
If strLastName <> "" and strFirstName <> "" and datDOB <> "" and strAddress <> "" Then
   strSQL = StrSQL & " OR 1 = 1"
   strOrderBy = StrOrderby & "LastName, FirstName, DOB, Address"
End If

You should place this If statement directly after the other ones.

Also, if you want to have LastName before Firstname in your Order by statement when they both are supplied in the search criteria, switch the two related If statements above.

The only problem now (and correct me if I am wrong) but when I get to the sort order part  I am sorting the DOB on the actual DOB and not whether it came back as a hit on the search criteria.  

I know that is confusing, but say when i search on the following:

I enter:
LastName: Smith
DOB: 03/11/72

I get the right records, but the wrong sort order:

LastName        FirstName        DOB              Address
Smith                 David          01/26/93       1234 Any City
Smith                 Sarah         03/11/72        2345 Rhodes St.

When what I want is the closest match on top:

LastName        FirstName        DOB              Address
Smith                 Sarah         03/11/72        2345 Rhodes St.
Smith                 David          01/26/93       1234 Any City

I understand now. Thus the need for scoring.

Are you using MS Access as the datbase?

yes

It is still hanging, on the DOb part of the OrderBy statment.

OK, try this.

  If datDOB <> "" then
                    strSQL = strSQL & " OR DATEDIFF('d',DOB,'" & datDOB & "') = 0"
                    strOrderby = strOrderby & " + Cint([DOB]=#" & datDOB & "#) "

   End If

Worked like a charm.  Thanks to all of you for the help and guidance.