Sam OZ
asked on
MS Access - Delete all rows after a certain value for a given column
I have a table (which is exported from Excel) .
I know that the column by name F1 will have the value starting with "Representation" . All the rows from that row (including that row) need to be deleted ( But I don't have the count of rows )
Can you help me with a query for this ?
Infact , This is part of a Access macro . So using VBA also is an option
Thanks
I know that the column by name F1 will have the value starting with "Representation" . All the rows from that row (including that row) need to be deleted ( But I don't have the count of rows )
Can you help me with a query for this ?
Infact , This is part of a Access macro . So using VBA also is an option
Thanks
ASKER
Thanks , But that is not what I am looking for . For example, If the 20th row has the column F1 Like "Representation*" , then all the rows from the row 20 should be deleted
I get a feeling this need to be done with Recordset . Loop thru RS ..Look for the row . When found delete all the rows from there as the loop goes thru
I get a feeling this need to be done with Recordset . Loop thru RS ..Look for the row . When found delete all the rows from there as the loop goes thru
for a record there is only ONE row.
the query I posted will delete the records with "Representation" in the F1 column
the query I posted will delete the records with "Representation" in the F1 column
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
can you post the codes that you used, so others may learn other ways of deleting records
ASKER
This is the code .
Private Function DeleteRows(dbobj As dao.Database)
Dim MyRs As dao.Recordset
Dim MyTableDef As TableDef
Dim Sql As String
Dim TableName As String
Dim PropertyValue As String
Dim bDeleteFlag As Boolean
For Each MyTableDef In dbobj.TableDefs
TableName = MyTableDef.Name
bDeleteFlag = False
''Routine to check validity of name
If IsValidTable(TableName) = False Then
GoTo NextTable
End If
Sql = " select " & SPFPropertyColumnName & " from " & TableName
Set MyRs = dbobj.OpenRecordset(Sql, dbOpenDynaset)
While Not MyRs.EOF
If Not IsNull(MyRs(PropertyColumn Name)) Then
PropertyValue = MyRs(PropertyColumnName)
Else
PropertyValue = ""
End If
'' Calling function to Check the propertyvalue starts with Junkvalue
If CheckStartsWith(UCase(Prop ertyValue) , UCase(JunkValueStart)) = True Then
bDeleteFlag = True
End If
If bDeleteFlag = True Then
MyRs.Delete
ElseIf PropertyValue = "" Then
MyRs.Delete
End If
MyRs.MoveNext
Wend
If Not MyRs Is Nothing Then
Set MyRs = Nothing
End If
NextTable:
Next
End Function
Private Function DeleteRows(dbobj As dao.Database)
Dim MyRs As dao.Recordset
Dim MyTableDef As TableDef
Dim Sql As String
Dim TableName As String
Dim PropertyValue As String
Dim bDeleteFlag As Boolean
For Each MyTableDef In dbobj.TableDefs
TableName = MyTableDef.Name
bDeleteFlag = False
''Routine to check validity of name
If IsValidTable(TableName) = False Then
GoTo NextTable
End If
Sql = " select " & SPFPropertyColumnName & " from " & TableName
Set MyRs = dbobj.OpenRecordset(Sql, dbOpenDynaset)
While Not MyRs.EOF
If Not IsNull(MyRs(PropertyColumn
PropertyValue = MyRs(PropertyColumnName)
Else
PropertyValue = ""
End If
'' Calling function to Check the propertyvalue starts with Junkvalue
If CheckStartsWith(UCase(Prop
bDeleteFlag = True
End If
If bDeleteFlag = True Then
MyRs.Delete
ElseIf PropertyValue = "" Then
MyRs.Delete
End If
MyRs.MoveNext
Wend
If Not MyRs Is Nothing Then
Set MyRs = Nothing
End If
NextTable:
Next
End Function
> If the 20th row has the column F1 Like "Representation*" ,
> then all the rows from the row 20 should be deleted
I can't see where this is brought into play.
Your code seems, basically, to do the same as Rey's query.
/gustav
> then all the rows from the row 20 should be deleted
I can't see where this is brought into play.
Your code seems, basically, to do the same as Rey's query.
/gustav
ASKER
If the rows are like
======= START OF ROWS ======================
Myr1
Myr2
...
...
Myr18
Representation : This is explaining the obvious
This is a row after row20
But the row 21 ,22,23 is blah blah
So 20,21,22,23 should be deleted.
======= END OF ROWS ======================
Row 19 is a row with no value .
The SQL will delete only row 20
My code will delete row 19 (because it is empty) , 20,21,22,23
( It is because after the Deleteflag is set to true all the rows are deleted until the end)
======= START OF ROWS ======================
Myr1
Myr2
...
...
Myr18
Representation : This is explaining the obvious
This is a row after row20
But the row 21 ,22,23 is blah blah
So 20,21,22,23 should be deleted.
======= END OF ROWS ======================
Row 19 is a row with no value .
The SQL will delete only row 20
My code will delete row 19 (because it is empty) , 20,21,22,23
( It is because after the Deleteflag is set to true all the rows are deleted until the end)
That is true. Missed that. Thanks.
/gustav
/gustav
ASKER
The solution is my previous comment . Comment from the other expert didn't help, unfortunately
delete * From [Name Of Table]
where [F1] Like "Representation*"