Results 1 to 3 of 3
  1. #1
    Star Lounger
    Join Date
    Sep 2009
    Posts
    58
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi,

    I've created a query in Microsoft Access which works when I run it. I've now transferred this query into vb. I'm calling the sql query like this:

    Set rs = dbs.OpenRecordset(rsSql, dbOpenDynaset)

    with rs defined as a DAO.Recordset

    I've set rssql as:

    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] & IIf(IsNull(tlkpCabinetCommitteeDate.Comments),"",C hr(13) & Chr(10) & " & _
    "tlkpCabinetCommitteeDate.Comments) AS Committee, [AIP_LatestAIPDate] & IIf(IsNull(tlkpCabAIPDate.AIPChangeComments),"",Ch r(13) & Chr(10) & " & _
    "tlkpCabAIPDate.AIPChangeComments) AS [AIP Date], [LatestSuppAIPDate] & IIf(IsNull(tlkpSuppAIPDate.SuppAIPComments),"",Chr (13) & Chr(10) & " & _
    "tlkpSuppAIPDate.SuppAIPComments) AS [Supp AIP Date], [LatestEDDate] & IIf(IsNull(tlkpEDDate.EDComments),"",Chr(13) & Chr(10) & tlkpEDDate.EDComments) AS " & _
    "[Exposure Draft], [LatestBACDate] & IIf(IsNull(tlkpCabBACDate.BACDateComments),"",Chr( 13) & Chr(10) & tlkpCabBACDate.BACDateComments) AS [BAC Date], " & _
    "tblBill.[Cabinet or LCC?], tblBill.LatestParlWeek AS [Intro Date], tblBill.COAG AS [COAG Initiative], 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, " & _
    "[CommitteeProposed] & IIf(IsNull(tlkpCabinetCommitteeDate.Comments),"",C hr(13) & Chr(10) & tlkpCabinetCommitteeDate.Comments), [AIP_LatestAIPDate] & " & _
    "IIf(IsNull(tlkpCabAIPDate.AIPChangeComments),"",C hr(13) & Chr(10) & tlkpCabAIPDate.AIPChangeComments), [LatestSuppAIPDate] & " & _
    "IIf(IsNull(tlkpSuppAIPDate.SuppAIPComments),"",Ch r(13) & Chr(10) & tlkpSuppAIPDate.SuppAIPComments), [LatestEDDate] & " & _
    "IIf(IsNull(tlkpEDDate.EDComments),"",Chr(13) & Chr(10) & tlkpEDDate.EDComments), [LatestBACDate] & IIf(IsNull(tlkpCabBACDate.BACDateComments),"",Chr( 13) & " & _
    "Chr(10) & tlkpCabBACDate.BACDateComments), tblBill.[Cabinet or LCC?], tblBill.LatestParlWeek, tblBill.COAG, tblBill.[Bill Comments], tblBill.YN_Issues, " & _
    "ParlWeeks.ParlWeekDates, tblBill.AIP_LatestAIPDate, tblBill.LatestSuppAIPDate, tblBill.LatestEDDate, tblBill.LatestBACDate, tblPriority.Priority_Number, " & _
    "tblBill.Portfolio, tblBill.BillTitle, tblBill.YN_RemovedFromProgram" & _
    " HAVING (((tblBill.YN_RemovedFromProgram)=False))" & _
    " ORDER BY tblPriority.Priority_Number, tblBill.Portfolio, tblBill.BillTitle;"

    When I run the code, I get an error which states:

    Syntax error (missing operator) in query expression 'tblBill.YN_RemovedFromProgram HAVING (((tblBill.YN_RemovedFromProgram)=False)) ORDER BY tblPriority.Priority_Number'

    I can't work out why this works when running it through Access, but not through the vb call. Can anyone help?

    Cheers,
    Jason

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    If you want to include a double-quote " within a quoted string, you have to double it to "", otherwise VBA interprets it as the end of the string.
    Consequently, if you want to include the code for an empty string "" within a quoted string, you have to use """".

    So you have to replace the 10 occurrences of "" in rsSQL with """".

    Alternatively, you can replace expressions such as

    IIf(IsNull(tlkpCabinetCommitteeDate.Comments),"",C hr(13) & Chr(10) & tlkpCabinetCommitteeDate.Comments)

    with

    (Chr(13) & Chr(10))+tlkpCabinetCommitteeDate.Comments

    The + operator when used with strings usually has the same result as & but with one exception: if either of the operands is Null, + results in Null too.

    So "Jason" & Null = "Jason", but "Jason"+Null = Null.

  3. #3
    Star Lounger
    Join Date
    Sep 2009
    Posts
    58
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='795985' date='02-Oct-2009 17:47']If you want to include a double-quote " within a quoted string, you have to double it to "", otherwise VBA interprets it as the end of the string.
    Consequently, if you want to include the code for an empty string "" within a quoted string, you have to use """".

    So you have to replace the 10 occurrences of "" in rsSQL with """".

    Alternatively, you can replace expressions such as

    IIf(IsNull(tlkpCabinetCommitteeDate.Comments),"",C hr(13) & Chr(10) & tlkpCabinetCommitteeDate.Comments)

    with

    (Chr(13) & Chr(10))+tlkpCabinetCommitteeDate.Comments

    The + operator when used with strings usually has the same result as & but with one exception: if either of the operands is Null, + results in Null too.

    So "Jason" & Null = "Jason", but "Jason"+Null = Null.[/quote]


    HI Hans, thanks for your help, that fixed the problem!

    Cheers,
    Jason

Posting Permissions

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