Count Blank Fields then output result to Form (1 Viewer)

[chris01252]

Hi Have scoured the forum and have found people with a similar problem, but I just can't seem to get my head round this.

Basically I have a table with various fields, one of these fields is a sign off field where the user enters their name once the record has been reviewed.

I want to count all the records that do not have a name entered in this particular field thus are null/blank. I then want this figure to appear on a form thus representing the total number of records still to be reviewed.

I have tried doing this with various methods with no joy.

Any help would be much appreciated.

 

[Dwight]

In a query enter an expression like this: IIf(IsNull([YourField]),1,0). All Nulls will have a value of 1. You can then sum the total.

 

[chris01252]

ok, I have manage to create a query that tells me how many records there are and how many have been signed off so the blank ones would be the difference between the two.

The part I am struggling with is how do I get this info into a form without using a sub form. I just want to use a txt box or label.

 

[Dwight]

In the unbound text box you can use a domain aggregate function such as Dsum. I guess you will need two Dsums. One for each column and then subtract them to get the difference.

My original proposed solution would only require one. But just as long as you get it working.

Dwight