What's the best method to calculate how many Fields have non-null values for a record (1 Viewer)

I have a Table called TblReliabilityProgramme which has 6 Fields that I am really interested in (I know the entire Table has 9 Fields/Columns, but these 6 are the main ones I am interested in):

• Title
• Intent
• Cost
• Reference
• Frequency
• ConceptionDate

For a little feature I am trying to create on a Form, I need a way of identifying or triggering on the Form, how many of these Fields have been filled in. For example there are 6 Fields in total, if 4 of these have been filled in, I will want to identify 4/6 have been filled in on the Form. That way I can trigger my FormStatusBar to show a bar that is 4/6 of it's length coloured.

I can currently make this work by creating a Calculated field, but I have concerns that this will massively bloat the Database as Access will be storing this data. I do not plan to use this data for anything else other than showing the completion on the FormStatusBar.

The calculated field does an IsNull and an IIf on each record to give a number for how many have been filled in per record. The attached pic shows the important fields.

Any suggestions on better ways to do/implement this?

If the calculated field works, there is nothing wrong with it for this purpose.

Hi. There three ways you could do the calculation:

1. Calculated field in a table
2. Calculated column in a query
3. VBA code on a form

Any of which will add to the file size. Access only stores the calculation expression in the table, not the result (as far as I know, but maybe I'm wrong). If you distribute your app as ACCDE, then the best place to reduce bloat is probably through the VBA option because it gets compiled to a smaller size.


Cheers!

For option #1, Access stores the actual result. The benefit of this is that you can add an index on a calculated field. But calculated fields are very limited because they don't want to cause data anomalies so very few functions are supported and the data fields are limited to those in the same record.

Pat Hartman said:

For option #1, Access stores the actual result. The benefit of this is that you can add an index on a calculated field. But calculated fields are very limited because they don't want to cause data anomalies so very few functions are supported and the data fields are limited to those in the same record.

Click to expand...

Hi Pat. Thanks for the clarification/correction. Cheers!

Yeah in my mind I am wanting to explore the VBA option as in my mind I envisage it only calculates it for the individual form that is opened at that moment in time, rather than the Calculated Field method which calculates it all at once and then never calculates again until refreshed.

To calculate on the fly, put the calculation in a query if possible. That gives you the most flexibility if you, like I, rely mostly on saved querydefs.

Are you looking for snapshot results or do you want the status bar to change as the number of null fields decreases/increases? I don't see where that's been defined. If dynamic, the percentage of filled in fields should probably be calculated on the form.

Micron said:

Are you looking for snapshot results or do you want the status bar to change as the number of null fields decreases/increases? I don't see where that's been defined. If dynamic, the percentage of filled in fields should probably be calculated on the form.

Click to expand...

Yes it would be dynamically. The mechanic I want it to show is where I fill in a box and then the status bar immediately updates to indicate that something has been put in the box.

So where are you at? I see a few suggestions and 6 downloads of your db (I'm not one of them). Did you implement any of the suggestions and all is well now?