Unable to edit query with an unselected subquery

[BlueJacket]

I can't edit this decently simple query and I don't understand why. Can you just not edit a query that has a subquery in it? Is it as simple as that? According to http://allenbrowne.com/ser-61.html, as long as the subquery isn't in the SELECT clause, then everything should be good.

SELECT tblPropertyDetails.PropertyID, tblPropertyDetails.BarmentSentDate, tblPropertyDetails.BarmentDeadline, DateAdd("yyyy",1,[tblPropertyDetails]![Sale]) AS RipenDate
FROM tblPropertyDetails LEFT JOIN qryClosed ON tblPropertyDetails.PropertyID = qryClosed.PropertyID
WHERE (((qryClosed.Closed)="0"));

Is it worth mentioning that the field in the subquery is a calculated field?

Thanks in advance.

 

[Ranman256]

SOME queries cant be edited due to the joins.
Outer joins are most likely the reason.

(note if CLOSED is a true/false field, then it will never equal the string : ="0"

 

[BlueJacket]

I changed the joins, but it made no difference. The Closed field was not a yes/no field, since I was using a query to calculate if an investment was closed (1) or still open (0) based off of certain events, but I eventually just created a yes/no field in my main table to do just that.

 

[ByteMyzer]

You might try:

SELECT
    tblPropertyDetails.PropertyID,
    tblPropertyDetails.BarmentSentDate,
    tblPropertyDetails.BarmentDeadline,
    DateAdd("yyyy", 1, [tblPropertyDetails]![Sale]) AS RipenDate
FROM tblPropertyDetails
WHERE EXISTS (
    SELECT qryClosed.PropertyID
    FROM qryClosed
    WHERE qryClosed.PropertyID = tblPropertyDetails.PropertyID
    AND qryClosed.Closed = '0');

 

[CJ_London]

you can try changing the recordset type to 'dynaset inconsistent updates' but will be limited to the table to the left side of the join (tblPropertyDetails).

But your dateadd field may also be a problem. You would be better to bring through the Sale field in your query and apply the dateadd function in an unbound control.

Another way is

WHERE tblPropertyDetails.PropertyID IN (
SELECT PropertyID
FROM qryClosed
WHERE Closed = '0');

Just out of curiosity, I would have thought Closed would be a Boolean so I would expect to see either

WHERE qryClosed.Closed = False

or

WHERE NOT qryClosed.Closed