Solved

Count instances in a record on a report

Posted on 2014-01-11
7
337 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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
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

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.

Question has a verified solution.

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

Suggested Solutions

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

810 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