Solved

Adding Criteria to a Delete Statement

Posted on 2014-09-22
15
82 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
MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

 
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

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

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

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

707 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