Access VBA SQL problem

[Magius96]

I need to delete records from my table when a field in another table does not
contain a certain value. I have the following SQL that selects the correct
accounts, but when I try to run it, it says it can't delete records from the
table. I can't figure out why!

DELETE PLD.* FROM AIS RIGHT JOIN PLD ON AIS.DEBTOR_FILENO =
PLD.DEBTOR_FILENO WHERE (((IIf([AIS].[ED_CUR_COND]<>'REHAB' Or
[AIS].[ED_FALLOUT_DT] Is Not Null Or
[AIS].[ED_FALLOUT_DT]>[PLD].[ED_COND_OPEN_DT] Or [AIS].[ED_FALLOUT_RSN] Is
Not Null,1,0))=1))

Can anyone give me some directive to get this working?
In english, Records should be deleted from the PLD table if in the AIS
table the condition is not 'REHAB', or there is a Fallout date that is after
the setup date, or there is a fallout reason.

The three fields being referenced in the AIS table do not exist in the PLD
table. The two tables are linked using file numbers. We can't create
relations in the database, and have to setup the relations on a query by
query basis because some queries require the relations to be set differently.

 

[Alex Dybenko]

Hi,
first try use distinctrow as here:
http://accessblog.net/2004/10/access-delete-update-sql-in-jet.html

second - try to use left join:

FROM PLD LEFT JOIN AIS

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

 

[John Spencer]

STEP 1: BACKUP your data before attempting the following.
STEP 2: BACKUP your data before attempting the following.

Without a backup you cannot restore the data if this does not work the way you
expect.

DELETE PLD.*
FROM PLD
WHERE DEBTORFileNO IN
(SELECT AIS.DebtorFileNo
FROM AIS RIGHT JOIN PLD
ON AIS.DEBTOR_FILENO = PLD.DEBTOR_FILENO
WHERE IIf([AIS].[ED_CUR_COND]<>'REHAB' Or
[AIS].[ED_FALLOUT_DT] Is Not Null Or
[AIS].[ED_FALLOUT_DT]>[PLD].[ED_COND_OPEN_DT] Or
[AIS].[ED_FALLOUT_RSN] Is Not Null,1,0)=1)

I can't understand why you have used an IIF expression in the where clause. I
would think it would have been more straightforward without the complexity of
the IIF.
SELECT AIS.DebtorFileNo
FROM AIS RIGHT JOIN PLD
ON AIS.DEBTOR_FILENO = PLD.DEBTOR_FILENO
WHERE IIf([AIS].[ED_CUR_COND]<>'REHAB' Or
[AIS].[ED_FALLOUT_DT] Is Not Null Or
[AIS].[ED_FALLOUT_DT]>[PLD].[ED_COND_OPEN_DT] Or
[AIS].[ED_FALLOUT_RSN] Is Not Null


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[Steve Sanford]

STEP 1: BACKUP your data before attempting the following.
STEP 2: BACKUP your data before attempting the following.

And don't forget:

STEP 3: BACKUP your data


From (the book you wish you had read): "The 3 Rules of Computing"