Error 3075 missing operator in strSQL statement 2

[vba317]

Hello,
I am error 3075 error in my strSQL statement and I was hoping I could get some assistance to finding where my error is. I have tried many different combinations with no luck. I copied and pasted the original code from a query that is working. Any help is appreciated. Tom

'Original code from query
SELECT DAT_Refunds.uci, TMP_Pats.PatName, TMP_Pats.AcctNu, DAT_Refunds.eid, DAT_Refunds.slid, DAT_Refunds.tid, DAT_Refunds.postdttran, TMP_Dpt.dptdesc, TMP_Prov.provdesc, TMP_Fac.facdesc, DAT_Refunds.curinsmne, DAT_Refunds.cpt, DAT_Refunds.comp, DAT_Refunds.amt, DAT_Refunds.doschg, DAT_Refunds.chgamt, DAT_Refunds.priminsmne, DAT_Refunds.ticketnum
FROM (((DAT_Refunds LEFT JOIN TMP_Prov ON DAT_Refunds.prov = TMP_Prov.provid) LEFT JOIN TMP_Pats ON DAT_Refunds.aid = TMP_Pats.aid) LEFT JOIN TMP_Dpt ON DAT_Refunds.dpt = TMP_Dpt.dptid) LEFT JOIN TMP_Fac ON DAT_Refunds.fac = TMP_Fac.facid
ORDER BY TMP_Pats.PatName, TMP_Dpt.dptdesc, DAT_Refunds.cpt;


'strSQL statement

strSQL = "SELECT DAT_Refunds.uci, TMP_Pats.PatName, TMP_Pats.AcctNu, DAT_Refunds.eid, DAT_Refunds.slid, DAT_Refunds.tid, DAT_Refunds.postdttran, " & _
             "TMP_Dpt.dptdesc, TMP_Prov.provdesc, TMP_Fac.facdesc, DAT_Refunds.curinsmne,DAT_Refunds.cpt, DAT_Refunds.comp, DAT_Refunds.amt, " & _
             "DAT_Refunds.doschg, DAT_Refunds.chgamt, DAT_Refunds.priminsmne, DAT_Refunds.ticketnum " & _
             "FROM DAT_Refunds " & _
             "LEFT JOIN TMP_Prov ON DAT_Refunds.prov = TMP_Prov.provid " & _
             "LEFT JOIN TMP_Pats ON DAT_Refunds.aid = TMP_Pats.aid " & _
             "LEFT JOIN TMP_Dpt ON DAT_Refunds.dpt = TMP_Dpt.dptid " & _
             "LEFT JOIN TMP_Fac ON DAT_Refunds.fac = TMP_Fac.facid" & _
             "ORDER BY TMP_Pats.PatName, TMP_Dpt.dptdesc, DAT_Refunds.cpt;"

debug.print output
SELECT DAT_Refunds.uci, TMP_Pats.PatName, TMP_Pats.AcctNu, DAT_Refunds.eid, DAT_Refunds.slid, DAT_Refunds.tid, DAT_Refunds.postdttran, TMP_Dpt.dptdesc, TMP_Prov.provdesc, TMP_Fac.facdesc, DAT_Refunds.curinsmne,DAT_Refunds.cpt, DAT_Refunds.comp, DAT_Refunds.amt, DAT_Refunds.doschg, DAT_Refunds.chgamt, DAT_Refunds.priminsmne, DAT_Refunds.ticketnum FROM DAT_Refunds LEFT JOIN TMP_Prov ON DAT_Refunds.prov = TMP_Prov.provid LEFT JOIN TMP_Pats ON DAT_Refunds.aid = TMP_Pats.aid LEFT JOIN TMP_Dpt ON DAT_Refunds.dpt = TMP_Dpt.dptid LEFT JOIN TMP_Fac ON DAT_Refunds.fac = TMP_Fac.facidORDER BY TMP_Pats.PatName, TMP_Dpt.dptdesc, DAT_Refunds.cpt;

 

[vba317]

When i read the error it says runtime error '3075';
sytax error (missing operator in query expression

'DAT_Refunds.prov = TMP_Prov.provid " & _
LEFT JOIN TMP_Pats ON DAT_Refunds.aid = TMP_Pats.aid " & _
LEFT JOIN TMP_Dpt ON DAT_Refunds.dpt = TMP_Dpt.dptid " & _
LEFT JOIN TMP_Fac ON DAT_Refunds.fac = TMP_Fac.facid'"

I changed the following line: "LEFT JOIN TMP_Fac ON DAT_Refunds.fac = TMP_Fac.facid" & _
to "LEFT JOIN TMP_Fac ON DAT_Refunds.fac = TMP_Fac.facid " & _
and I still get the same error

 

[PHV]

Missing parenthesis in the FROM clause.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[dhookom]

Missing a space

"LEFT JOIN TMP_Fac ON DAT_Refunds.fac = TMP_Fac.facid[highlight #EF2929] [/highlight]" & _

Duane
Hook'D on Access
MS Access MVP

 

[vba317]

I added the parentheses and I added the space but I still get the error. Any suggestions?

 strSQL = "SELECT DAT_Refunds.uci, TMP_Pats.PatName, TMP_Pats.AcctNu, DAT_Refunds.eid, DAT_Refunds.slid, DAT_Refunds.tid, DAT_Refunds.postdttran, TMP_Dpt.dptdesc, TMP_Prov.provdesc, TMP_Fac.facdesc, DAT_Refunds.curinsmne, DAT_Refunds.cpt, DAT_Refunds.comp, DAT_Refunds.amt, DAT_Refunds.doschg, DAT_Refunds.chgamt, DAT_Refunds.priminsmne, DAT_Refunds.ticketnum " & _
             "FROM (((DAT_Refunds LEFT JOIN TMP_Prov ON DAT_Refunds.prov = TMP_Prov.provid) LEFT JOIN TMP_Pats ON DAT_Refunds.aid = TMP_Pats.aid) LEFT JOIN TMP_Dpt ON DAT_Refunds.dpt = TMP_Dpt.dptid) LEFT JOIN TMP_Fac ON DAT_Refunds.fac = TMP_Fac.facid " & _
             "ORDER BY TMP_Pats.PatName, TMP_Dpt.dptdesc, DAT_Refunds.cpt;"

debug result
SELECT DAT_Refunds.uci, TMP_Pats.PatName, TMP_Pats.AcctNu, DAT_Refunds.eid, DAT_Refunds.slid, DAT_Refunds.tid, DAT_Refunds.postdttran, TMP_Dpt.dptdesc, TMP_Prov.provdesc, TMP_Fac.facdesc, DAT_Refunds.curinsmne,DAT_Refunds.cpt, DAT_Refunds.comp, DAT_Refunds.amt, DAT_Refunds.doschg, DAT_Refunds.chgamt, DAT_Refunds.priminsmne, DAT_Refunds.ticketnum (((FROM DAT_Refunds LEFT JOIN TMP_Prov ON DAT_Refunds.prov = TMP_Prov.provid) LEFT JOIN TMP_Pats ON DAT_Refunds.aid = TMP_Pats.aid) LEFT JOIN TMP_Dpt ON DAT_Refunds.dpt = TMP_Dpt.dptid) LEFT JOIN TMP_Fac ON DAT_Refunds.fac = TMP_Fac.facid ORDER BY TMP_Pats.PatName, TMP_Dpt.dptdesc, DAT_Refunds.cpt;

 

[dhookom]

From your strSQL = statement with "(((" following the "FROM", how did it get before the "FROM" in your debug results?

BTW: Please edit your code to include carriage returns so there isn't a need for a horizontal scroll bar.

strSQL = "SELECT DAT_Refunds.uci, TMP_Pats.PatName, TMP_Pats.AcctNu, DAT_Refunds.eid, DAT_Refunds.slid, DAT_Refunds.tid, DAT_Refunds.postdttran, TMP_Dpt.dptdesc,  " & _
    "TMP_Prov.provdesc, TMP_Fac.facdesc, DAT_Refunds.curinsmne, DAT_Refunds.cpt, DAT_Refunds.comp, DAT_Refunds.amt, DAT_Refunds.doschg, DAT_Refunds.chgamt, " & _
    "DAT_Refunds.priminsmne, DAT_Refunds.ticketnum " & _
    "FROM [highlight #CC0000]((([/highlight]DAT_Refunds LEFT JOIN TMP_Prov ON DAT_Refunds.prov = TMP_Prov.provid) LEFT JOIN TMP_Pats ON DAT_Refunds.aid = TMP_Pats.aid) " & _
    "LEFT JOIN TMP_Dpt ON DAT_Refunds.dpt = TMP_Dpt.dptid) LEFT JOIN TMP_Fac ON DAT_Refunds.fac = TMP_Fac.facid " & _
    "ORDER BY TMP_Pats.PatName, TMP_Dpt.dptdesc, DAT_Refunds.cpt;"

debug result
SELECT DAT_Refunds.uci, TMP_Pats.PatName, TMP_Pats.AcctNu, DAT_Refunds.eid, DAT_Refunds.slid, DAT_Refunds.tid, DAT_Refunds.postdttran, TMP_Dpt.dptdesc, 
TMP_Prov.provdesc, TMP_Fac.facdesc, DAT_Refunds.curinsmne,DAT_Refunds.cpt, DAT_Refunds.comp, DAT_Refunds.amt, DAT_Refunds.doschg, DAT_Refunds.chgamt, 
DAT_Refunds.priminsmne, DAT_Refunds.ticketnum 
[highlight #A40000]((([/highlight]FROM DAT_Refunds LEFT JOIN TMP_Prov ON DAT_Refunds.prov = TMP_Prov.provid) LEFT JOIN TMP_Pats ON DAT_Refunds.aid = TMP_Pats.aid) 
LEFT JOIN TMP_Dpt ON DAT_Refunds.dpt = TMP_Dpt.dptid) 
LEFT JOIN TMP_Fac ON DAT_Refunds.fac = TMP_Fac.facid 
ORDER BY TMP_Pats.PatName, TMP_Dpt.dptdesc, DAT_Refunds.cpt;

Duane
Hook'D on Access
MS Access MVP

 

[vba317]

I have tried many different combinations and that is an example of another failed attempt. I will add carriage returns. This is my current code which still gives me an error.
The error point is highlighted in Blue

Tom

 strSQL = "SELECT DAT_Refunds.uci, TMP_Pats.PatName, TMP_Pats.AcctNu, DAT_Refunds.eid, DAT_Refunds.slid, DAT_Refunds.tid, DAT_Refunds.postdttran, " & _
             "TMP_Dpt.dptdesc, TMP_Prov.provdesc, TMP_Fac.facdesc, DAT_Refunds.curinsmne, DAT_Refunds.cpt, DAT_Refunds.comp, DAT_Refunds.amt, " & _
             "DAT_Refunds.doschg, DAT_Refunds.chgamt, DAT_Refunds.priminsmne, DAT_Refunds.ticketnum " & _
             "(((FROM DAT_Refunds " & _
             "LEFT JOIN TMP_Prov ON DAT_Refunds.prov = TMP_Prov.provid) " & _
             "LEFT JOIN TMP_Pats ON DAT_Refunds.aid = TMP_Pats.aid) " & _
             "LEFT JOIN TMP_Dpt ON DAT_Refunds.dpt = TMP_Dpt.dptid) " & _
             "LEFT JOIN TMP_Fac ON DAT_Refunds.fac = TMP_Fac.facid " & _
             "ORDER BY TMP_Pats.PatName, TMP_Dpt.dptdesc, DAT_Refunds.cpt;"
             
             Debug.Print strSQL
             Call xlRecordset(strSQL)
Public Function xlRecordset(strSQL As String)
    Dim rst As DAO.Recordset
    Dim iBotRow As Integer
    Dim iFormulaRow As Integer
    Call xlCalcBotRow(iBotRow, iFormulaRow)
 [Blue]   Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot) [/Blue]
    If Not rst.EOF Then
        With rst
            .MoveLast
            .MoveFirst
        End With
    End If
    With goXl.ActiveSheet
        .Range("A" & iFormulaRow).CopyFromRecordset rst
    End With
End Function


debug results
SELECT DAT_Refunds.uci, TMP_Pats.PatName, TMP_Pats.AcctNu, DAT_Refunds.eid, DAT_Refunds.slid, DAT_Refunds.tid, DAT_Refunds.postdttran,
 TMP_Dpt.dptdesc, TMP_Prov.provdesc, TMP_Fac.facdesc, DAT_Refunds.curinsmne, DAT_Refunds.cpt, DAT_Refunds.comp, DAT_Refunds.amt,
DAT_Refunds.doschg, DAT_Refunds.chgamt, DAT_Refunds.priminsmne, DAT_Refunds.ticketnum 
(((FROM DAT_Refunds LEFT JOIN TMP_Prov ON DAT_Refunds.prov = TMP_Prov.provid) 
LEFT JOIN TMP_Pats ON DAT_Refunds.aid = TMP_Pats.aid) LEFT JOIN TMP_Dpt ON DAT_Refunds.dpt = TMP_Dpt.dptid) 
LEFT JOIN TMP_Fac ON DAT_Refunds.fac = TMP_Fac.facid ORDER BY TMP_Pats.PatName, TMP_Dpt.dptdesc, DAT_Refunds.cpt;

 

[dhookom]

Are you comparing your debug.print to your "original code from query" that apparently worked? It had the ((( following the FROM.

Duane
Hook'D on Access
MS Access MVP

 

[PHV]

Replace this:
"(((FROM DAT_Refunds " & _
with this:
"FROM (((DAT_Refunds " & _

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[vba317]

Thanks Duane that fixed it!