What is wrong with this code?

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' "
 
D

Dale Fye

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
 
C

confused

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.
 
C

confused

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
 
C

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')"
 
C

confused

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Please, What is wrong with this Query? 6

Top