asked on 10/8/2011

Delete rows based on value of cell

Hi Experts

Not sure how to explain what I'm trying to get here. But, in the attached databased. I have the original table with rows that contain the text (No Itemized, etc.) in the Comments Column. Now I've appended the table and now have rows that contain text (Capacity) in the Comments Column. I need to delete the row that has the text (No Itemized, etc), and keep the row that has the text (Capacity).

See attached for clarification.

Original Table
Original Results
test11.accdb

Microsoft Access SQL

Last Comment

mbizup

10/8/2011

peter57r

10/8/2011

Your explanation is not sufficient.

You have also kept records that have 'Timing' as well as 'Capacity' and you have deleted records that say 'No Items' as well as ones that have Not itemized this week*"

You need to explain the rules in full.

Based on what you have posted it seems simpler to look at the Constraint column or maybe the Duration column and delete any row that has no value in that column?

mato01

10/8/2011

ASKER

Thats what I need to delete rows that have

'No Items' as well as ones that have Not itemized this week*

mbizup

10/8/2011

If you are simply trying to delete rows where the text is "No Items" or "Not Itemized" and keep anything else, this will give you the reults in your results table:

DELETE * 
FROM [tbl_Output - Original]
WHERE [Comments] = "No Items" OR [Comments] LIKE "Not Item*"

Open in new window

In VBA:

dim strSQL as string
strSQL = "DELETE * " _ 
            & "FROM [tbl_Output - Original] " _
            & "WHERE [Comments] = 'No Items' OR [Comments] LIKE 'Not Item*'"
Currentdb.execute strSQL, dbfailon error

Open in new window

Also, it looks like there are other criteria that may be more fitting, especially if there are other hand-entered variations on the comments field. For example, [constraint number] and other fields are NULL in records you seem to want deleted. If this is a consistent pattern, that might be a better option for your criteria.

mato01

10/8/2011

ASKER

You're right. And, I haven't been quite clear. Even though I know in my mind what I want to do. Your VBA works fine, but I only want to delete 'No Items' as well as ones that have Not itemized this week* if there is another instance.

See example, for AllGrp (Jeff). I want to leave this one because there is no other records reference to it in the table.

See example, for AllGrp (Carm). I want to delete the reference to "Not Itemized this week" because there is another reference in the table that is itemized.

Still not sure If I'm clear. Either way thanks for your help.

test111.accdb

ASKER CERTIFIED SOLUTION

mbizup

10/8/2011

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