Solved

IIf...Else statement problem in SQL statement

Posted on 2004-04-23
28
1,094 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 10
  • 7
  • 6
  • +2
28 Comments
 
LVL 31

Expert Comment

by:alorentz
ID: 10900822
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
ID: 10900849
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
ID: 10900946
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

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

Expert Comment

by:Lord_McFly
ID: 10901003
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
ID: 10901088
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
ID: 10901148
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
ID: 10901167
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
ID: 10901178
Are you familiar with the CONTAINS clause?  That may works?
0
 
LVL 31

Expert Comment

by:alorentz
ID: 10901185
0
 

Author Comment

by:Jason Atherton
ID: 10901188
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
ID: 10901198
alorentz

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

0
 
LVL 31

Expert Comment

by:alorentz
ID: 10901209
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
ID: 10901717
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
 
LVL 31

Expert Comment

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

0
 

Expert Comment

by:fairon
ID: 10902327
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
ID: 10902370
@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
ID: 10902544
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
ID: 10902548
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
ID: 10902626
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
ID: 10903315
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
ID: 10903410
I understand now. Thus the need for scoring.

Are you using MS Access as the datbase?
0
 

Author Comment

by:Jason Atherton
ID: 10903444
yes
0
 
LVL 1

Accepted Solution

by:
hoenth earned 50 total points
ID: 10903613
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
ID: 10905481
It is still hanging, on the DOb part of the OrderBy statment.

0
 
LVL 1

Expert Comment

by:hoenth
ID: 10906996
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
ID: 10908251
Worked like a charm.  Thanks to all of you for the help and guidance.
0
 
LVL 1

Expert Comment

by:hoenth
ID: 10909069
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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…
I would like to start this tip/trick by saying Thank You, to all who said that this could not be done, as it forced me to make sure that it could be accomplished. :) To start, I want to make sure everyone understands the importance of utilizing p…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…

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