Hi,
I have a piece of vb script which generates a sql query and then exports this to Microsoft Excel. I’m trying to format any date fields into a medium date, but keep getting a syntax error message. I’ve tried various combinations but just can’t seem to get this to work. I’ve copied the script below – can anyone help?
rsSql = “SELECT tblBill.Portfolio, tblBill.BillTitle AS [Bill Title], tblBill.YN_SuitableForStatementLegIntent AS SGI, tblBill.BillInfo_Priority AS Priority, ” & _
“tblBill.BillInfo_PolicyTheme AS Theme, [CommitteeProposed] & (Chr(10))+tlkpCabinetCommitteeDate.Comments ” & _
“AS Committee, Format([AIP_LatestAIPDate], & Chr(34) & “dd-mm-yyy” & Chr(34)) & (Chr(10))+[tlkpCabAIPDate].[AIPChangeComments] ” & _
“AS [AIP Date], Format([LatestSuppAIPDate], & Chr(34) & “dd-mm-yyy” & Chr(34)) & (Chr(10))+[tlkpSuppAIPDate].[SuppAIPComments] ” & _
“AS [Supp AIP Date], Format([LatestEDDate], & Chr(34) & “dd-mm-yyy” & Chr(34)) & (Chr(10))+[tlkpEDDate].[EDComments] AS ” & _
“[Exposure Draft], Format([LatestBACDate], & Chr(34) & “dd-mm-yyy” & Chr(34)) & (Chr(10))+[tlkpCabBACDate].[BACDateComments] AS [BAC Date], ” & _
“tblBill.[Cabinet or LCC?], tblBill.LatestParlWeek AS [Intro Date], tblBill.[Bill Comments], tblBill.YN_Issues, ” & _
“ParlWeeks.ParlWeekDates, tblBill.AIP_LatestAIPDate, tblBill.LatestSuppAIPDate, tblBill.LatestEDDate, tblBill.LatestBACDate ” & _
“FROM ((((((tblBill LEFT JOIN ParlWeeks ON tblBill.LatestParlWeek = ParlWeeks.ParlWeek) LEFT JOIN tlkpCabAIPDate ON tblBill.LatestAIPDateID = ” & _
“tlkpCabAIPDate.CabAIPDateID) LEFT JOIN tlkpSuppAIPDate ON tblBill.LatestSuppAIPDateID = tlkpSuppAIPDate.SuppAIPDateID) LEFT JOIN tlkpCabinetCommitteeDate ON ” & _
“tblBill.LatestCommitteeDateID = tlkpCabinetCommitteeDate.CabinetCommitteeDateID) LEFT JOIN tlkpCabBACDate ON tblBill.LatestBACDateID = ” & _
“tlkpCabBACDate.CabBACDateID) LEFT JOIN tlkpEDDate ON tblBill.LatestEDDateID = tlkpEDDate.EDDateID) LEFT JOIN tblPriority ON tblBill.BillInfo_Priority = ” & _
“tblPriority.Priority_Description ” & _
“GROUP BY tblBill.Portfolio, tblBill.BillTitle, tblBill.YN_SuitableForStatementLegIntent, tblBill.BillInfo_Priority, tblBill.BillInfo_PolicyTheme, ” & _
“Format([AIP_LatestAIPDate], & Chr(34) & “dd-mm-yyy” & Chr(34)) & (Chr(10))+[tlkpCabAIPDate].[AIPChangeComments], Format([LatestSuppAIPDate], & Chr(34) & “dd-mm-yyy” & Chr(34)) ” & _
“& (Chr(10))+[tlkpSuppAIPDate].[SuppAIPComments], Format([LatestEDDate], & Chr(34) & “dd-mm-yyy” & Chr(34)) & (Chr(10))+[tlkpEDDate].[EDComments], Format([LatestBACDate], & Chr(34) & “dd-mm-yyy” & Chr(34)) & ” & _
“(Chr(10))+[tlkpCabBACDate].[BACDateComments], tblBill.[Cabinet or LCC?], tblBill.LatestParlWeek, tblBill.[Bill Comments], tblBill.YN_Issues, ParlWeeks.ParlWeekDates, ” & _
“tblBill.AIP_LatestAIPDate, tblBill.LatestSuppAIPDate, tblBill.LatestEDDate, tblBill.LatestBACDate, [CommitteeProposed] & Chr(10)+tlkpCabinetCommitteeDate.Comments, ” & _
“tblBill.COAG, tblPriority.Priority_Number, tblBill.Portfolio, tblBill.BillTitle, tblBill.YN_RemovedFromProgram” & _
” HAVING (((tblBill.YN_RemovedFromProgram) = False))” & _
” ORDER BY tblPriority.Priority_Number, tblBill.Portfolio, tblBill.BillTitle;”
