asked on Count instances in a record on a report
On a report I have three fields. Named Field1, Field2, and Field3. Each of them can have a combination of TWO letters. For example, AB, CD, EF, DU, GH and so forth. (Note, There may be only one entry or two or three)
Also on the report I have a 4th field named txtCount. This field has to count the number of the previous fields that have combinations of letters NOT INCLUDING "AE" or "DC".
In other words, let's use two record examples:
Record 1: Field1 displays EF, Field2 displays GH, Field 3 displays CD. So for that record txtCount would be 3.
Record s: Field1 displays DU, Field2 displays AE, Field 3 displays DC. So for that record txtCount would be 1. (Because fields 2 & 3 displayed "AE" or "DC".
What is the code or control source for txtCount?
--Steve
Also on the report I have a 4th field named txtCount. This field has to count the number of the previous fields that have combinations of letters NOT INCLUDING "AE" or "DC".
In other words, let's use two record examples:
Record 1: Field1 displays EF, Field2 displays GH, Field 3 displays CD. So for that record txtCount would be 3.
Record s: Field1 displays DU, Field2 displays AE, Field 3 displays DC. So for that record txtCount would be 1. (Because fields 2 & 3 displayed "AE" or "DC".
What is the code or control source for txtCount?
--Steve
Microsoft Access
Last Comment
mbizup
If the fields are only two letters, you can also simplify it like this:
Abs((Field1 & "" <> "AE" AND Field1 & "" <> "DC") + (Field2 & "" <> "AE" AND Field2 & "" <> "DC") + (Field3 & "" <> "AE" AND Field4 & "" <> "DC"))
ASKER
It works if there are 3 entries. But if there are only 1 or 2 the result is "3".
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Incredible! That did it. Now in a footer section of the report I'm trying to sum these values. I can't figure that out either.
Here's what I've tried:
=Sum(Abs(([Field1] & ""<>"AE" And [Field1] & ""<>"DC" And [Field1] & ""<>"")+([Field2] & ""<>"AE" And [Field2] & ""<>"DC" And [Field2] & ""<>"")+([Field3] & ""<>"AE" And [Field3] & ""<>"DC" And [Field3] & ""<>"")))
But it doesn't work. Do I need to close this topic and start a new one?
Here's what I've tried:
=Sum(Abs(([Field1] & ""<>"AE" And [Field1] & ""<>"DC" And [Field1] & ""<>"")+([Field2] & ""<>"AE" And [Field2] & ""<>"DC" And [Field2] & ""<>"")+([Field3] & ""<>"AE" And [Field3] & ""<>"DC" And [Field3] & ""<>"")))
But it doesn't work. Do I need to close this topic and start a new one?
ASKER
I got it with run sum fields. Thanks.
Glad to help - and nice job working out the footer sums :)
Open in new window
The & "_" in each part of the expression handles nulls if any are present in your data.