Solved

Count instances in a record on a report

Posted on 2014-01-11
7
340 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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…
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

726 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