quote:

---

Originally posted by jimtimber

Hi,

I have set a parameter for a query (SELECT DISTINCT SWSurname from Tbl_Agents ORDER BY SWSurname) which works. However, we have 2 surnames that have an ' in the name (e.g. D'Costa, O'Donnell) which brings up the error message "SyntaxErrorException: Syntax error: Missing operand after 'Costa' operator.".

Any ideas how to fix this would be great! :)
Thanks.
J






Jim

---

SELECT SWSurname FROM Tbl_Agents
WHERE SWSurname IN ('D''Costa','O''Donnell');

quote:

---

Originally posted by jimtimber

Hi James K, thanks for your response. What you did worked but it only brought through those 2 names. We have a list of about 300 SWs that this parameter turns into a drop down box. So I need to include what you suggested, alongside all the other names that don't have an ' in the name.

Thanks again!

Jim

---

 SELECT REPLACE(SWSurname,'''','''''') AS SWSurname FROM Tbl_Agents

 SELECT TOP (100) PERCENT
 TbP.FamiliesAgency ,
 TbC.ChildrenID ,
 TbC.AgencyCode ,
 TbA.AgencyName ,
 TSW.SocialWorkerID ,
 TSW.SWFirstname ,
 TSW.SWSurname ,
 TbC.ChildsSurname ,
 TbC.ChildsFirstName
 TbC.ChildsDateOfBirth AS DOB ,
 TbC.ChildsAgeYears ,
 dbo.Tbl_Families.ApplicantCode AS [FC(s)] ,
 TbC.TypeOfPlacement ,
 TbP.DateOfPlacement ,
 TbP.DatePlacementFinished,
 CASE
 WHEN ChildsAgeYears >= '16' THEN 'N/A'
 ELSE
 CASE
 WHEN ChildsAgeYears < '16'
 AND Vw_MaxChildLacPlan.Expr1 > Tbl_Placements.DateOfPlacement THEN CONVERT(VARCHAR(10), Vw_MaxChildLacPlan.Expr1, 103)
 ELSE NULL
 END
 END AS [LAC Care Plan],
 CASE
 WHEN ChildsAgeYears < '16' THEN 'N/A'
 ELSE
 CASE
 WHEN ChildsAgeYears >= '16'
 AND Vw_JW_01.Expr1 > Tbl_Placements.DateOfPlacement THEN CONVERT(VARCHAR(10), Vw_JW_01.Expr1, 103)
 ELSE NULL
 END
 END AS [Pathway Plan],
 CASE
 WHEN
 (
 SELECT MAX(ChildrenActionDate) AS Expr1
 FROM TbCProgress
 WHERE (
 ChildrenAction = N'Placement Plan')
 AND (
 ChildrenID = TbC.ChildrenID)) >=  TbP.DateOfPlacement THEN
 (
 SELECT MAX(ChildrenActionDate) AS Expr1
 FROM TbCProgress
 WHERE (
 ChildrenAction = N'Placement Plan')
 AND (
 ChildrenID = TbC.ChildrenID))
 ELSE NULL
 END AS [Placement Plan],
 (
 SELECT MAX(ChildrenActionDate) AS Expr1
 FROM TbCProgress
 WHERE (
 ChildrenAction = N'Review')
 AND (
 ChildrenID = TbC.ChildrenID)) AS [Last Review],
 (
 SELECT MAX(ChildrenActionDate) AS Expr1
 FROM TbCProgress AS Tbl_ChildrenProgress_1
 WHERE (
 ChildrenAction = N'Initial Health Assessment received')
 AND (
 ChildrenID = TbC.ChildrenID)) AS [Health Assessment],
 CASE
 WHEN ChildsAgeYears < '4' THEN 'N/A'
 ELSE
 CASE
 WHEN ChildsAgeYears >= '4' THEN CONVERT(VARCHAR(10),
 (
 SELECT MAX(ChildrenActionDate) AS Expr1
 FROM TbCProgress
 WHERE (
 ChildrenAction = N'Personal Education Plan (PEP) / Individual Education Plan (IEP)')
 AND (
 ChildrenID = TbC.ChildrenID)), 103)
 ELSE NULL
 END
 END AS IndivPeps,
 dbo.tbll_FamilyPlacementWorker.FpwFirstname AS LASWFirstName,
 dbo.tbll_FamilyPlacementWorker.FpwSurname AS LASWSurname
FROM dbo.Tbl_Families
INNER JOIN dbo.Tbl_Placements As TbP
ON dbo.Tbl_Families.FamiliesID =  TbP.FamiliesID
FULL OUTER JOIN dbo.Tbll_SocialWorker As TSW
ON dbo.Tbl_Families.SocialWorkerID = TSW.SocialWorkerID
FULL OUTER JOIN dbo.Tbl_Children As Tbc
FULL OUTER JOIN dbo.tbll_FamilyPlacementWorker
ON TbC.FpwID = dbo.tbll_FamilyPlacementWorker.FpwID
FULL OUTER JOIN dbo.Tbll_Agents AS TbA
ON TbC.AgencyCode = TbA.AgencyCode
ON  TbP.ChildrenID = TbC.ChildrenID
FULL OUTER JOIN dbo.Vw_MaxChildLacPlan
ON TbC.ChildrenID = dbo.Vw_MaxChildLacPlan.ChildrenID
FULL OUTER JOIN dbo.Vw_JW_01
ON TbC.ChildrenID = dbo.Vw_JW_01.ChildrenID
FULL OUTER JOIN dbo.Vw_JW_02
ON TbC.ChildrenID = dbo.Vw_JW_02.ChildrenID
WHERE (
  TbP.DateOfPlacement IS NOT NULL)
AND (
  TbP.DatePlacementFinished IS NULL)
AND (
 NOT (
  TbP.TypeOfPlacement = 'respite house'))
AND (
 NOT (
  TbP.TypeOfPlacement LIKE '%Respite House - INTERNAL'))
ORDER BY TbC.ChildsSurname