Ignoring null or black fields

[AndieV]

I have a database setup in Access97 that contains a linked table that is coded to delete and update its link to the various dbf files that were created previously.

I have all of the reports, queries and forms based on this transitional database and everything was running smoothly until the user called up a dbf table that had blank fields. Then none of the reports would run.

Is there a way to code the file to either sweep the table when it opens and remove any blank fields or to ignore them when it runs the reports.

Right now, I can go in and remove the rows that are blank to get the reports to run, but with over 1000 companies, this could be a very time consuming process if it has to be done for all of them.

If someone could help, it would be greatly appreciated, or if you need clarification.

Andie

 

[Lightning]

A suggestion:

When the link has been deleted and renewed, open the transition table as a Recordset ("Table" type) and then step through the records to identify the blank records and delete them.

This will give a clean data set each time the link is renewed. You could check out the Access Help File topic OpenRecordset Method for more info on Recordsets.

HTH

Lightning

 

[Francescina]

This code snippet checks whether a field contains Null or an empty string:

if len(recordsetName(&quot;Fieldname&quot & &quot;&quot <> 0 then
' your code here

 

[JimHorton]

Andie,
If you can define what constitues a 'blank' record, then a simple query should do it. Since you're basing reports off of this link, then there must be some key field or fields that, if blank, they render the entire record invalid. Just run sql over the linked table (assuming you have read/write access over the dbf file) and delete where this field/fields is null or &quot;&quot; (empt string).
--Jim