1

I have an Access front end with a SQL backend. I’m trying to add a couple of fields to a form. When I edit the working SQL statement for the Form_Open event and add the LEFT JOIN table I get the following error:

There has been an error.
3075: Syntax error (missing operator) in query expression ‘CS.[SegmentID] = S.[SegmentID] LEFT JOIN DATR_ContractEnviroFee AS CEF ON C.[ContractID] = CEF.[ContractID’.
Source: DAO.Database
Form_InitialFeeHelper.Form_Open

SQL Statement:

Set rst = db.OpenRecordset( _
"SELECT TotalNewSignFee AS NewInstall, OnContactCompanyId, ContractEntryID, " & _
"TotalMonthlyFee AS MonthlyFee, CEF.EnviroFee AS EnviroFeeAmt, CEF.Comment AS EnviroComment " & _
"NoEnviroFee As EnviroFee, Sponsor, TotalInitialFee AS InitialFee, " & _
"S.SegmentCode AS SegmentID, S.RouteName AS ROUTE, C.TotalInitialFeeBilled AS InitialFeeBilled, " & _
"S.DirectionCode AS DIRECTION, S.City AS CITY, S.StateCode, C.AssignedRep AS Rep, " & _
"C.[Free01] AS [Free01], C.[Free24] AS [Free24], C.[Free25] AS [Free25], C.[Disc01] AS [Disc01], " & _
"C.[DiscSF] AS [DiscSF], C.[DiscMF] AS [DiscMF], C.[FreeMonth01] AS [FreeMonth01], " & _
“C.[FreeMonth24] AS [FreeMonth24], C.[GiftCard] AS [GiftCard],” & _
"C.[FreeMonth25] AS [FreeMonth25], C.[Discount01] AS [Discount01], C.[DiscountSignFee] AS [DiscountSignFee], " & _
“C.[DiscountMoFee] AS [DiscountMoFee], C.[GiftCardAmount] AS [GiftCardAmount],” & _
"C.[FreeMonth01_Processed] AS [FreeMonth01_Processed], " & _
"C.[FreeMonth24_Processed] AS [FreeMonth24_Processed], C.[FreeMonth25_Processed] AS [FreeMonth25_Processed], " & _
"C.[Discount01_Processed] AS [Discount01_Processed], C.[DiscountSignFee_Processed] AS [DiscountSignFee_Processed], " & _
"C.[DiscountMoFee_Processed] AS [DiscountMoFee_Processed], C.[GiftCard_Processed] AS [GiftCard_Processed] " & _
"FROM (DATR_Contract AS C INNER JOIN DATR_ContractSegment AS CS ON C.ContractID = CS.ContractID) " & _
"INNER JOIN DAT_Segment AS S ON CS.[SegmentID] = S.[SegmentID] " & _
"LEFT JOIN DATR_ContractEnviroFee AS CEF ON C.[ContractID] = CEF.[ContractID] " & _
"WHERE C.ContractID = " & conId, dbOpenDynaset, dbSeeChanges)

Again, the statement was working fine until I tried to add the DATR_ContractEnviroFee table into the mix.

2 Spice ups
2

Those parenthesis look a little out of place in the beginning of your from clause. Are you sure those need to surround the first 2 tables?

FROM (DATR_Contract AS C INNER JOIN DATR_ContractSegment AS CS ON C.ContractID = CS.ContractID)

3

There also appears to be a comma missing after “CEF.Comment AS EnviroComment”.

4

I removed the parentheses and still get a syntax error.

5

Did you add the missing comma? Looks like that field was related to the new table you were trying to add.

6

Yes. Same error still. I even took the fields out of the query and left the table just to make sure that the error happens upon adding the table into the query. Can I not do a LEFT JOIN in VBA?

7

I’m not sure. Are you able to test just changing to INNER join, leaving the rest of the new part of the script as is?

8

Instead of putting in a string value for your sql, I would assign that to a variable so you can print it out (debug) to see how it evaluates. That’s where we’ll be able to see the error.

9

Your error shows a missing close bracket (]) even though your code snippet does not.

It’s an awfully long statement, and I think I remember reading somewhere Openrecordset has a string length limit for the query string using DAO.

It’s been a long time since I messed with DAO and VBA, so I could be mistaken.

10

WHERE C.ContractID = " & conId

What is conId and where does it come from? Looks like an unqualified reference at first glance.

11

The answer ended up being that I did not have the parentheses in the correct place for the table joins. I needed to do them the way they do in Access. In SQL you don’t need those parentheses.