Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations bkrike on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

SQL string causes error 3075 missing operator

Status
Not open for further replies.
vba317

vba317

Programmer
Mar 5, 2009
708
US
I have a new sql string that I have been working on all week. My problem area is in the left join section of my code. I have a debug print statement printing out the strSQL. I believe that I have all the proper spaces. This is the first time I have used Left JOIN. The error is Runtime error 3075 Missing operator(Syntax error)
rpt_dat_chgs_tbl.fac = rpt_dic_fac_tbl.facid LEFT JOIN rpt_dic_prov_tbl ON rpt_dat_chgs_tbl.uci = rpt_dic_prov_tbl.uci

It looks to me that the code is not recognizing the ON statement. I have tried "ON", " ON" and what I have now is " ON ". These combinations give me syntax errors. I really thought I got it the last time, but I was wrong. I have copied the original query and the debug output into a word document and done a character by character comparison. I couldn't see any differences. I really do appreciate any help that can be provided.

Original query
Code: 
SELECT rpt_dat_chgs_tbl.uci, rpt_dic_period_tbl.cache_id, rpt_dic_period_tbl.yr, rpt_dic_period_tbl.pfy, rpt_dat_chgs_tbl.cpt, NBM_CS_Data.[CPT Name], NBM_CS_Data.[CPT Component], NBM_CS_Data.[Facility Name], NBM_CS_Data.[POS Name], NBM_CS_Data.[Department Name], NBM_CS_Data.[Provider Name], NBM_CS_Data.[Revenue Center Name], Sum(rpt_dat_chgs_tbl.units) AS SumOfunits, Sum(rpt_dat_chgs_tbl.amt) AS SumOfamt, Sum(rpt_dat_chgs_tbl.adjunit) AS SumOfadjunit
FROM ((((rpt_dat_chgs_tbl LEFT JOIN rpt_dic_fac_tbl ON rpt_dat_chgs_tbl.fac = rpt_dic_fac_tbl.facid) LEFT JOIN rpt_dic_prov_tbl ON (rpt_dat_chgs_tbl.uci = rpt_dic_prov_tbl.uci) AND (rpt_dat_chgs_tbl.prov = rpt_dic_prov_tbl.prvid)) LEFT JOIN rpt_dic_period_tbl ON rpt_dat_chgs_tbl.svcpd = rpt_dic_period_tbl.pd) INNER JOIN _LinkedProv ON ([_LinkedProv].uci = rpt_dat_chgs_tbl.uci) AND (rpt_dat_chgs_tbl.prov = [_LinkedProv].prvid)) INNER JOIN NBM_CS_Data ON rpt_dat_chgs_tbl.cpt = NBM_CS_Data.[CPT Code]
GROUP BY rpt_dat_chgs_tbl.uci, rpt_dic_period_tbl.cache_id, rpt_dic_period_tbl.yr, rpt_dic_period_tbl.pfy, rpt_dat_chgs_tbl.cpt, NBM_CS_Data.[CPT Name], NBM_CS_Data.[CPT Component], NBM_CS_Data.[Facility Name], NBM_CS_Data.[POS Name], NBM_CS_Data.[Department Name], NBM_CS_Data.[Provider Name], NBM_CS_Data.[Revenue Center Name], rpt_dat_chgs_tbl.billpd
HAVING (((rpt_dat_chgs_tbl.uci)="NBM") AND ((rpt_dat_chgs_tbl.billpd)>321))
ORDER BY rpt_dat_chgs_tbl.billpd;

results from the debug statement
SELECT rpt_dat_chgs_tbl.uci, rpt_dic_period_tbl.cache_id, rpt_dic_period_tbl.yr, rpt_dic_period_tbl.pfy, rpt_dat_chgs_tbl.cpt, NBM_CS_Data.[CPT Name], NBM_CS_Data.[CPT Component], NBM_CS_Data.[Facility Name], NBM_CS_Data.[POS Name], NBM_CS_Data.[Department Name], NBM_CS_Data.[Provider Name], NBM_CS_Data.[Revenue Center Name], Sum(Units)AS SumOfUnits,Sum(amt)AS SumOfamt, Sum(adjunit)AS SumOfadjunit FROM rpt_dat_chgs_tbl LEFT JOIN rpt_dic_fac_tbl ON rpt_dat_chgs_tbl.fac = rpt_dic_fac_tbl.facid LEFT JOIN rpt_dic_prov_tbl ON rpt_dat_chgs_tbl.uci = rpt_dic_prov_tbl.uci AND rpt_dat_chgs_tbl.prov = rpt_dic_prov_tbl.prvid LEFT JOIN rpt_dic_period_tbl ON rpt_dat_chgs_tbl.svcpd = rpt_dic_period_tbl.pd INNER JOIN _LinkedProv ON _LinkedProv.uci = rpt_dat_chgs_tbl.uci AND rpt_dat_chgs_tbl.cpt = _LinkedProv.prvid INNER JOIN NBM_CS_Data ON rpt_dat_chgs_tbl.cpt = NBM_CS_Data.[CPT Code] WHERE rpt_dat_chgs_tbl.uci = NBM AND rpt_dat_chgs_tbl.billpd > 369 GROUP BY rpt_dat_chgs_tbl.uci, rpt_dic_period_tbl.cache_id, rpt_dic_period_tbl.yr, rpt_dic_period_tbl.pfy, rpt_dat_chgs_tbl.cpt, NBM_CS_Data.[CPT Name], NBM_CS_Data.[CPT Component], NBM_CS_Data.[Facility Name], NBM_CS_Data.[POS Name], NBM_CS_Data.[Department Name], NBM_CS_Data.[Provider Name], NBM_CS_Data.[Revenue Center Name], rpt_dat_chgs_tbl.billpd ORDER BY rpt_dat_chgs_tbl.billpd;

Actual Code
Code: 
Public Function GetSelData()
Dim strSum1 As String
Dim strSum2 As String
Dim strSum3 As String
Dim strTbl1 As String
Dim strTbl2 As String
Dim strTbl3 As String
Dim strTbl4 As String
Dim strTbl5 As String
Dim strTbl6 As String
Dim strTbl7 As String
Dim strSel1 As String
Dim strSel2 As String
Dim strSel3 As String
Dim strSel4 As String
Dim strSel5 As String
Dim strSel6 As String
Dim strFld1 As String
Dim strFld2 As String
Dim strFld3 As String
Dim strLJoin1 As String
Dim strLJoin2 As String
Dim strLJoin3 As String
Dim strLJoin4 As String
Dim strLJoin5 As String
Dim strLJoin6 As String
Dim strLJoin7 As String
Dim strIJoin1 As String
Dim strIJoin2 As String
Dim strIJoin3 As String
Dim strIJoin4 As String
Dim strIJoin5 As String
Dim strWhr1 As String
Dim strWhr2 As String
Dim strWhr3 As String
Dim iWhr As Integer
Dim strSql As String
Dim rst As Recordset

strSel1 = "uci"
strSel2 = "cache_id"
strSel3 = "yr"
strSel4 = "pfy"
strSel5 = "cpt"
strSel6 = "[CPT Name]"
strSel7 = "[CPT Component]"
strSel8 = "[Facility Name]"
strSel9 = "[POS Name]"
strSel10 = "[Department Name]"
strSel11 = "[Provider Name]"
strSel12 = "[Revenue Center Name]"
strFld1 = "Units"
strFld2 = "amt"
strFld3 = "adjunit"
strSum1 = "Sum(" & strFld1 & ")" & "AS SumOf" & strFld1
strSum2 = "Sum(" & strFld2 & ")" & "AS SumOf" & strFld2
strSum3 = "Sum(" & strFld3 & ")" & "AS SumOf" & strFld3
strTbl1 = "rpt_dat_chgs_tbl"
strTbl2 = "rpt_dic_period_tbl"
strTbl3 = "NBM_CS_Data"
strTbl4 = "rpt_dic_fac_tbl"
strTbl5 = "rpt_dic_prov_tbl"
strTbl6 = "rpt_dic_period_tbl"
strTbl7 = "_LinkedProv"
strLJoin1 = "fac"
strLJoin2 = "facid"
strLJoin3 = "uci"
strLJoin4 = "prov"
strLJoin5 = "prvid"
strLJoin6 = "svcpd"
strLJoin7 = "pd"
strIJoin1 = "uci"
strIJoin2 = "prov"
strIJoin3 = "prvid"
strIJoin4 = "cpt"
strIJoin5 = "[CPT Code]"
strWhr1 = "uci"
strWhr2 = "NBM"
strWhr3 = "billpd"
iWhr = 369

'Original strSql
strSql = "SELECT " & strTbl1 & "." & strSel1 & ", " & strTbl2 & "." & strSel2 & ", " & strTbl2 & "." & strSel3 & _
         ", " & strTbl2 & "." & strSel4 & ", " & strTbl1 & "." & strSel5 & ", " & strTbl3 & "." & strSel6 & _
         ", " & strTbl3 & "." & strSel7 & ", " & strTbl3 & "." & strSel8 & ", " & strTbl3 & "." & strSel9 & _
         ", " & strTbl3 & "." & strSel10 & ", " & strTbl3 & "." & strSel11 & ", " & strTbl3 & "." & strSel12 & _
         ", " & strSum1 & "," & strSum2 & ", " & strSum3 & _
         " FROM " & strTbl1 & _
         " LEFT JOIN " & strTbl4 & " ON " & strTbl1 & "." & strLJoin1 & " = " & strTbl4 & "." & strLJoin2 & _
         " LEFT JOIN " & strTbl5 & " ON " & strTbl1 & "." & strLJoin3 & " = " & strTbl5 & "." & strLJoin3 & _
         " AND " & strTbl1 & "." & strLJoin4 & " = " & strTbl5 & "." & strLJoin5 & _
         " LEFT JOIN " & strTbl6 & " ON " & strTbl1 & "." & strLJoin6 & " = " & strTbl2 & "." & strLJoin7 & _
         " INNER JOIN " & strTbl7 & " ON " & strTbl7 & "." & strIJoin1 & " = " & strTbl1 & "." & strIJoin1 & _
         " AND " & strTbl1 & "." & strIJoin4 & " = " & strTbl7 & "." & strIJoin3 & _
         " INNER JOIN " & strTbl3 & " ON " & strTbl1 & "." & strIJoin4 & " = " & strTbl3 & "." & strIJoin5 & _
         " WHERE " & strTbl1 & "." & strWhr1 & " = " & strWhr2 & " AND " & strTbl1 & "." & strWhr3 & " > " & [iWhr] & _
         " GROUP BY " & strTbl1 & "." & strSel1 & ", " & strTbl2 & "." & strSel2 & ", " & strTbl2 & "." & strSel3 & ", " & strTbl2 & "." & strSel4 & _
         ", " & strTbl1 & "." & strSel5 & ", " & strTbl3 & "." & strSel6 & ", " & strTbl3 & "." & strSel7 & _
         ", " & strTbl3 & "." & strSel8 & ", " & strTbl3 & "." & strSel9 & ", " & strTbl3 & "." & strSel10 & ", " & strTbl3 & "." & strSel11 & _
         ", " & strTbl3 & "." & strSel12 & ", " & strTbl1 & "." & strWhr3 & _
         " ORDER BY " & strTbl1 & "." & strWhr3 & ";" & ""
         Debug.Print strSql
 Set rst = CurrentDb.OpenRecordset(strSql, dbOpenSnapshot)
If Not (rst.BOF Or rst.EOF) Then
       goXL.ActiveSheet.Range("A2").CopyFromRecordset rst
End If
End Function
 
Sort by date Sort by votes
hi,

Have you constructed your query in the GUI?

Skip,

[glasses]Just traded in my old subtlety...
for a NUANCE![tongue]
 
Upvote 0 Downvote
Yes. Under the heading original query. I copied and pasted that information from the SQL view of that query.
 
Upvote 0 Downvote
and that ran sucessfully?

Skip,

[glasses]Just traded in my old subtlety...
for a NUANCE![tongue]
 
Upvote 0 Downvote
I am running it now. The last time I tried to run it I had to stop it after 30 min. So I will repost after the query finishes.

Tom
 
Upvote 0 Downvote
FYI: It is very typical for an error message to have ABSOLUTELY NOTHING to do with the actual error.

Code: 
WHERE rpt_dat_chgs_tbl.uci = NBM
  AND rpt_dat_chgs_tbl.billpd > 369
No QUOTES around literal NBM. Should be...
Code: 
WHERE rpt_dat_chgs_tbl.uci = 'NBM'
  AND rpt_dat_chgs_tbl.billpd > 369


Skip,

[glasses]Just traded in my old subtlety...
for a NUANCE![tongue]
 
Upvote 0 Downvote
Furthermore you missed all the parenthesis in the FROM clause.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
Upvote 0 Downvote
Thank you both I will add in the quotes around NBM and 369 and the parenthesis around the FROM clause and get back.

Tom
 
Upvote 0 Downvote
Lf billpd is defined as numeric in rpt_dat_chgs_tbl then no need of single quotes around 369

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

C
  • Question
Run-time error '3075': Syntax error (missing operator) in query expression 'Birthday = and Birthmonth ='.
Replies
3
Views
239
OzFoxy
OzFoxy
combo
  • Locked
  • Question
SQL data types in CREATE/ALTER TABLE queries 1
Replies
2
Views
2K
combo
combo
Swi
  • Locked
  • Question
Jet Sql Statement - Select Distinct Multiple Columns 1
Replies
4
Views
909
Swi
Swi
breezett93
  • Locked
  • Question
Brand new lock violations errors all over my database
Replies
11
Views
5K
dhookom
dhookom
serino
  • Locked
  • Question
Help with SQL inner join statement 1
Replies
2
Views
474
serino
serino

Part and Inventory Search

Sponsor

Back
Top