Yes, it has been a while.......
OK,
Originally Posted by
mindbender
This is the sql I have running from the export button. I am getting a runtime 3131 error Syntax error in FROM clause and I cant figure it out.
Looking at the "FROM" clause in the query in the code in the previous post, there is 1 opening parenthesis and 2 closing parenthesizes.
Code:
& "FROM ([USED-AECS_BASE_SO_NO] LEFT JOIN [USED-ALL_ACCESSORIES] ON [USED-AECS_BASE_SO_NO].SO_NO = [USED-ALL_ACCESSORIES].SO_NO)" _
& "LEFT JOIN [USED-PROD_UNION] ON [USED-AECS_BASE_SO_NO].MAINFR_SER_NO = [USED-PROD_UNION].PROD_SER_NO)" '<< delete this one
I would delete the last closing parenthesis (and add a semi-colon), then test the SQL to ensure it works.
Originally Posted by
mindbender
One weird thing I noticed is when I hover over strWhere is shows strWhere = WHERE 1=1 and [SUB_LOC_CD] = 'C001'. The C001 makes sense because that is what I put in one of the filter search text boxes....the 1=1 I am not sure about.
The "1=1" is from the filter button (code in Post #4).
Maybe you could try this code.....
Code:
Private Sub export_used_Click()
Dim strWhere As String
Dim sSQL As String
Dim strFile As String
sSQL = "SELECT [USED-AECS_BASE_SO_NO].MAINFR_SER_NO as SERNO, [USED-ALL_ACCESSORIES].PROD_CD1 as PROD, [USED-PROD_UNION].CUST_NAME as CUST,"
sSQL = sSQL & " [USED-AECS_BASE_SO_NO].SUB_LOC_CD as CARRIER, [USED-AECS_BASE_SO_NO].MKT_CD as MKTCODE"
sSQL = sSQL & " FROM ([USED-AECS_BASE_SO_NO] LEFT JOIN [USED-ALL_ACCESSORIES] ON [USED-AECS_BASE_SO_NO].SO_NO = [USED-ALL_ACCESSORIES].SO_NO)"
sSQL = sSQL & " LEFT JOIN [USED-PROD_UNION] ON [USED-AECS_BASE_SO_NO].MAINFR_SER_NO = [USED-PROD_UNION].PROD_SER_NO"
Debug.Print sSQL
'create the filter (where clause)
strWhere = " 1=1"
If Not IsNull(Me.txtCode) Then
strWhere = strWhere & " and Left([MAINFR_SER_NO],3)='" & Me.txtCode & "'"
End If
If Not IsNull(Me.txtCarrier) Then
strWhere = strWhere & " and [SUB_LOC_CD]='" & Me.txtCarrier & "'"
End If
If Not IsNull(Me.txtMCode) Then
strWhere = strWhere & " and [MKT_CD] Like '*" & Me.txtMCode & "*'"
End If
Debug.Print strWhere
sSQL = sSQL & strWhere
' Debug.Print sSQL
strFile = "C:\Executive_Summary\MyExport.xlsx"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, sSQL, strFile, True '<<-- (TRUE adds column names)
End Sub
Look at the immediate window to see if the two lines are formed correctly. Then create a new query, switch to SQL view and paste in the two lines and execute the query.