Solved

Adding Criteria to a Delete Statement

Posted on 2014-09-22
15
80 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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
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

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

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

Suggested Solutions

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
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…

776 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