Crystal Report Small Change Needed

[Former Member]

Hi, Im using this code in a crystal report .I need small changes needed in this query.There is a filed named "[Invoice No. & Dt.]," in this query which is combining two fields , there is an alignment issue while printing this document.So that i decided to remove the docdate field from this query.How can i do this im getting too many errors while modifying the code.Please help m do this.

Thanks in advance

Nabil

convert(varchar,OVPM.DocDate,105) [Date], convert(varchar,OVPM.TaxDate,105) [taxDate],OVPM.[Address],VPM1.[CheckSum],Ovpm.PayToCode,vpm2.SumApplied , (select SeriesName from nnm1 where Series=OVPM.Series)+'/'+cast(OVPM.DocNum as nvarchar) [DocNum], isnull(OVPM.U_Remarks,'') [Comments],OVPM.CardName, VPM1.CheckNum,convert(varchar,VPM1.DueDate,105) [DueDate], isnull((select SUM(ReconSum) from ITR1 where ReconNum in (select OITR.ReconNum from ITR1 inner join OITR on OITR.ReconNum=ITR1.ReconNum where ITR1.SrcObjAbs=VPM2.DocEntry and  SrcObjTyp=VPM2.InvType and OITR.IsCard='C' and Isnull(InitObjAbs,'0')!={?DocKey@}) and SrcObjAbs =VPM2.DocEntry),0) Deduction, isnull((Select CASE when VPM2.InvType='18' then   (select Cast(isnull(NumAtCard,'') as varchar)+'/'+CONVERT(Varchar,DocDate,105) from OPCH where OPCH.DocEntry=VPM2.DocEntry) else   (select Cast(isnull(NumAtCard,'') as varchar)+'/'+CONVERT(Varchar,DocDate,105) from ORPC where ORPC.DocEntry=VPM2.DocEntry) end),'') [Invoice No. & Dt.], isnull((Select CASE when VPM2.InvType='18' then   (select DocTotal from OPCH where OPCH.DocEntry=VPM2.DocEntry) else   (select DocTotal from ORPC where ORPC.DocEntry=VPM2.DocEntry) end),0) [Invoice Amount], (SELECT OCHO.VendorName FROM OCHO WHERE OCHO.PmntNum={?DocKey@} and OCHO.TransRef=OVPM.DocNum) VendorName, (SELECT OCHO.[CheckSum] FROM OCHO WHERE OCHO.PmntNum={?DocKey@} and OCHO.TransRef=OVPM.DocNum) [CheckSum] from OVPM left join VPM2 on OVPM.DocEntry=VPM2.DocNum left join VPM1 on OVPM.DocEntry=VPM1.DocNum where OVPM.DocEntry={?DocKey@}

[kothandaraman_nagarajan]

Hi,

please try this:

convert(varchar,OVPM.DocDate,105) [Date], convert(varchar,OVPM.TaxDate,105) [taxDate],OVPM.[Address],VPM1.[CheckSum],Ovpm.PayToCode,vpm2.SumApplied , (select SeriesName from nnm1 where Series=OVPM.Series)+'/'+cast(OVPM.DocNum as nvarchar) [DocNum], isnull(OVPM.U_Remarks,'') [Comments],OVPM.CardName, VPM1.CheckNum,convert(varchar,VPM1.DueDate,105) [DueDate], isnull((select SUM(ReconSum) from ITR1 where ReconNum in (select OITR.ReconNum from ITR1 inner join OITR on OITR.ReconNum=ITR1.ReconNum where ITR1.SrcObjAbs=VPM2.DocEntry and  SrcObjTyp=VPM2.InvType and OITR.IsCard='C' and Isnull(InitObjAbs,'0')!={?DocKey@}) and SrcObjAbs =VPM2.DocEntry),0) Deduction, isnull((Select CASE when VPM2.InvType='18' then   (select Cast(isnull(NumAtCard,'') as varchar)+'/'+CONVERT(Varchar,DocDate,105) from OPCH where OPCH.DocEntry=VPM2.DocEntry) else   (select Cast(isnull(NumAtCard,'') as varchar) from ORPC where ORPC.DocEntry=VPM2.DocEntry) end),'') [Invoice No. & Dt.], isnull((Select CASE when VPM2.InvType='18' then   (select DocTotal from OPCH where OPCH.DocEntry=VPM2.DocEntry) else   (select DocTotal from ORPC where ORPC.DocEntry=VPM2.DocEntry) end),0) [Invoice Amount], (SELECT OCHO.VendorName FROM OCHO WHERE OCHO.PmntNum={?DocKey@} and OCHO.TransRef=OVPM.DocNum) VendorName, (SELECT OCHO.[CheckSum] FROM OCHO WHERE OCHO.PmntNum={?DocKey@} and OCHO.TransRef=OVPM.DocNum) [CheckSum] from OVPM left join VPM2 on OVPM.DocEntry=VPM2.DocNum left join VPM1 on OVPM.DocEntry=VPM1.DocNum where OVPM.DocEntry={?DocKey@}

Thanks