Results 1 to 5 of 5
  1. #1
    Star Lounger
    Join Date
    Sep 2009
    Posts
    58
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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;"

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Why do you want to format dates in a query? Excel recognizes dates from Access!
    And why do you include line feeds Chr(10) in the dates?

  3. #3
    Star Lounger
    Join Date
    Sep 2009
    Posts
    58
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by HansV View Post
    Why do you want to format dates in a query? Excel recognizes dates from Access!
    And why do you include line feeds Chr(10) in the dates?
    HI Hans,

    I'm using line feeds because I'm trying to join two fields - Eg, the [AIP_Date] field is actually a combination of the [LatestAIPDate] field and the [AIPChangeComments] field. Because of this, I need to format the dates before they go into Excel.

    Hope this makes sense.

    Cheers,
    Jason

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Thanks for the explanation. Try changing

    Format([AIP_LatestAIPDate], & Chr(34) & "dd-mm-yyy" & Chr(34)) & (Chr(10))+[tlkpCabAIPDate].[AIPChangeComments]

    to

    Format([AIP_LatestAIPDate], ""dd-mm-yyy"") & Chr(10)+[tlkpCabAIPDate].[AIPChangeComments]

    and similar for the others.

  5. #5
    Star Lounger
    Join Date
    Sep 2009
    Posts
    58
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by HansV View Post
    Thanks for the explanation. Try changing

    Format([AIP_LatestAIPDate], & Chr(34) & "dd-mm-yyy" & Chr(34)) & (Chr(10))+[tlkpCabAIPDate].[AIPChangeComments]

    to

    Format([AIP_LatestAIPDate], ""dd-mm-yyy"") & Chr(10)+[tlkpCabAIPDate].[AIPChangeComments]

    and similar for the others.
    Yes, that's working now - Thanks Hans, much appreciated.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •