Using loops to run a report on missing values

[jo_notintheknow]

Hi,

I'm having difficulties deciding the best way of using VBA for the following problem;

I'm looking to use VBA to loop through a table to select any fields that contain the value -1 (which stands for a missing value in this table). I need to be able to run the code so that it runs through about 20 fields that could possibly contain this value and then report back on which records have -1 and in which particular fields!

I've been trying little snippets of code but I can only seem to get it working one field at a time. The added complication is that I don't want this code attached to a form as there's already quite a few records currently in the database, I was thinking along the lines of a simple printed report instead.

Any ideas or should I stop trying to do it using loops? I have checked other fields using If...else statements to produce different messages depending on whether there is data in the field and then run this through a query which I can then use as a report.

Sorry if this seems a really daft problem!
Thanks!

 

[dcx693]

You don't need to run a loop or use VBA code for this, though you can if you want to. You can use a query to search for -1 in your fields, but you need to be careful if you have any boolean true/false fields in your database because Access considers a -1 in those fields to be equal to True. Use the query as the recordsource for your report.

 

[jo_notintheknow]

Hi,

Thanks for the tip but I really don't want to use a standard query for this problem as there would be too many fields that the criteria would apply to.

I have set up functions to test if certain fields contain the value -1 and referred to the function in a query so that the code runs and enters a relevant message in an expression but this gets a bit messy when you have to check 10+ fields. E.g if a weight field is missing the message "Weight missing" appears in the query in a comments column.

The fields that would have the -1 as a missing value are numerical fields where -1 would not normally be relevant to these particular fields e.g number of weeks, age, height etc.

 

[AncientOne]

Use the GetRows method to create an array,then step through the rows using two variables for Row and Field count. You can send the returned values to the debug window, or to a table for a hard-copy printout