Query with "In" in the WHERE clause isn't updateable

[Peter Stone]

Access 2003 XP Pro.

The following query is the Record Source for a continuous subform. The
query selects the correct records, but it isn't updateable.

From the combo on the form, I want to select into tjnDestPtf which has just
the two fields shown in the SELECT statement below.

SELECT tjnDestPtf.PtfDestID, tjnDestPtf.DestID
FROM (tblDest INNER JOIN tjnDestGeo ON tblDest.DestID = tjnDestGeo.DestID)
INNER JOIN tjnDestPtf ON tblDest.DestID = tjnDestPtf.PtfDestID
WHERE (((tjnDestGeo.CatID) In (504,509)))
GROUP BY tjnDestPtf.PtfDestID, tjnDestPtf.DestID
HAVING (((Count(*))=2));

I guess it's because the query result is a construction.

Any suggestions would be appreciated.

Thank you

Peter Stone

 

[John W. Vinson]

Access 2003 XP Pro.

The following query is the Record Source for a continuous subform. The
query selects the correct records, but it isn't updateable.

From the combo on the form, I want to select into tjnDestPtf which has just
the two fields shown in the SELECT statement below.

SELECT tjnDestPtf.PtfDestID, tjnDestPtf.DestID
FROM (tblDest INNER JOIN tjnDestGeo ON tblDest.DestID = tjnDestGeo.DestID)
INNER JOIN tjnDestPtf ON tblDest.DestID = tjnDestPtf.PtfDestID
WHERE (((tjnDestGeo.CatID) In (504,509)))
GROUP BY tjnDestPtf.PtfDestID, tjnDestPtf.DestID
HAVING (((Count(*))=2));

I guess it's because the query result is a construction.

Any suggestions would be appreciated.

Thank you

Peter Stone

It's because it's a grouped query. No query containing a GROUP BY clause is
ever updateable. Try using DCount() instead of the joined query.