remove bad rows to another table

  • Thread starter Thread starter Terry Thomas
  • Start date Start date
T

Terry Thomas

Hi

I would like to go through all of the records and remove
any rogues from the main table which do not comply to
having every field filled in.

My table holding the records is named tblvisits. I would
like to transfer out any of the bad rows to another table
named - tblvisitbadrows. This table has exactly the same
structure as the tblvisits and I will use it to collect
the bad rows in, this will keep my data clean for export
and provide me a way of seeing how many of these records
are being created.

Any help would be appreciated. I already have my
tblvisitbadrows made.

Terry Thomas
 
Hi

I would like to go through all of the records and remove
any rogues from the main table which do not comply to
having every field filled in.

My table holding the records is named tblvisits. I would
like to transfer out any of the bad rows to another table
named - tblvisitbadrows. This table has exactly the same
structure as the tblvisits and I will use it to collect
the bad rows in, this will keep my data clean for export
and provide me a way of seeing how many of these records
are being created.

Any help would be appreciated. I already have my
tblvisitbadrows made.
Click to expand...

An Append query followed by a Delete query will do this. Create a
Query based on tblVisits; apply whatever criteria define a "bad
record" - such as putting IS NULL on the criteria line under each
required field, on separate rows of the query grid (so that OR logic
will select the record if any one of the fields is null). Make two
copies of this query; change one to an Append query and the other to a
Delete query. BACK UP YOUR DATABASE, just in case; then run the two
queries in succession.

If this is an ongoing problem, you may want to consider making these
fields Required, or putting code in the data entry form's BeforeUpdate
event to prevent adding such records in the first place. It's much
easier to keep them out than to remove them once they get in, just
like gatecrashers at a party. <g>

John W. Vinson[MVP]
 
Back
Top