# 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
Microsoft Access

Last Comment
mbizup

8/22/2022 - Mon
mbizup

Try this for your expression - either as a field in your query or as a textbox control source:

``````Abs((instr(1,Field1 & "_" ,"AE") = 0 AND instr(1,Field1 & "_" ,"DC") = 0) + (instr(1,Field2 & "_" ,"AE") = 0 AND instr(1,Field2 & "_" ,"DC") = 0) + (instr(1,Field3 & "_" ,"AE") = 0 AND instr(1,Field3 & "_" ,"DC") = 0))
``````

The & "_" in each part of the expression handles nulls if any are present in your data.
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"))
``````
SteveL13

It works if there are 3 entries.  But if there are only 1 or 2 the result is "3".
mbizup

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.
SteveL13

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?
SteveL13