Syntax error (missing operator)

Topic

New Reply

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),””,Chr(13) & Chr(10) & ” & _
“tlkpCabinetCommitteeDate.Comments) AS Committee, [AIP_LatestAIPDate] & IIf(IsNull(tlkpCabAIPDate.AIPChangeComments),””,Chr(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),””,Chr(13) & Chr(10) & tlkpCabinetCommitteeDate.Comments), [AIP_LatestAIPDate] & ” & _
“IIf(IsNull(tlkpCabAIPDate.AIPChangeComments),””,Chr(13) & Chr(10) & tlkpCabAIPDate.AIPChangeComments), [LatestSuppAIPDate] & ” & _
“IIf(IsNull(tlkpSuppAIPDate.SuppAIPComments),””,Chr(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

Reply | Quote

Replies

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),””,Chr(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.

Reply | Quote

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),””,Chr(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.

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

Cheers,
Jason

Reply | Quote