Avatar of PeterBaileyUk
PeterBaileyUk

asked on 

access delete query

I have this query

SELECT AbiToMvris_NEW_.[MVRIS CODE], SMMT.[MVRIS CODE]
FROM AbiToMvris_NEW_ LEFT JOIN SMMT ON AbiToMvris_NEW_.[MVRIS CODE] = SMMT.[MVRIS CODE]
WHERE (((SMMT.[MVRIS CODE]) Is Null));

Open in new window


I would like to delete the rows in AbiToMvris_NEW_ table  where the AbiToMvris_NEW_ .[MVRIS CODE] is not in the SMMT table.

always get stumped on these

I want to do this in vba
Microsoft AccessSQL

Avatar of undefined
Last Comment
Rgonzo1971
Avatar of Rgonzo1971
Rgonzo1971

Hi,

pls try

Dim MySQL as String
MySQL = "DELETE FROM AbiToMvris_NEW_ LEFT JOIN SMMT ON AbiToMvris_NEW_.[MVRIS CODE] = SMMT.[MVRIS CODE]" & _
    "WHERE (((SMMT.[MVRIS CODE]) Is Null));"
CurrentProject.Connection.Execute MySQL

Open in new window


Be sure to make a backup first

Regards
Try this

Delete A
FROM AbiToMvris_NEW_ A LEFT JOIN SMMT B ON A.AbiToMvris_NEW_.[MVRIS CODE] = B.SMMT.[MVRIS CODE]
WHERE (((A.SMMT.[MVRIS CODE]) Is Null));
ASKER CERTIFIED SOLUTION
Avatar of Rgonzo1971
Rgonzo1971

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Microsoft Access
Microsoft Access

Microsoft Access is a rapid application development (RAD) relational database tool. Access can be used for both desktop and web-based applications, and uses VBA (Visual Basic for Applications) as its coding language.

226K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews

TRUSTED BY

IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo