Counting non-blank fields in a record

[Rog]

(Using a Report) I have 6 fields in a 10 field record that may or may not
have data in them. I need to count the non-blank fields in that restricted
set of 6 fields and put that number in a separate field for that record.
DCOUNT and COUNT don't work for this and there is no COUNTIF in Access. Is
there a way to do this? As always, the help is much appreciated!

 

[KARL DEWEY]

Try this using your field names --
Field_Count: IIf([Lname] Is Not Null,1,0)+IIf([fname] Is Not
Null,1,0)+IIf([SECT] Is Not Null,1,0)+IIf([Position] Is Not Null,1,0)

 

[Jeff Boyce]

It sounds like you are describing a spreadsheet, not a relational database
table.

If you'll provide a more specific description of what might be in those
fields, folks here may be ablet to offer more specific suggestions.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Rog]

Thanks for the help. Here's what I found worked. For some reason "ISNULL"
works but IS NOT NULL does not.

PM PTS CNT: IIf([MASTER DWG LIST]!PM="Y",(IsNull([DWG REF])+1)+(IsNull([DWG
REF1])+1)+(IsNull([DWG REF2])+1)+(IsNull([DWG REF3])+1)+(IsNull([DWG
REF4])+1)+(IsNull([DWG REF5])+1),0)

I agree that this is more for a database in Excel, but sometimes the data in
a relational database needs to manipulated before the relationships can be
established. I find oftentimes the need to combine them.

Thanks very much for the feedback and thanks so very much for even being out
there! Those of us who do not use ACCESS programming everyday would be lost
without you! RA

Try this using your field names --
Field_Count: IIf([Lname] Is Not Null,1,0)+IIf([fname] Is Not
Null,1,0)+IIf([SECT] Is Not Null,1,0)+IIf([Position] Is Not Null,1,0)
--
KARL DEWEY
Build a little - Test a little

(Using a Report) I have 6 fields in a 10 field record that may or may not
have data in them. I need to count the non-blank fields in that restricted
set of 6 fields and put that number in a separate field for that record.
DCOUNT and COUNT don't work for this and there is no COUNTIF in Access. Is
there a way to do this? As always, the help is much appreciated!