JOIN expression not supported

**Rabbit** · Jul 25 '12, 10:14 PM

It would help to see the string that's actually submitted.

**Wesley Hader** · Jul 25 '12, 10:56 PM

Thank you for the quick response.

The SQL string is executed by an After Update event using

Code:

DoCmd.RunSQL strSQL

**Rabbit** · Jul 26 '12, 04:00 AM

I didn't mean how is it executed. I wanted to know what is in the variable right before that line of code.

**Wesley Hader** · Jul 26 '12, 01:03 PM

Here is the entire procedure. I apologize if I have not been clear.

Code:

Private Sub txtTicketNo_AfterUpdate()
On Error GoTo ERR_txtTicketNo_AfterUpdate

Dim Answer, strSQL, strConn As String
Dim TicketNo As Long
Dim Conn1 As New ADODB.Connection
Dim RsHdr, RsCust, RsCustAddr, RsHdrAddr, RsLookup As ADODB.Recordset

strConn = "ODBC;Dsn='';" & _
          "Driver={INFORMIX 3.81 32 BIT};" & _
          "Host=192.168.1.3;" & _
          "Server=dataline_725;" & _
          "Service=20000;" & _
          "Protocol=sesoctcp;" & _
          "Database=ecspro;" & _
          "UID=odbc;" & _
          "PWD=odbc"
          
Conn1.Open strConn

Set RsHdr = New ADODB.Recordset
RsHdr.CursorLocation = adUseClient
RsHdr.CursorType = adOpenKeyset
Set RsHdr = Conn1.Execute("SELECT * FROM informix.ioq_hdr")

Set RsCust = New ADODB.Recordset
RsCust.CursorLocation = adUseClient
RsCust.CursorType = adOpenKeyset
Set RsCust = Conn1.Execute("SELECT * FROM informix.cust")

Set RsHdrAddr = New ADODB.Recordset
RsHdrAddr.CursorLocation = adUseClient
RsHdrAddr.CursorType = adOpenKeyset
Set RsHdrAddr = Conn1.Execute("SELECT * FROM informix.ioq_hdr_addr")

Set RsCustAddr = New ADODB.Recordset
RsCustAddr.CursorLocation = adUseClient
RsCustAddr.CursorType = adOpenKeyset
Set RsCustAddr = Conn1.Execute("SELECT * FROM informix.cust_addr")

TicketNo = Nz(Me.txtTicketNo)

Set RsLookup = New ADODB.Recordset
RsLookup.CursorLocation = adUseClient
RsLookup.CursorType = adOpenKeyset
Set RsLookup = Conn1.Execute("SELECT * FROM informix.ioq_hdr WHERE ioqh_nbr='" & TicketNo & "';")

If RsLookup.BOF = True And RsLookup.EOF = True Then
 
    ' Record does not exist
       
    MsgBox "This is not a valid transaction number!", vbExclamation, "Invalid Transaction Number"
        
    Me.txtTicketNo = ""
            
    Me.txtTicketNo.SetFocus

    Exit Sub

End If

If IsNull(DLookup("Log_ID", "Logsheet_Table", "[Ticket_No]= " & TicketNo & "")) Then

   ' Record does not exist
    
        strSQL = "INSERT INTO Logsheet_Table ( Ticket_No, Date_Entered, Time_Entered, Ticket_Date, Cust_Code, Cust_Name, Cust_Address, Cust_AddressA, Cust_Address_2, " & _
                 "Cust_Address_2A, Cust_City, Cust_CityA, Cust_State, Cust_StateA, Cust_Zip_Code, Cust_Zip_CodeA, Ticket_Type, Status, Sort_Code ) " & _
                 "SELECT "" & RsHdr.ioqh_nbr & "", Date() AS Date_Entered, Time() AS Time_Entered, "" & RsHdr.ioqh_dt & "", "" & RsHdr.ioqh_cust_cd & "", "" & RsHdr.ioqh_cust_nm & "", " & _
                 """ & RsCustAddr.custa_frst_ln & "", "" & RsHdrAddr.ioqhe_frst_ln & "", "" & RsCustAddr.custa_scnd_ln & "", "" & RsHdrAddr.ioqhe_scnd_ln & "", "" & RsCustAddr.custa_city & "", " & _
                 "Trim(["" & RsHdr.ioqhe_city & ""]) AS Cust_City, "" & RsCustAddr.custa_state & "", "" & RsHdrAddr.ioqhe_state & "", "" & RsCustAddr.custa_zip_cd & "", "" & RsHdrAddr.ioqhe_zip_cd & "", " & _
                 """ & RsHdr.ioqh_type & "", ""OE"" AS Status, ""E"" AS Sort " & _
                 "FROM ((("" & RsHdr & "" INNER JOIN "" & RsCust & "" ON "" & RsHdr.ioqh_cust_cd & ""="" & RsCust.cust_cd & "") LEFT JOIN "" & RsHdrAddr & "" ON "" & RsHdr.ioqh_id & ""="" & RsHdrAddr.ioqh_id & "") INNER JOIN "" & RsCustAddr & "" ON "" & RsCust.cust_id & ""="" & RsCustAddr.cust_id & "")" & _
                 "WHERE ((("" & RsHdr.ioqh_nbr & "")=[Forms]![Frm_Logsheet_Today].[txtTicketNo]));"

    DoCmd.RunSQL strSQL
    
    Me.txtTicketNo = ""
       
    Form.Refresh
    
    Me.txtTicketNo.SetFocus
    
    Exit Sub
    
Else

    Answer = MsgBox("This ticket is already on today's logsheet!" & vbNewLine & "Would you like to add it anyway?", vbYesNo, "Duplicate Ticket No found")
    
    If Answer = vbYes Then
    
        stDocName = "Qry_Get_Ticket"
        DoCmd.OpenQuery stDocName
        
        Me.txtTicketNo = ""
           
        Form.Refresh
        
        Me.txtTicketNo.SetFocus
        
        Exit Sub
        
    Else
          
        Me.txtTicketNo = ""
           
        Form.Refresh
        
        Me.txtTicketNo.SetFocus

        Exit Sub

    End If
        
End If

Exit_txtTicketNo_AfterUpdate:

RsHdr.Close
RsCust.Close
RsHdrAddr.Close
RsCustAddr.Close
RsLookup.Close

Conn1.Close

Set RsHdr = Nothing
Set RsCust = Nothing
Set RsHdrAddr = Nothing
Set custhdr = Nothing
Set RsLookup = Nothing

Set Conn1 = Nothing

    Exit Sub
    
ERR_txtTicketNo_AfterUpdate:
    MsgBox Err.Description
    Resume Exit_txtTicketNo_AfterUpdate
    
End Sub

The Run-Time Error highlights line #75 on debug and states JOIN expression not supported.

**dsatino** · Jul 27 '12, 03:10 PM

SQL syntax is not the same across all database types. You need to check on the syntax that is used for the specific type of database that you are passing the SQL string to. In your case it tells you that the 'JOIN expression is not supported'. So start with that.

**Rabbit** · Jul 27 '12, 03:39 PM

@Wesley, that's not what I meant either. You already posted your code. I only need to know the actual value of the variable that is being submitted to the DBMS.

Take this code for example:

Code:

intValue = 5
sqlString = "SELECT *" & vbCrLf & "FROM someTable" & vbCrLf & "WHERE ID = " & intValue

When I say I want to see what's in the variable. I want to see this:

Code:

SELECT *
FROM someTable
WHERE ID = 5

And I don't mean what you think is in the variable or what you expect to be in the variable. I want to know the actual variable value as it exists in your computer's memory.

**dsatino** · Jul 27 '12, 03:47 PM

Actually, I think the first thing you should do is examine your use of quotes in building your strSQL variable.

**Rabbit** · Jul 27 '12, 04:08 PM

@dsatino, that's why I want to see what's in his variable. It will reveal any misquotations along with other SQL syntax errors.

**dsatino** · Jul 27 '12, 05:15 PM

Well, that's easy. He gets this:

Code:

INSERT INTO Logsheet_Table ( Ticket_No, Date_Entered, Time_Entered, Ticket_Date, Cust_Code, Cust_Name, Cust_Address, Cust_AddressA, Cust_Address_2, Cust_Address_2A, Cust_City, Cust_CityA, Cust_State, Cust_StateA, Cust_Zip_Code, Cust_Zip_CodeA, Ticket_Type, Status, Sort_Code ) SELECT " & RsHdr.ioqh_nbr & ", Date() AS Date_Entered, Time() AS Time_Entered, " & RsHdr.ioqh_dt & ", " & RsHdr.ioqh_cust_cd & ", " & RsHdr.ioqh_cust_nm & ", " & RsCustAddr.custa_frst_ln & ", " & RsHdrAddr.ioqhe_frst_ln & ", " & RsCustAddr.custa_scnd_ln & ", " & RsHdrAddr.ioqhe_scnd_ln & ", " & RsCustAddr.custa_city & ", Trim([" & RsHdr.ioqhe_city & "]) AS Cust_City, " & RsCustAddr.custa_state & ", " & RsHdrAddr.ioqhe_state & ", " & RsCustAddr.custa_zip_cd & ", " & RsHdrAddr.ioqhe_zip_cd & ", " & RsHdr.ioqh_type & ", "OE" AS Status, "E" AS Sort FROM (((" & RsHdr & " INNER JOIN " & RsCust & " ON " & RsHdr.ioqh_cust_cd & "=" & RsCust.cust_cd & ") LEFT JOIN " & RsHdrAddr & " ON " & RsHdr.ioqh_id & "=" & RsHdrAddr.ioqh_id & ") INNER JOIN " 
& RsCustAddr & " ON " & RsCust.cust_id & "=" & RsCustAddr.cust_id & ")WHERE (((" & RsHdr.ioqh_nbr & ")=[Forms]![Frm_Logsheet_Today].[txtTicketNo]));

Which is not a valid sql string in anyway of course, but I think it's better to make them work that out themselves by pointing them in the right direction.

**Wesley Hader** · Jul 31 '12, 01:25 PM

@Rabbit

Are you wanting to see what EACH variable is equal to or just the one in the WHERE statement?

Code:

WHERE (((" & RsHdr.ioqh_nbr & ")=726148));

@dsatino

I have always assumed that this was a syntax error probably related to quotations. This is the first time I have tried using a variable for a recordset in a SQL string so I thought there may be a chance that this is actually not supported, which is why I posed the question in the way that I did.

Either way, thank you both for your responses!

**NeoPa** · Jul 31 '12, 02:38 PM

Originally posted by Wesley Hader

Wesley Hader:
Here is the SQL String I am using.

Actually, that's not a SQL string at all Wesley. It's some VBA code that creates a SQL string using some literal values, but also some other variable values that are not available to us (as you haven't shared this information). I can see that other experts have also stumbled into this problem in the thread.

Please read Before Posting (VBA or SQL) Code. This will help everyone to help you in a more timely manner.

**dsatino** · Jul 31 '12, 06:38 PM

Ah, I see...I think.

I Wesley is trying to reference his recordset variables with his SQL statement. If that's the case, then no Wesley you can't do that. You can, however, use your recordsets to build a proper SQL string that you can run.

**Wesley Hader** · Jul 31 '12, 06:53 PM

Thank you dsatino! It seemed like a long shot when I first attempted it, but I thought I would give it a shot.

Can you point me in the right direction on using ADO recordsets in VBA, creating the VBA TEXT string, and using that to create the SQL string? My problem, I assume, will arise in trying to create the JOINs or what I am thinking of as the relationships between the recordsets.

I know my wording has not been up to par and that I did not provide enough information to make this an easy question to answer, but I appreciate the effort anyways!

**dsatino** · Jul 31 '12, 07:20 PM

Before you go down that road...

Do you have your DB linked to these remote tables?

JOIN expression not supported

JOIN expression not supported

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment