If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Counting non-blank fields in a record
(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! |
#2
|
|||
|
|||
Counting non-blank fields in a record
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 "Rog" wrote: (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! |
#3
|
|||
|
|||
Counting non-blank fields in a record
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" wrote in message ... (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! |
#4
|
|||
|
|||
Counting non-blank fields in a record
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 "KARL DEWEY" wrote: 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 "Rog" wrote: (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! |
Thread Tools | |
Display Modes | |
|
|