Solved

IIf...Else statement problem in SQL statement

Posted on 2004-04-23
28
1,078 Views
Last Modified: 2008-01-16
I am attempting to use the following SQL statement in my asp page to produce a ordered by search results recordset. I pass the recordset to a page to list results using a function. I am hanging on this SQL problem. It keeps telling me I am 'Syntax error (missing operator) in query expression '. Can someone give me some advice or a good place to look for an answer?

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;"
0
Comment
Question by:Jason Atherton
  • 10
  • 7
  • 6
  • +2
28 Comments
 
LVL 31

Expert Comment

by:alorentz
Comment Utility
The use of all the IIF is highly unecessary.  You can do all you need to by just extending the WHERE clause.

0
 
LVL 31

Expert Comment

by:alorentz
Comment Utility
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...
0
 

Author Comment

by:Jason Atherton
Comment Utility
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?
0
 

Author Comment

by:Jason Atherton
Comment Utility
oops, sorry you posted again before I submitted my response to your first response.  Sorry
0
 
LVL 6

Expert Comment

by:Lord_McFly
Comment Utility
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.
0
 
LVL 6

Expert Comment

by:Lord_McFly
Comment Utility
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.
0
 

Author Comment

by:Jason Atherton
Comment Utility
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...
0
 
LVL 6

Expert Comment

by:Lord_McFly
Comment Utility
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
0
 
LVL 31

Expert Comment

by:alorentz
Comment Utility
Are you familiar with the CONTAINS clause?  That may works?
0
 
LVL 31

Expert Comment

by:alorentz
Comment Utility
0
 

Author Comment

by:Jason Atherton
Comment Utility
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?
0
 

Author Comment

by:Jason Atherton
Comment Utility
alorentz

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

0
 
LVL 31

Expert Comment

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

Author Comment

by:Jason Atherton
Comment Utility
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"
0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 31

Expert Comment

by:alorentz
Comment Utility
Do reponse.write strSQL to see what it writes so you can debug...

0
 

Expert Comment

by:fairon
Comment Utility
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
0
 
LVL 31

Expert Comment

by:alorentz
Comment Utility
@fairon - let's not make this more confusing than necessary... The idea would be to get it to work first as basic as possible.
0
 
LVL 1

Expert Comment

by:hoenth
Comment Utility
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
0
 

Expert Comment

by:fairon
Comment Utility
yeah maby you're right...

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

Expert Comment

by:hoenth
Comment Utility
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.
0
 

Author Comment

by:Jason Atherton
Comment Utility
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



0
 
LVL 1

Expert Comment

by:hoenth
Comment Utility
I understand now. Thus the need for scoring.

Are you using MS Access as the datbase?
0
 

Author Comment

by:Jason Atherton
Comment Utility
yes
0
 
LVL 1

Accepted Solution

by:
hoenth earned 50 total points
Comment Utility
Ok, here is the trick,

We will need to revise the sql code slightly. Basically, we are creating a numeric score based on a bunch of boolean comparisons. The boolean values are converted to integers and added together. Whenever there is a match for one of the critieria, a -1 is added to the Orderby value. Access uses -1 to indicate true. So the best score is a -4, if you used all 4 criteria.

The Orderby statement would look something like this: Order by 0 + Cint([FirstName]='Smith') + Cint([Address]='123 MyStreet')

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


          If strLastName <> "" then
                    strSQL = strSQL & " OR LastName = '" & strLastName & "'"
                    strOrderby = strOrderby & " + Cint([LastName= '" & strLastName & "') "
          End If


          If strFirstName <> "" then
                    strSQL = strSQL & " OR FirstName = '" & strFirstName & "'"
                    strOrderby = strOrderby & " + Cint([FirstName]='" & strFirstName & "') "
          End If

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

          End If

          If strAddress <> "" then
                    strSQL = strSQL & " OR Address = '" & strAddress & "'"
                    strOrderby = strOrderby & " + Cint([Address ]='" & strAddress & "') "
          End If

     strSQL = strSQL & strOrderby

0
 

Author Comment

by:Jason Atherton
Comment Utility
It is still hanging, on the DOb part of the OrderBy statment.

0
 
LVL 1

Expert Comment

by:hoenth
Comment Utility
OK, try this.

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

   End If
0
 

Author Comment

by:Jason Atherton
Comment Utility
Worked like a charm.  Thanks to all of you for the help and guidance.
0
 
LVL 1

Expert Comment

by:hoenth
Comment Utility
Your very welcome
One last comment. The above will sort by the score. But if you have a tie, then there is no further defined sort order.

To defeat this, you could merge the earlier sort order definition with the scoring definition, appending the fields by which you want to sort if there is a tie after the score. You would need to build a second Orderby string using the field names, as we previously had.

Then append that after the score orderby string.

the the order by computed string would look like: order by 0 + Cint([FirstName]='Smith') + Cint([Address]='123 MyStreet'), FirstName, Address

Good luck, and thanks for the points.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Update static table with two columns and multiple rows... 15 81
CSS Question.. 3 72
Pass through dll 2 32
Error viewing ASP page 12 58
Hello, all! I just recently started using Microsoft's IIS 7.5 within Windows 7, as I just downloaded and installed the 90 day trial of Windows 7. (Got to love Microsoft for allowing 90 days) The main reason for downloading and testing Windows 7 is t…
Have you ever needed to get an ASP script to wait for a while? I have, just to let something else happen. Or in my case, to allow other stuff to happen while I was murdering my MySQL database with an update. The Original Issue This was written…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now