Join Expression Not Supported

[Drand]

Hi

I have the following code which exports excel files form a query to a folder on C:\. (with a lot of assistance from the forum!)

Private Sub cmdExpoirtMissingDataFiles_Click()

Dim rs As Recordset
Dim CreationMoment As String: CreationMoment = " Created on " & Format(Date, "ddmmyy") & " at " & Format(Time, "hhmmss")
Dim strSQL As String
Dim AndWhere As String
Dim MemberFirm As String

Dim xlApp As Object     'Excel.Application
Dim xlWB As Object      'Excel.Workbook
Dim xlSh As Object      'Excel.Worksheet
Dim sFilePath$

On Error GoTo ErrorHandler
    
    Set rs = CurrentDb.OpenRecordset("SELECT DISTINCT CountryCode, [Member Firm] FROM qryMissingData")
    Set xlApp = CreateObject("Excel.Application")   'New Excel.Application
    
    Do While Not rs.EOF
      
        ' this is the right alias
        
        AndWhere = "tblConsolRawData.CountryCode = " & rs!CountryCode
        ' access the sql string in the querydef and replace the semicolon with the where clause
        strSQL = Replace(CurrentDb.QueryDefs("QryMissingData").Sql, ";", " AND " & AndWhere)
      
        
        CurrentDb.CreateQueryDef "CountryFile", strSQL
        
        sFilePath = "C:\KPMG\Missing data Files\" & "Missing Data For Country Code  " & rs!CountryCode
        
        DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "CountryFile", sFilePath, True
        DoCmd.DeleteObject acQuery, "CountryFile"

        Set xlWB = xlApp.Workbooks.Open(sFilePath)
        Set xlSh = xlWB.Sheets(1)
    
        xlApp.Visible = False
        xlSh.Range("A1").Select
        xlSh.Range(xlApp.Selection, xlApp.Selection.End(-4121)).Select   '-4121 = xlDown
        xlSh.Range(xlApp.Selection, xlApp.Selection.End(-4161)).Select   '-4161 = xlToRight
        xlApp.Selection.FormatConditions.Add 2, , "=LEN(TRIM(A1))=0"     '2 = xlExpression
        xlApp.Selection.FormatConditions(xlApp.Selection.FormatConditions.Count).SetFirstPriority
        With xlApp.Selection.FormatConditions(1).Interior
            .PatternColorIndex = -4105                                '-4105 = xlAutomatic
            .ThemeColor = 4                                           '4 = xlThemeColorLight2
            .TintAndShade = 0.599963377788629
        End With
        xlApp.Selection.FormatConditions(1).StopIfTrue = False
        
        xlWB.Close True
        rs.MoveNext
    Loop

    Call CountFiles
    
cmdExportMissingDataFiles_Bye:
    On Error Resume Next
    rs.Close: Set rs = Nothing
    Set xlWB = Nothing
    Set xlSh = Nothing
    xlApp.Quit
    Set xlApp = Nothing
    Err.Clear
    Exit Sub

ErrorHandler:
    MsgBox Err.Number & vbCr & Err.Description
    ' make sure CountryFile is deleted anyway
    DoCmd.DeleteObject acQuery, "CountryFile"
    
    Resume cmdExportMissingDataFiles_Bye
  End Sub

This code worked well but I have changed the underlying query and it now fails at the line
"CurrentDb.CreateQueryDef "CountryFile", strSQL"

producing the error message "Join Expression Not Supported"

The sql for the underlying query is

SELECT tblDump.Id, tblConsolRawData.[Member Firm], tblConsolRawData.[Survey Methodology (Legacy Anonymous / Linked Data)], tblConsolRawData.GPID, tblConsolRawData.[First Name], tblConsolRawData.[Last Name], tblConsolRawData.[Email Address], tblConsolRawData.CountryCode, tblConsolRawData.[Global Job Level], tblConsolRawData.[Global Function], tblConsolRawData.[Employment Type], tblConsolRawData.Age, tblConsolRawData.[Full-time/Part-time], tblConsolRawData.Gender, tblConsolRawData.[Tenure (Length of Service)], tblConsolRawData.[Is a High Performer], tblConsolRawData.[Is a Performance Manager], tblConsolRawData.[Is a People Leader], tblConsolRawData.[Is Client Facing], tblConsolRawData.[Has Taken Parental Leave in the Past 3 Years], tblConsolRawData.[Recently Promoted (last 24 months)], tblConsolRawData.[Cost Centre], tblConsolRawData.[Mystery Code], tblConsolRawData.[Function L1], tblConsolRawData.[Function L2], tblConsolRawData.[Function L3], tblConsolRawData.[Function L4], tblConsolRawData.[Function L5], tblConsolRawData.[Function L6], tblConsolRawData.[Function L7], tblConsolRawData.[Function L8], tblConsolRawData.[Function L9], tblConsolRawData.[Function L10], tblConsolRawData.[Function L11], tblConsolRawData.[Function L12], tblConsolRawData.[Mystery Code1], tblConsolRawData.[Location L1], tblConsolRawData.[Location L2], tblConsolRawData.[Location L3], tblConsolRawData.[Location L4], tblConsolRawData.[Location L5], tblConsolRawData.[Location L6], tblConsolRawData.[Location L7], tblConsolRawData.[Location L8], tblConsolRawData.[Mystery Code2], tblConsolRawData.[Market L1], tblConsolRawData.[Market L2], tblConsolRawData.[Market L3], tblConsolRawData.[Market L4], tblConsolRawData.[Market L5], tblConsolRawData.[Market L6], tblConsolRawData.[Market L7], tblConsolRawData.[Market L8], tblConsolRawData.[Country Custom Demographic 1], tblConsolRawData.[Country Custom Demographic 2], tblConsolRawData.[Country Custom Demographic 3], tblConsolRawData.[Country Custom Demographic 4]
FROM tblDump INNER JOIN tblConsolRawData ON (tblDump.CountryCode = tblConsolRawData.CountryCode) AND (tblDump.Id = tblConsolRawData.ID);

I have read a lot about bracketing to solve this problem but have been unable to fix this.

Could someone please tell me what is wrong with this.

Many thanks

 

[plog]

First, what changes did you make? Those are the most likely the culprits. After that, I'm finding 3 possibilities when I google that error message:

1. Memo field in JOIN. Access doesn't like when you join on a memo field. Is CountryCode a memo field in one of those sources?

2. Character limit. I'm getting conflicting limits in the characters you can use in an SQL statement inside VBA, but having a query that is too larrge might be the issue.

3. Parenthesis in the ON. I am also seeing that Access only like the ON to include one set of parenthesis, you have 2.

I would test #3 first. Change your ON to this and see if it works:

...ON (tblDump.CountryCode = tblConsolRawData.CountryCode AND tblDump.Id = tblConsolRawData.ID);

 

[Drand]

Thanks for your response.

The original underlying query only ran on one table. It is now extracting data from 2 joined tables. The new table is a set of rules surrounding data requirements.

1.There are no memo fields.
2. I don't think character limits are the issue here. The query runs as stands. It is only within the exporting code that I run into the problem.
3. I tried the ...On as suggested but the same error occurs.

 

[The_Doc_Man]

My first thought would be to put a breakpoint just after your line:

strSQL = Replace(CurrentDb.QueryDefs("QryMissingData").Sql, ";", " AND " & AndWhere)

Check strSQL to see what it contains after that line has been executed.

 

[Pat Hartman]

I have read a lot about bracketing to solve this problem but have been unable to fix this.

If you can't type SQL without thinking about it, your best bet is to start with the QBE, at least for the select clause and the joins. Once that works, you can add the criteria and either leave the query as a querydef or switch to SQL view and copy the string and paste it into VBA. There are tools that help with formatting the string. I don't use embedded SQL, so I don't need them frequently enough to bother keeping a link handy.

 

[Drand]

My first thought would be to put a breakpoint just after your line:

strSQL = Replace(CurrentDb.QueryDefs("QryMissingData").Sql, ";", " AND " & AndWhere)

Check strSQL to see what it contains after that line has been executed.

This is what I am getting

SELECT tblDump.Id, tblConsolRawData.[Member Firm], tblConsolRawData.[Survey Methodology (Legacy Anonymous / Linked Data)], tblConsolRawData.GPID, tblConsolRawData.[First Name], tblConsolRawData.[Last Name], tblConsolRawData.[Email Address], tblConsolRawData.CountryCode, tblConsolRawData.[Global Job Level], tblConsolRawData.[Global Function], tblConsolRawData.[Employment Type], tblConsolRawData.Age, tblConsolRawData.[Full-time/Part-time], tblConsolRawData.Gender, tblConsolRawData.[Tenure (Length of Service)], tblConsolRawData.[Is a High Performer], tblConsolRawData.[Is a Performance Manager], tblConsolRawData.[Is a People Leader], tblConsolRawData.[Is Client Facing], tblConsolRawData.[Has Taken Parental Leave in the Past 3 Years], tblConsolRawData.[Recently Promoted (last 24 months)], tblConsolRawData.[Cost Centre], tblConsolRawData.[Mystery Code], tblConsolRawData.[Function L1], tblConsolRawData.[Function L2], tblConsolRawData.[Function L3], tblConsolRawData.[Function L4], tblConsolRawData.[Function L5], tbl
ConsolRawData.[Function L6], tblConsolRawData.[Function L7], tblConsolRawData.[Function L8], tblConsolRawData.[Function L9], tblConsolRawData.[Function L10], tblConsolRawData.[Function L11], tblConsolRawData.[Function L12], tblConsolRawData.[Mystery Code1], tblConsolRawData.[Location L1], tblConsolRawData.[Location L2], tblConsolRawData.[Location L3], tblConsolRawData.[Location L4], tblConsolRawData.[Location L5], tblConsolRawData.[Location L6], tblConsolRawData.[Location L7], tblConsolRawData.[Location L8], tblConsolRawData.[Mystery Code2], tblConsolRawData.[Market L1], tblConsolRawData.[Market L2], tblConsolRawData.[Market L3], tblConsolRawData.[Market L4], tblConsolRawData.[Market L5], tblConsolRawData.[Market L6], tblConsolRawData.[Market L7], tblConsolRawData.[Market L8], tblConsolRawData.[Country Custom Demographic 1], tblConsolRawData.[Country Custom Demographic 2], tblConsolRawData.[Country Custom Demographic 3], tblConsolRawData.[Country Custom Demographic 4]
FROM tblDump INNER JOIN tblConsolRawData ON (tblDump.CountryCode = tblConsolRawData.CountryCode) AND (tblDump.Id = tblConsolRawData.ID) AND tblConsolRawData.CountryCode = 4

 

[ebs17]

Here's what it looks like (brackets are superfluous) and doesn't work in the JOIN expression:

...
FROM tblDump INNER JOIN tblConsolRawData
ON tblDump.CountryCode = tblConsolRawData.CountryCode
AND tblDump.Id = tblConsolRawData.ID
AND tblConsolRawData.CountryCode = 4

With WHERE it will work:

...
FROM tblDump INNER JOIN tblConsolRawData
ON tblDump.CountryCode = tblConsolRawData.CountryCode
AND tblDump.Id = tblConsolRawData.ID
WHERE tblConsolRawData.CountryCode = 4

When composing SQL statements using VBA, it would be good to know what the working statement should look like, because that's how it must come out at the end.

 

[Drand]

Here's what it looks like (brackets are superfluous) and doesn't work in the JOIN expression:

...
FROM tblDump INNER JOIN tblConsolRawData
ON tblDump.CountryCode = tblConsolRawData.CountryCode
AND tblDump.Id = tblConsolRawData.ID
AND tblConsolRawData.CountryCode = 4

With WHERE it will work:

...
FROM tblDump INNER JOIN tblConsolRawData
ON tblDump.CountryCode = tblConsolRawData.CountryCode
AND tblDump.Id = tblConsolRawData.ID
WHERE tblConsolRawData.CountryCode = 4

When composing SQL statements using VBA, it would be good to know what the working statement should look like, because that's how it must come out at the end.

Ok Thanks

I understand what you mean but I am unsure how to do this as the code is actually creating the qryDefs

 

[ebs17]

You say: If I knew what I'm doing ...

' bad
AndWhere = "tblConsolRawData.CountryCode = " & rs!CountryCode

' better
AndWhere = " WHERE tblConsolRawData.CountryCode = " & rs!CountryCode

If I know what should come out as a result (in this case an SQL statement), then I can compare this goal with what I have achieved so far with my VBA actions, and I am able to correct discrepancies and errors.
Can you look, compare and correct?

 

[Drand]

You say: If I knew what I'm doing ...

' bad
AndWhere = "tblConsolRawData.CountryCode = " & rs!CountryCode

' better
AndWhere = " WHERE tblConsolRawData.CountryCode = " & rs!CountryCode

If I know what should come out as a result (in this case an SQL statement), then I can compare this goal with what I have achieved so far with my VBA actions, and I am able to correct discrepancies and errors.
Can you look, compare and correct?

Sorry, I am trying to learn SQL but am only a beginner at it.

I replaced the AndWhere as suggested and I now get "Syntax Error (Missing Operator) in query expression

 

[ebs17]

Sorry, I am trying to learn ...

Then you look again at what is in the string variable:

Debug.Print strSQL

... and compare again.

You can repeat this as often as you like (without being told).

 

[Drand]

This is now showing ...................And WHERE tblConsolRawData.CountryCode = 4

 

[ebs17]

This is now showing ................... And WHERE tblConsolRawData.CountryCode = 4

What is your opinion on this when you open both eyes? Of course, the "AND" has to go out with the Replace. Think something along, don't just copy blindly.

 

[Drand]

This is now showing ................... And WHERE tblConsolRawData.CountryCode = 4

What is your opinion on this when you open both eyes?

Obviously the "and" is a problem. I did try to delete this from the code initially but had trouble including the syntax surrounding "andwhere".

I have now sorted that out and the code is running correctly.

Thank you for your help and patience.