Solved

Adding Criteria to a Delete Statement

Posted on 2014-09-22
15
79 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
  • 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
 
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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
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

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

920 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now