Solved

Count instances in a record on a report

Posted on 2014-01-11
7
335 Views
Last Modified: 2014-01-11
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
0
Comment
Question by:SteveL13
  • 4
  • 3
7 Comments
 
LVL 61

Expert Comment

by:mbizup
ID: 39773371
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))

Open in new window



The & "_" in each part of the expression handles nulls if any are present in your data.
0
 
LVL 61

Expert Comment

by:mbizup
ID: 39773375
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"))

Open in new window

0
 

Author Comment

by:SteveL13
ID: 39773376
It works if there are 3 entries.  But if there are only 1 or 2 the result is "3".
0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 61

Accepted Solution

by:
mbizup earned 500 total points
ID: 39773382
Try extending the condition to exclude blanks:

Abs((Field1 & ""  <>  "AE" AND Field1 & "" <> "DC" AND Field1 & "" <> "")   + (Field2 & ""  <>  "AE" AND Field2 & "" <> "DC" AND Field2 & "" <> "")  +  (Field3 & ""  <>  "AE" AND Field3 & "" <> "DC" AND Field3 & "" <> "") )

Open in new window

etc...


Add the FieldN & "" <> "" to each part of the expression
0
 

Author Comment

by:SteveL13
ID: 39773402
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?
0
 

Author Comment

by:SteveL13
ID: 39773407
I got it with run sum fields.  Thanks.
0
 
LVL 61

Expert Comment

by:mbizup
ID: 39773413
Glad to help - and nice job working out the footer sums :)
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now