Hi

I am trying to change IIf([Contacts].[APC]='XXXXX','Deleted','')in this query but don't know to change is SQL as db is transfering to SQL

here's Query

criteria = "SELECT Contacts.Contact, Customers.Customer, Customers.CID, IIf([Contacts].[APC]='XXXXX','Deleted','') AS Status FROM Customers INNER JOIN Contacts ON Customers.CID = Contacts.CID WHERE (Contacts.Contact Like '" & Me!SearchFilter & "*') AND (" & GetCondition("Reps", "rep") & ") ORDER BY Contacts.Contact, Customers.Customer, IIf([Contacts].[APC]='XXXXX','Deleted','');"


Thanks

Solution! :)
SELECT Contacts.Contact,
 Customers.Customer,
 Customers.CID,
 CASE WHEN [Contacts].[APC]='XXXXX' THEN 'Deleted' ELSE '' END AS Status
 FROM Customers
INNER JOIN Contacts
ON Customers.CID = Contacts.CID
WHERE (Contacts.Contact Like 'q%')
--AND (" & GetCondition("Reps", "rep") & ")
ORDER BY 1,2,4

I think that part will work. You want to change Contacts.APC to the string "deleted", otherwise display an empty string?

Also, if you are going to SQL Server, then you should change this to:

criteria = "SELECT Contacts.Contact, Customers.Customer, Customers.CID, IIf([Contacts].[APC]='XXXXX','Deleted','') AS Status FROM Customers INNER JOIN Contacts ON Customers.CID = Contacts.CID WHERE (Contacts.Contact Like '" & Me!SearchFilter & "%') AND (" & GetCondition("Reps", "rep") & ") ORDER BY Contacts.Contact, Customers.Customer, Status"

Note: Change the wildcard * to %, and omit the ";" at the end of the string. Also, in the ORDER BY clause, refer to the alias of the column, not the expression.

I think that will work.

An even better solution would be to build this as a view on the SQL Server since the server will optimize the query, and also reduce data on the wire.

The View would be:

"SELECT Contacts.Contact, Customers.Customer, Customers.CID, IIf([Contacts].[APC]='XXXXX','Deleted','') AS Status
FROM Customers INNER JOIN Contacts ON Customers.CID = Contacts.CID
ORDER BY Contacts.Contact, Customers.Customer, Status"

Then you would do this:

criteria = "WHERE [Contact] Like '" & Me!SearchFilter & "%') AND (" & GetCondition("Reps", "rep") & ")"

Then your SQL string would be:

sSQL = "SELECT * FROM vwMyViewOnSQLServer '" & criteria & "'"

Did that help any?




mmcdonal

Oh well, forget it then. =)

mmcdonal

Thanks very much for that help

Hi mmcdonal

In Addition, I found

IIF() doesn't recognize by SQL server so better to change IIF() in CASE and it help as well

Thanks