Statement Help

[Alex O]

I'm fairly 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;

[Bob Phillips]

It looks to be you are implicitly joining two tables and you are referencing a field in both without saying which one.

I would think it should be along the lines of

SELECT DEBT_ID, MAX(PMT_DATE) AS MAXOFPMT_DATE
FROM CLT230_VIEW, PMT
WHERE CLT230_VIEW.CLT_ID = PMT.CLT_ID
AND DATEFORMAT(PMT_DATE,'YYYY-MM') = PERIOD)
AND PMT.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;

[stanl]

or an INNER JOIN clause....

[Bob Phillips]

or an INNER JOIN clause....

Indeed. I just worked with what was there