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
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
ASKER
Thats what I need to delete rows that have
'No Items' as well as ones that have Not itemized this week*
'No Items' as well as ones that have Not itemized this week*
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:
In VBA:
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.
DELETE *
FROM [tbl_Output - Original]
WHERE [Comments] = "No Items" OR [Comments] LIKE "Not Item*"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 errorAlso, 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.
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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?