Statement Syntax Error

**Alex O** · 05-27-2012, 12:21 PM

I'm completely new to Access and writing SQL statements, and stuck on one of my first projects. I've written the statement below (which is part of a larger statement) but I keep getting a syntax error ')' near line 6 error. Can anyone please help me figure out what/where the problem is?

Thanks,

SELECT DEBT_ID, MAX(PMT_DATE) AS MAXOFPMT_DATE FROM
DM.PMT
WHERE CLT230_VIEW.CLT_ID = PMT.CLT_ID
AND DATEFORMAT(PMT_DATE,'YYYY-MM') = PERIOD) AND CLT_ID IN
('01006557', '01010322','01010325','01010326',
'01010327','01010328','01010329',
'01010461','01010462','01010463',
'01010464','01010465','01010466',
'01011242','01011243','01011409',
'01011410','01011852','01011853',
'01011854','01011855','01011856',
'01011857','01011858','01011859',
'01011890','01011891','01011675',

'01012719','01012720','01012860',
'01012861','01012890','01012891',
'01012892','01012893','01012894',
'01012895','01012888','01012889',
'01012957','01012958','01013048',
'01013225','01013230','01013231')
AND DATEFORMAT(PMT_DATE,'YYYY-MM') BETWEEN '2010-
04' AND '2012-03'
GROUP BY
DEBT_ID, DEBTOR_ID;

**NTC** · 05-27-2012, 12:57 PM

My general advice for a new person in this area - is to use the design view to create a query....make sure it works ok...and then put it in SQL view mode in order to see the syntax....and then go from there.

Hope this helps,

**John_G** · 05-28-2012, 10:22 PM

Hi -

There are too many ")" in this line:

AND DATEFORMAT(PMT_DATE,'YYYY-MM') = PERIOD) AND CLT_ID IN

Remove the one highlighted in red to see if that helps.

But, since you said that this is part of a larger statement (in what context?), then the error could be anywhere (I have never seen A2003 give a message like that, with a line number).

By itself, that statement (probably) won't work because:

a) what is period?
b) you have DEBTOR_ID in the group by but not the Select (might not give an error - but why do you have it?)
c) your whole WHERE clause makes no sense - you have DATEFORMAT(PMT_DATE,'YYYY-MM') = PERIOD, but then you also have
DATEFORMAT(PMT_DATE,'YYYY-MM') BETWEEN '2010-04' AND '2012-03' Which is it? Or do you mean PERIOD is between those dates?

Please post the whole statement - it will be a lot easier for us to help you out.

John

**Alex O** · 05-29-2012, 08:39 AM

John,
Let me first start by thanking you for your willingness to help. I've been stuck for three days now with no clue how to correct the issue! So the complete statement is below. The statement is designed to create a report that provides portfolio performance by period. Everything seems to correct, except the section in bold (which I tried to correct using the original statement posted). What I'm trying to get is the number of payees, not payments. So if debtor A made 1 payment and debtor B made 3 for the referenced period, the total payments for the period would be 2. Does this make sense?

SELECT PERIOD, LD_LIST_ACCTSAS GROSS_REFERAL_NBR,
LD_LIST_AMT ASGROSS_REFERAL_AMT,

(SELECT COUNT(*) FROM DM.DEBT
WHERE CLT230_VIEW.CLT_ID =DEBT.CLT_ID
ANDDATEFORMAT(LIST_DATE,'YYYY-MM') = PERIOD
AND STATUS_CODE IN ('503','506','508','509','600','601'))AS WITHDRAWLS_ADJUSTMENTS,

(SELECTCOALESCE(SUM(LIST_AMT),0) FROM DM.DEBT_VIEW AS DEBT
WHERE CLT230_VIEW.CLT_ID =DEBT.CLT_ID
AND DATEFORMAT(LIST_DATE,'YYYY-MM')= PERIOD
AND STATUS_CODE IN ('503','506','508','509','600','601'))AS WITHDRAWLS_AMT,

(SELECT COUNT(*) FROM DM.DEBT
WHERE CLT230_VIEW.CLT_ID =DEBT.CLT_ID
AND DATEFORMAT(LIST_DATE,'YYYY-MM')= PERIOD
AND STATUS_CODE IN('503','506','508','509','600','601')) AS NBR_NET_REFERRAL,

(SELECTCOALESCE(SUM(LIST_AMT),0) FROM DM.DEBT_VIEW AS DEBT
WHERE CLT230_VIEW.CLT_ID =DEBT.CLT_ID
AND DATEFORMAT(LIST_DATE,'YYYY-MM')= PERIOD
AND STATUS_CODE IN('503','506','508','509','600','601')) AS NET_REFERRAL_AMTS,

(SELECT SUM(PRINC_AMT) FROMDM.PMT_VIEW AS PMT
WHERE CLT230_VIEW.CLT_ID =PMT.CLT_ID
AND CLT230_VIEW.PERIOD =DATEFORMAT(PMT_DATE,'YYYY-MM')
ANDDATEFORMAT(DEBT_LIST_DATE,'YYYY-MM') = PERIOD
AND COALESCE(BACKOUT_ID,'') ='') AS PRIOR_MONTH,
LD_GROSS AS AMOUNT_COLLECTED,

(SELECT COUNT(*) FROM DM.PMT WHERE
CLT230_VIEW.CLT_ID = PMT.CLT_ID
AND DATEFORMAT(PMT_DATE,'YYYY-MM') = PERIOD)
AS NBR_ACCTS_PAID,

(SELECT COUNT(*) FROM DM.DEBT
WHERE CLT230_VIEW.CLT_ID =DEBT.CLT_ID
AND DATEFORMAT(LIST_DATE,'YYYY-MM')= PERIOD
AND STATUS_CODE < '300')AS AGENCY_OPEN_NBR,
COALESCE((SELECT SUM(PRINC_BAL)FROM DM.DEBT_VIEW AS DEBT
WHERE CLT230_VIEW.CLT_ID =DEBT.CLT_ID
AND DATEFORMAT(LIST_DATE,'YYYY-MM')= PERIOD
AND STATUS_CODE <'300'),0) AS AGENCY_OPEN_AMT,

(SELECT COUNT(*) FROM DM.DEBT
WHERE CLT230_VIEW.CLT_ID =DEBT.CLT_ID
AND DATEFORMAT(LIST_DATE,'YYYY-MM')= PERIOD
AND STATUS_CODE NOT IN('360') AND STATUS_CODE BETWEEN '300' AND '399') AS
ATTORNEY_OPEN_NBR,

COALESCE((SELECTSUM(PRINC_BAL) FROM DM.DEBT_VIEW AS DEBT
WHERE CLT230_VIEW.CLT_ID =DEBT.CLT_ID
AND DATEFORMAT(LIST_DATE,'YYYY-MM')= PERIOD
AND STATUS_CODE NOT IN('360') AND STATUS_CODE BETWEEN '300' AND '399'),0) AS
ATTORNEY_OPEN_AMT,

(SELECT COUNT(*) FROM DM.DEBT
WHERE CLT230_VIEW.CLT_ID =DEBT.CLT_ID
AND DATEFORMAT(LIST_DATE,'YYYY-MM')= PERIOD
AND ((STATUS_CODE BETWEEN '400'AND '499') OR
(STATUS_CODEIN('501','502','505')))) AS CLOSED_UNCOLLECTED_NBR,

COALESCE((SELECTSUM(PRINC_BAL_B4_CLS) FROM DM.DEBT_VIEW AS
DEBT WHERE CLT230_VIEW.CLT_ID= DEBT.CLT_ID
AND DATEFORMAT(LIST_DATE,'YYYY-MM')= PERIOD
AND ((STATUS_CODE BETWEEN'400' AND '499') OR
(STATUS_CODEIN('501','502','505')))),0) AS CLOSED_UNCOLLECTED_AMT
FROM DM.CLT230_VIEW
WHERE CLT_ID IN ('01006557',
'01010322','01010325','01010326',
'01010327','01010328','01010329',
'01010461','01010462','01010463',
'01010464','01010465','01010466',
'01011242','01011243','01011409',
'01011410','01011852','01011853',
'01011854','01011855','01011856',
'01011857','01011858','01011859',
'01011890','01011891','01011675',
'01012719','01012720','01012860',
'01012861','01012890','01012891',
'01012892','01012893','01012894',
'01012895','01012888','01012889',
'01012957','01012958','01013048',
'01013225','01013230','01013231')AND
PERIOD BETWEEN '2010-04' AND'2012-03'
AND GROSS_REFERAL_NBR > 0
ORDER BY PERIOD

**John_G** · 05-29-2012, 11:56 AM

A lot to chew on there!!!

The first thing I noticed is that there are missing spaces all over the place in the statement, starting with the first line -

SELECT PERIOD, LD_LIST_ACCTSAS GROSS_REFERAL_NBR,
should be
SELECT PERIOD, LD_LIST_ACCTS AS GROSS_REFERAL_NBR,

(space before "as").

If the spaces really are missing, no wonder there are errors.

Other than that, I can't see anything obvious - the brackets seem to be balanced. Is Status_Code numeric or text?

One thing you could try is running each of the sub-queries by themselves, to see if they work - it might isolate the problem.

HTH

John

**Alex O** · 05-29-2012, 12:51 PM

There are definately some missing spaces. I'm cleaning it up now. I tried your earlier suggestion about removing the bracket (running the MAXOfPAYMENT) statement, and received an error message stating Correlation name "CLT230_VIEW" not found. Any idea what that means?
With my main (and rather lengthy) statement, do you have any thought or suggestions on how to get the (SELECT COUNT(*) FROM DM.PMT WHERE
CLT230_VIEW.CLT_ID = PMT.CLT_ID AND DATEFORMAT(PMT_DATE,'YYYY-MM') = PERIOD) AS NBR_ACCTS_PAID, statement to return the number of payees rather than the number of payments?

**John_G** · 05-29-2012, 01:40 PM

Hi -

Given the whole statement to look at, the extra ")" was in fact required.

The get the number of payees rather than the number of payments, something along these lines might work:

Select count(*) as AS NBR_ACCTS_PAID from

(Select distinct CLT_ID from DM.PMT WHERE
CLT230_VIEW.CLT_ID = PMT.CLT_ID AND DATEFORMAT(PMT_DATE,'YYYY-MM') = PERIOD) )

I can't claim that as being my own idea - I got it from this forum thread:

http://www.accessmonster.com/Uwe/For...istinct-Values

That site has helped me out many times.

Let us know how that solution works!

John

**RayMilhon** · 05-29-2012, 02:15 PM

Looking at your query the from clauses and where clauses don't quite make sense to me.

Code:

(SELECT COUNT(*) 
FROM DM.PMT 
WHERE CLT230_VIEW.CLT_ID = PMT.CLT_ID 
AND DATEFORMAT(PMT_DATE,'YYYY-MM') = PERIOD) 
AS NBR_ACCTS_PAID

In the above subquery you have DM.PMT in the From Clause. but in the where clause you have PMT.CLT_ID In the main from clause you again have the DM.CLT230_View but leave of the DM. in the Where of the sub query. It may not make a difference but it's been my experience that the Table portion of the Select, Where, Group by and Order by should match the from.

**Alex O** · 06-08-2012, 08:12 AM

Thanks for all of the input and suggestions. After taking some time to better understand how the statement was performing (vs what I was trying to accomplish) I opted to just start over. I have successfully crafted something that's doing exactly what I needed, thanks in no small part to all of the input received here. Thanks to everyone for providing some direction!

Statement Syntax Error

Thread Tools

Statement Syntax Error

Similar Threads

Syntax error in UPDATE statement HELP!

bad syntax error on sql statement used in a openrecordset statement

IIf Statement Syntax Error

Incomplete Syntax Clause (syntax error)

Syntax Error In Insert Into Statement

Posting Permissions