Jason Atherton
asked on
IIf...Else statement problem in SQL statement
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].[DaytimePho ne], " _
& "(IIf([tblMembers].[LastNa me]=" & strLastName & ",1,0)+" _
& "IIf([tblMembers].[FirstNa me]=" & strFirstName & ",1,0)+" _
& "IIf([tblMembers].[DOB]=" & datDOB & ",1,0)+" _
& "IIf([tblHousehold].[Addre ss]=" & strAddress & ",1,0) AS bytHits) " _
& "FROM tblHousehold INNER JOIN tblMembers ON " _
& "[tblHousehold].[Household ID]=[tblMe mbers].[Ho useholdId] " _
& "WHERE " _
& "[tblMembers].[LastName]=' " & strLastName & "' Or [tblMembers].[FirstName]=' " & strFirstName & "' " _
& "Or [tblMembers].[DOB]='" & datDOB & "' Or [tblHousehold].[Address]=' " & strAddress & "' "
strSQL = strSQL & " ORDER BY IIf(([tblMembers].[LastNam e]='" & strLastName & "'),1,0)+IIf(([tblMembers] .[FirstNam e]='" & strFirstName & "'),1,0)+" _
& "IIf(([tblMembers].[DOB]=' " & datDOB & "'),1,0)+IIf(([tblHousehol d].[Addres s]='" & strAddress & "'),1,0) DESC;"
strSQL = "SELECT [tblMembers].[LastName], [tblMembers].[FirstName], [tblMembers].[DOB], " _
& "[tblHousehold].[Address],
& "(IIf([tblMembers].[LastNa
& "IIf([tblMembers].[FirstNa
& "IIf([tblMembers].[DOB]=" & datDOB & ",1,0)+" _
& "IIf([tblHousehold].[Addre
& "FROM tblHousehold INNER JOIN tblMembers ON " _
& "[tblHousehold].[Household
& "WHERE " _
& "[tblMembers].[LastName]='
& "Or [tblMembers].[DOB]='" & datDOB & "' Or [tblHousehold].[Address]='
strSQL = strSQL & " ORDER BY IIf(([tblMembers].[LastNam
& "IIf(([tblMembers].[DOB]='
The use of all the IIF is highly unecessary. You can do all you need to by just extending the WHERE clause.
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].[DaytimePho ne], " _
& "(IIf([tblMembers].[LastNa me]=" & strLastName & ",1,0)+" _
& "IIf([tblMembers].[FirstNa me]=" & strFirstName & ",1,0)+" _
& "IIf([tblMembers].[DOB]=" & datDOB & ",1,0)+" _
& "IIf([tblHousehold].[Addre ss]=" & strAddress & ",1,0) AS bytHits) " _
& "FROM tblHousehold INNER JOIN tblMembers ON " _
& "[tblHousehold].[Household ID]=[tblMe mbers].[Ho useholdId] " _
& "WHERE " _
& "[tblMembers].[LastName]=' " & strLastName & "' Or [tblMembers].[FirstName]=' " & strFirstName & "' " _
& "Or [tblMembers].[DOB]='" & datDOB & "' Or [tblHousehold].[Address]=' " & strAddress & "' "
strSQL = strSQL & " ORDER BY IIf(([tblMembers].[LastNam e]='" & strLastName & "'),1,0)+IIf(([tblMembers] .[FirstNam e]='" & strFirstName & "'),1,0)+" _
& "IIf(([tblMembers].[DOB]=' " & datDOB & "'),1,0)+IIf(([tblHousehol d].[Addres s]='" & strAddress & "'),1,0) DESC;"
Response.write "SQL: " & strSQL
Then see where the missing ' is...
strSQL = "SELECT [tblMembers].[LastName], [tblMembers].[FirstName], [tblMembers].[DOB], " _
& "[tblHousehold].[Address],
& "(IIf([tblMembers].[LastNa
& "IIf([tblMembers].[FirstNa
& "IIf([tblMembers].[DOB]=" & datDOB & ",1,0)+" _
& "IIf([tblHousehold].[Addre
& "FROM tblHousehold INNER JOIN tblMembers ON " _
& "[tblHousehold].[Household
& "WHERE " _
& "[tblMembers].[LastName]='
& "Or [tblMembers].[DOB]='" & datDOB & "' Or [tblHousehold].[Address]='
strSQL = strSQL & " ORDER BY IIf(([tblMembers].[LastNam
& "IIf(([tblMembers].[DOB]='
Response.write "SQL: " & strSQL
Then see where the missing ' is...
ASKER
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?
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?
ASKER
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(con dition,tru e,false)
I'm just trying to organise you query using the correct syntax.
IIF(condition,true,false)
and if nested...
IFF(condition,true,IIF(con
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/19 72') = 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.
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/19
Address = 'MyAddress'
Can you describe in more detail what you expect the query to do and how you expect them to be sorted - cheers.
ASKER
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...
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
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?
ASKER
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?
ASKER
alorentz
Not familiar with the contains clause, but I am willing to check it out.
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
ASKER
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"
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
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]='
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
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...
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.
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.
ASKER
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 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?
Are you using MS Access as the datbase?
ASKER
yes
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
If datDOB <> "" then
strSQL = strSQL & " OR DATEDIFF('d',DOB,'" & datDOB & "') = 0"
strOrderby = strOrderby & " + Cint([DOB]=#" & datDOB & "#) "
End If
ASKER
Worked like a charm. Thanks to all of you for the help and guidance.
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.
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.