Solved

Adding Criteria to a Delete Statement

Posted on 2014-09-22
15
81 Views
Last Modified: 2014-09-24
this statement works

DoCmd.RunSQL "Delete [CMTS_Daily_Extract].[CMMT_NUM] FROM [CMTS_Daily_Extract] WHERE [CMTS_Daily_Extract]![CMMT_NUM] IN (SELECT [DupsWithOnlyMINTime].[CMMT_NUM] FROM [DupsWithOnlyMINTime]);"

Want to add
'AND where([CMTS_Daily_Extract].[CMMT_NUM]=[DupsWithOnlyMINTime].[CMMT_NUM]);"
'AND ([CMTS_Daily_Extract].[TakenTime]=[DupsWithOnlyMINTime].[TakenTime]);"

I tried many ways and not found anything to work.
.  I would appreciated you help!
0
Comment
Question by:ca1358
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 7
15 Comments
 
LVL 15

Expert Comment

by:Haris Djulic
ID: 40337908
Hello,

you have error in this statement :

'AND where([CMTS_Daily_Extract].[CMMT_NUM]=[DupsWithOnlyMINTime].[CMMT_NUM]);"

it should be like this :

'AND ([CMTS_Daily_Extract].[CMMT_NUM]=[DupsWithOnlyMINTime].[CMMT_NUM]);"

complete statement is:

DoCmd.RunSQL "Delete [CMTS_Daily_Extract].[CMMT_NUM] FROM [CMTS_Daily_Extract] 
WHERE [CMTS_Daily_Extract]![CMMT_NUM] IN (SELECT [DupsWithOnlyMINTime].[CMMT_NUM] FROM [DupsWithOnlyMINTime])
AND ([CMTS_Daily_Extract].[CMMT_NUM]=[DupsWithOnlyMINTime].[CMMT_NUM])
AND ([CMTS_Daily_Extract].[TakenTime]=[DupsWithOnlyMINTime].[TakenTime]);"

Open in new window

0
 

Author Comment

by:ca1358
ID: 40337925
I getting this

Enter Parameter Value
SupsWithOnlyMinTime.CMMT_Num
0
 

Author Comment

by:ca1358
ID: 40337926
I getting this

Enter Parameter Value
DupsWithOnlyMinTime.CMMT_Num
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 15

Expert Comment

by:Haris Djulic
ID: 40337936
try this :

DoCmd.RunSQL "Delete [CMTS_Daily_Extract].[CMMT_NUM] FROM [CMTS_Daily_Extract] 
WHERE [CMTS_Daily_Extract]![CMMT_NUM] IN (SELECT [DupsWithOnlyMINTime].[CMMT_NUM] FROM [DupsWithOnlyMINTime])
AND ([CMTS_Daily_Extract]![CMMT_NUM]=[DupsWithOnlyMINTime].[CMMT_NUM])
AND ([CMTS_Daily_Extract]![TakenTime]=[DupsWithOnlyMINTime].[TakenTime]);"

Open in new window

0
 

Author Comment

by:ca1358
ID: 40337957
Still getting this I getting this

Enter Parameter Value
DupsWithOnlyMinTime.CMMT_Num
Microsfot Visual Basic Run-time error '3021'
Reserved Error
0
 
LVL 15

Expert Comment

by:Haris Djulic
ID: 40337966
now try this:

DoCmd.RunSQL "Delete [CMTS_Daily_Extract].[CMMT_NUM] FROM [CMTS_Daily_Extract] 
WHERE [CMTS_Daily_Extract]![CMMT_NUM] IN (SELECT [DupsWithOnlyMINTime].[CMMT_NUM] FROM [DupsWithOnlyMINTime])
AND ([CMTS_Daily_Extract]![CMMT_NUM]=(SELECT [DupsWithOnlyMINTime].[CMMT_NUM] FROM [DupsWithOnlyMINTime]))
AND ([CMTS_Daily_Extract]![TakenTime]=(SELECT [DupsWithOnlyMINTime].[TakenTime] FROM [DupsWithOnlyMINTime]));"

Open in new window

0
 

Author Comment

by:ca1358
ID: 40337989
DoCmd.RunSQL "Delete [CMTS_Daily_Extract].[CMMT_NUM] FROM [CMTS_Daily_Extract] WHERE [CMTS_Daily_Extract]![CMMT_NUM] IN (SELECT [DupsWithOnlyMINTime].[CMMT_NUM] FROM [DupsWithOnlyMINTime])AND ([CMTS_Daily_Extract]![CMMT_NUM]=(SELECT [DupsWithOnlyMINTime].[CMMT_NUM] FROM [DupsWithOnlyMINTime])AND ([CMTS_Daily_Extract]![TakenTime]=(SELECT [DupsWithOnlyMINTime].[TakenTime] FROM [DupsWithOnlyMINTime]);"


getting runtime error run-time error 3075
0
 
LVL 15

Expert Comment

by:Haris Djulic
ID: 40338002
Hi,

the issue here is that I don't know the structure of the data and what you want to filter in where clause, i.e. I'm not sure to what you are linking these conditions...

Can you provide the sample data from tables involved in this statement .
0
 

Author Comment

by:ca1358
ID: 40338031
0
 
LVL 15

Expert Comment

by:Haris Djulic
ID: 40338048
Hello,

are these the rows which  you want to delete :

SELECT a.cmmt_num, a.takentime
FROM CMTS_Daily_Extract a, DupsWithOnlyMINTime b
where a.cmmt_num=b.cmmt_num
and a.takentime=b.takentime;

Open in new window

0
 

Author Comment

by:ca1358
ID: 40338058
I want to delete the entire record of CMTS_Daily_Extract which match CMMT_NUM&only the Minimum TakenTime out DupsWithOnlyMinTime Table
0
 

Author Comment

by:ca1358
ID: 40338061
I want to delete the entire record of CMTS_Daily_Extract which match CMMT_NUM&only the Minimum TakenTime out DupsWithOnlyMinTime Table
Leaqving the Max TakenTime in CMTS_Daily_Extract
0
 
LVL 15

Expert Comment

by:Haris Djulic
ID: 40338079
Hello,

a lot of issues here but i figure it out..

Here is the final result/query :
delete from CMTS_Daily_Extract
where id in (SELECT CMTS_Daily_Extract.id
FROM CMTS_Daily_Extract INNER JOIN DupsWithOnlyMINTime ON (CMTS_Daily_Extract.TakenTime = DupsWithOnlyMINTime.TakenTime) AND (CMTS_Daily_Extract.CMMT_NUM = DupsWithOnlyMINTime.CMMT_NUM));

Open in new window


I added the ID column and made it primary key to help with the process...

I attached the access with saved tables and query....

Hopefully this solution is ok for you..
Del.mdb
0
 
LVL 15

Accepted Solution

by:
Haris Djulic earned 500 total points
ID: 40340261
Hello,

did the solution worked?
0
 

Author Closing Comment

by:ca1358
ID: 40342218
Thank you! Sorry for being late was out sick yesterday.
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

696 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question