STATE EMPLOYEE REQUESTS QUERY HELP!

[ST8 EMPLOYEE]

I need a query that will give me a value of 1 for each record that is not
null in any one of twenty fields (text datatype).

In other words, I need to search twenty specific fields and if any of them
has anything in it then I need to count that record in my report.

PLEASE TELL ME HOW TO DO THIS!!!

 

[ashg657]

In query design view add a new field with a control of something like:

COUNTME:COUNT(IIF(

![FIELD1] IS NULL OR

![FIELD2] IS NULL OR

![FIELD3] IS NULL,1,0))

hope that helps m8.

 

[ST8 EMPLOYEE]

Thank you! I knew it could be done, and that it wouldn't take much... I just
couldn't get it right. I adapted the string you sent me and it works
perfectly:

COUNTME: IIf([hitting res 1] Is Not Null Or [hitting res 2] Is Not Null Or
[biting res 1] Is Not Null Or [biting res 2] Is Not Null Or [scratching res
1] Is Not Null Or [scratching res 2] Is Not Null Or [head butting res 1] Is
Not Null Or [head butting res 2] Is Not Null Or [slapping res 1] Is Not Null
Or [slapping res 2] Is Not Null Or [push/pull res 1] Is Not Null Or
[push/pull res 2] Is Not Null Or [hair pulling res 1] Is Not Null Or [hair
pulling res 2] Is Not Null Or [kicking res 1] Is Not Null Or [kicking res 2]
Is Not Null Or [pinching res 1] Is Not Null Or [pinching res 2] Is Not Null
Or [choking res 1] Is Not Null Or [choking res 2] Is Not Null,1,0)

In query design view add a new field with a control of something like:

COUNTME:COUNT(IIF(

![FIELD1] IS NULL OR

![FIELD2] IS NULL OR

![FIELD3] IS NULL,1,0))

hope that helps m8.

I need a query that will give me a value of 1 for each record that is not
null in any one of twenty fields (text datatype).

In other words, I need to search twenty specific fields and if any of them
has anything in it then I need to count that record in my report.

PLEASE TELL ME HOW TO DO THIS!!!

 

[John Spencer]

You could also just concatenate the fields, if any one of them has a value,
then you will end up with something other than null for the result.

IIF( ( [Field1] & [Field2] & [Field3] & ... & [Field20] ) is Not null, 1,
Null)

Checking for null on each field may be more efficient if there indexes on
all the fields involved.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Thank you! I knew it could be done, and that it wouldn't take much... I
just
couldn't get it right. I adapted the string you sent me and it works
perfectly:

COUNTME: IIf([hitting res 1] Is Not Null Or [hitting res 2] Is Not Null Or
[biting res 1] Is Not Null Or [biting res 2] Is Not Null Or [scratching
res
1] Is Not Null Or [scratching res 2] Is Not Null Or [head butting res 1]
Is
Not Null Or [head butting res 2] Is Not Null Or [slapping res 1] Is Not
Null
Or [slapping res 2] Is Not Null Or [push/pull res 1] Is Not Null Or
[push/pull res 2] Is Not Null Or [hair pulling res 1] Is Not Null Or [hair
pulling res 2] Is Not Null Or [kicking res 1] Is Not Null Or [kicking res
2]
Is Not Null Or [pinching res 1] Is Not Null Or [pinching res 2] Is Not
Null
Or [choking res 1] Is Not Null Or [choking res 2] Is Not Null,1,0)

In query design view add a new field with a control of something like:

COUNTME:COUNT(IIF(

![FIELD1] IS NULL OR

![FIELD2] IS NULL OR

![FIELD3] IS NULL,1,0))

hope that helps m8.

I need a query that will give me a value of 1 for each record that is
not
null in any one of twenty fields (text datatype).

In other words, I need to search twenty specific fields and if any of
them
has anything in it then I need to count that record in my report.

PLEASE TELL ME HOW TO DO THIS!!!