What is wrong with this code?

  • Thread starter Thread starter confused
  • Start date Start date
C

confused

Can someone please tell me what is wrong with this code. I get and
error " syntax error missing operator"
I can run this in the analyzer without a problem.

strSQL = "INSERT INTO TBL_PROG_Timekeepers " & _
"( MATT_CLASS_CODE, TRAN_TYPE, LEAD_CODE, LEAD_NAME, " & _
" MATTER_CODE, MATTER_NAME, PERIOD, EMPLOYEE_CODE, EMPLOYEE_NAME,
" & _
"WriteOffs, TXT1, Markdowns, CLIENT_CODE, Reason_desc,
Trans_date ) " & _
"SELECT HBM_MATTER.MATT_CLASS_CODE, " & _
" 'FM' AS TRAN_TYPE, HBM_PERSNL_1.EMPLOYEE_CODE, " & _
"HBM_PERSNL_1.EMPLOYEE_NAME, " & _
"HBM_MATTER.MATTER_CODE, " & _
"HBM_MATTER.MATTER_NAME, " & _
"BLH_BILLED_FEES.PERIOD, " & _
"p.EMPLOYEE_CODE, p.EMPLOYEE_NAME, " & _
"SUM(0) AS Writeoffs,ACT_TEXT.txt1, " & _
"SUM(BLH_BILLED_FEES.BILLED_AMT - BLH_BILLED_FEES.BASE_AMT) AS
Markdowns, " & _
"HBM_MATTER.CLIENT_CODE, " & _
"TAL_ADJ.ADJ_DESC, ACT_TRAN.Trans_date "

strSQL = strSQL + "FROM BLH_BILLED_FEES INNER JOIN " & _
"HBM_MATTER ON BLH_BILLED_FEES.MATTER_UNO = HBM_MATTER.MATTER_UNO
" & _
"INNER JOIN HBM_PERSNL as p ON HBM_MATTER.RESP_EMPL_UNO =
p.EMPL_UNO " & _
"INNER JOIN ACT_TRAN ON BLH_BILLED_FEES.SOURCE_TRAN_UNO =
ACT_TRAN.TRAN_UNO " & _
"INNER JOIN ACT_TEXT ON ACT_TRAN.TRANS_TEXT_ID = ACT_TEXT.TEXT_ID
" & _
"INNER JOIN HBM_PERSNL HBM_PERSNL_1 ON p.EMPL_UNO =
HBM_PERSNL_1.EMPL_UNO " & _
"INNER JOIN BLT_BILL ON ACT_TRAN.TRAN_UNO = BLT_BILL.TRAN_UNO " &
_
"INNER JOIN TAL_ADJ ON BLT_BILL.TIME_ADJ_CODE = TAL_ADJ.ADJ_CODE "
& _
"LEFT JOIN ACL_REASON ON ACT_TRAN.REASON_CODE =
ACL_REASON.REASON_CODE " & _
"GROUP BY HBM_MATTER.MATT_CLASS_CODE, " & _
"HBM_PERSNL_1.EMPLOYEE_CODE, HBM_PERSNL_1.EMPLOYEE_NAME, " & _
"HBM_MATTER.MATTER_CODE, " & _
"HBM_MATTER.MATTER_NAME, " & _
"BLH_BILLED_FEES.PERIOD, BLH_BILLED_FEES.period, " & _
"ACT_TEXT.txt1, HBM_MATTER.CLIENT_CODE, TAL_ADJ.ADJ_DESC,
ACT_TRAN.Trans_date, " & _
"p.EMPLOYEE_NAME, p.EMPLOYEE_CODE " & _
"HAVING BLH_BILLED_FEES.PERIOD >= " & txtFromPeriod & " " & _
" AND BLH_BILLED_FEES.PERIOD <= " & txtToPeriod & " " & _
" AND (SUM(BLH_BILLED_FEES.BILLED_AMT - BLH_BILLED_FEES.BASE_AMT)
<> 0) " & _
" AND HBM_MATTER.CLIENT_CODE <>'9999' " & _
" AND HBM_MATTER.CLIENT_CODE <> '9901' "
 
Confused,

Unlike SQL Server and Oracle, in Access, when you have joins of more than
two tables, they have to be wrapped in parenthesis. It looks something
like:

FROM (tbl_A INNER JOIN tbl_B ON tbl_A.ID = tbl_B.ID) INNER JOIN tbl_C ON
tbl_B.ID = tbl_C.ID

I'm not very good at getting the parenthesis in the right places (its an
attention to detail thing), so I generally create the query in the query
design view, make sure it runs and returns the results that I'm looking for.
Then, if I'm convinced I need to write the SQL in code for some reason, and
cannot just use the already created query, I will copy the SQL and paste it
into my code. Once in code, I put in all of the appropriate quotes and line
wraps to make it easier to read.

Dale
 
Confused,

Unlike SQL Server and Oracle, in Access, when you have joins of more than
two tables, they have to be wrapped in parenthesis. It looks something
like:

FROM (tbl_A INNER JOIN tbl_B ON tbl_A.ID = tbl_B.ID) INNER JOIN tbl_C ON
tbl_B.ID = tbl_C.ID

I'm not very good at getting the parenthesis in the right places (its an
attention to detail thing), so I generally create the query in the query
design view, make sure it runs and returns the results that I'm looking for.
Then, if I'm convinced I need to write the SQL in code for some reason, and
cannot just use the already created query, I will copy the SQL and paste it
into my code. Once in code, I put in all of the appropriate quotes and line
wraps to make it easier to read.

Dale








- Show quoted text -
Click to expand...

Can you tell me how to write the code to print this sql statement to a
txt?
The person who created this app., had a workgroup that one would need
to be added to to modify the code, debug. etc...
I am limited on what access I have. I can't re-write the code in a
query wizard then copy the code.
 
Can you tell me how to write the code to print this sql statement to a
txt?
The person who created this app., had a workgroup that one would need
to be added to to modify the code, debug. etc...
I am limited on what access I have. I can't re-write the code in a
query wizard then copy the code.- Hide quoted text -

- Show quoted text -
Click to expand...

Dis-regard my question about writhing to a txt file.
I got the txt to write afterall.

Thanks,
confused
 
Dis-regard my question about writhing to a txt file.
I got the txt to write afterall.

Thanks,
confused- Hide quoted text -

- Show quoted text -
Click to expand...

Okay, I need help again;
This code when ran is asking for blh_billed_fees.period.
Can find out why.

strSQL = "INSERT INTO [Table name here] " & _
"( MATT_CLASS_CODE, TRAN_TYPE, LEAD_CODE, LEAD_NAME, " & _
" MATTER_CODE, MATTER_NAME, PERIOD, EMPLOYEE_CODE, EMPLOYEE_NAME,
" & _
"WriteOffs, TXT1, Markdowns, CLIENT_CODE, Reason_desc,
Trans_date ) " & _
"SELECT dbo_HBM_MATTER.MATT_CLASS_CODE, " & _
" 'FM' AS TRAN_TYPE, dbo_HBM_PERSNL_1.EMPLOYEE_CODE, " & _
"dbo_HBM_PERSNL_1.EMPLOYEE_NAME, " & _
"dbo_HBM_MATTER.MATTER_CODE, " & _
"dbo_HBM_MATTER.MATTER_NAME, " & _
"dbo_BLH_BILLED_FEES.PERIOD, " & _
"p.EMPLOYEE_CODE, p.EMPLOYEE_NAME, " & _
"SUM(0) AS Writeoffs, dbo_ACT_TEXT.txt1, " & _
"SUM(dbo_BLH_BILLED_FEES.BILLED_AMT -
dbo_BLH_BILLED_FEES.BASE_AMT) AS Markdowns, " & _
"dbo_HBM_MATTER.CLIENT_CODE, " & _
"dbo_TAL_ADJ.ADJ_DESC, dbo_ACT_TRAN.Trans_date "
strSQL = strSQL + " FROM (((((((dbo_HBM_MATTER INNER JOIN
dbo_BLH_BILLED_FEES " & _
" ON dbo_HBM_MATTER.MATTER_UNO = dbo_BLH_BILLED_FEES.MATTER_UNO)"
& _
" INNER JOIN dbo_HBM_PERSNL AS p ON dbo_HBM_MATTER.RESP_EMPL_UNO =
p.EMPL_UNO)" & _
" INNER JOIN dbo_ACT_TRAN ON dbo_BLH_BILLED_FEES.SOURCE_TRAN_UNO =
dbo_ACT_TRAN.TRAN_UNO)" & _
" INNER JOIN dbo_ACT_TEXT ON dbo_ACT_TRAN.TRANS_TEXT_ID =
dbo_ACT_TEXT.TEXT_ID)" & _
" INNER JOIN dbo_BLT_BILL ON dbo_ACT_TRAN.TRAN_UNO =
dbo_BLT_BILL.TRAN_UNO)" & _
" INNER JOIN dbo_TAL_ADJ ON dbo_BLT_BILL.TIME_ADJ_CODE =
dbo_TAL_ADJ.ADJ_CODE)" & _
" INNER JOIN dbo_HBM_PERSNL AS dbo_HBM_PERSNL_1 ON p.EMPL_UNO =
dbo_HBM_PERSNL_1.EMPL_UNO)" & _
" LEFT JOIN dbo_ACL_REASON ON dbo_ACT_TRAN.REASON_CODE =
dbo_ACL_REASON.REASON_CODE " & _
"GROUP BY dbo_HBM_MATTER.MATT_CLASS_CODE,
dbo_HBM_PERSNL_1.EMPLOYEE_CODE, " & _
" dbo_HBM_PERSNL_1.EMPLOYEE_NAME, dbo_HBM_MATTER.MATTER_CODE,
dbo_HBM_MATTER.MATTER_NAME, " & _
"dbo_BLH_BILLED_FEES.PERIOD, BLH_BILLED_FEES.period,
dbo_ACT_TEXT.txt1, " & _
" dbo_HBM_MATTER.CLIENT_CODE, dbo_TAL_ADJ.ADJ_DESC,
dbo_ACT_TRAN.Trans_date, " & _
" p.EMPLOYEE_NAME, p.EMPLOYEE_CODE " & _
"HAVING (dbo_BLH_BILLED_FEES.PERIOD >= " & txtFromPeriod & " ) " &
_
" AND (dbo_BLH_BILLED_FEES.PERIOD <= " & txtToPeriod & " ) " & _
" AND (SUM(dbo_BLH_BILLED_FEES.BILLED_AMT -
dbo_BLH_BILLED_FEES.BASE_AMT) <> 0) " & _
" AND ((dbo_HBM_MATTER.CLIENT_CODE <>'9999') AND
dbo_HBM_MATTER.CLIENT_CODE <> '9901')"
 
Dis-regard my question about writhing to a txt file.
I got the txt to write afterall.
Click to expand...
Thanks,
confused- Hide quoted text -
Click to expand...
- Show quoted text -
Click to expand...

Okay, I need help again;
This code when ran is asking for blh_billed_fees.period.
Can find out why.

strSQL = "INSERT INTO [Table name here] " & _
"( MATT_CLASS_CODE, TRAN_TYPE, LEAD_CODE, LEAD_NAME, " & _
" MATTER_CODE, MATTER_NAME, PERIOD, EMPLOYEE_CODE, EMPLOYEE_NAME,
" & _
"WriteOffs, TXT1, Markdowns, CLIENT_CODE, Reason_desc,
Trans_date ) " & _
"SELECT dbo_HBM_MATTER.MATT_CLASS_CODE, " & _
" 'FM' AS TRAN_TYPE, dbo_HBM_PERSNL_1.EMPLOYEE_CODE, " & _
"dbo_HBM_PERSNL_1.EMPLOYEE_NAME, " & _
"dbo_HBM_MATTER.MATTER_CODE, " & _
"dbo_HBM_MATTER.MATTER_NAME, " & _
"dbo_BLH_BILLED_FEES.PERIOD, " & _
"p.EMPLOYEE_CODE, p.EMPLOYEE_NAME, " & _
"SUM(0) AS Writeoffs, dbo_ACT_TEXT.txt1, " & _
"SUM(dbo_BLH_BILLED_FEES.BILLED_AMT -
dbo_BLH_BILLED_FEES.BASE_AMT) AS Markdowns, " & _
"dbo_HBM_MATTER.CLIENT_CODE, " & _
"dbo_TAL_ADJ.ADJ_DESC, dbo_ACT_TRAN.Trans_date "
strSQL = strSQL + " FROM (((((((dbo_HBM_MATTER INNER JOIN
dbo_BLH_BILLED_FEES " & _
" ON dbo_HBM_MATTER.MATTER_UNO = dbo_BLH_BILLED_FEES.MATTER_UNO)"
& _
" INNER JOIN dbo_HBM_PERSNL AS p ON dbo_HBM_MATTER.RESP_EMPL_UNO =
p.EMPL_UNO)" & _
" INNER JOIN dbo_ACT_TRAN ON dbo_BLH_BILLED_FEES.SOURCE_TRAN_UNO =
dbo_ACT_TRAN.TRAN_UNO)" & _
" INNER JOIN dbo_ACT_TEXT ON dbo_ACT_TRAN.TRANS_TEXT_ID =
dbo_ACT_TEXT.TEXT_ID)" & _
" INNER JOIN dbo_BLT_BILL ON dbo_ACT_TRAN.TRAN_UNO =
dbo_BLT_BILL.TRAN_UNO)" & _
" INNER JOIN dbo_TAL_ADJ ON dbo_BLT_BILL.TIME_ADJ_CODE =
dbo_TAL_ADJ.ADJ_CODE)" & _
" INNER JOIN dbo_HBM_PERSNL AS dbo_HBM_PERSNL_1 ON p.EMPL_UNO =
dbo_HBM_PERSNL_1.EMPL_UNO)" & _
" LEFT JOIN dbo_ACL_REASON ON dbo_ACT_TRAN.REASON_CODE =
dbo_ACL_REASON.REASON_CODE " & _
"GROUP BY dbo_HBM_MATTER.MATT_CLASS_CODE,
dbo_HBM_PERSNL_1.EMPLOYEE_CODE, " & _
" dbo_HBM_PERSNL_1.EMPLOYEE_NAME, dbo_HBM_MATTER.MATTER_CODE,
dbo_HBM_MATTER.MATTER_NAME, " & _
"dbo_BLH_BILLED_FEES.PERIOD, BLH_BILLED_FEES.period,
dbo_ACT_TEXT.txt1, " & _
" dbo_HBM_MATTER.CLIENT_CODE, dbo_TAL_ADJ.ADJ_DESC,
dbo_ACT_TRAN.Trans_date, " & _
" p.EMPLOYEE_NAME, p.EMPLOYEE_CODE " & _
"HAVING (dbo_BLH_BILLED_FEES.PERIOD >= " & txtFromPeriod & " ) " &
_
" AND (dbo_BLH_BILLED_FEES.PERIOD <= " & txtToPeriod & " ) " & _
" AND (SUM(dbo_BLH_BILLED_FEES.BILLED_AMT -
dbo_BLH_BILLED_FEES.BASE_AMT) <> 0) " & _
" AND ((dbo_HBM_MATTER.CLIENT_CODE <>'9999') AND
dbo_HBM_MATTER.CLIENT_CODE <> '9901')"- Hide quoted text -

- Show quoted text -
Click to expand...

Never mind found error in group by clause
 
Back
Top