Solved

Count instances in a record on a report

Posted on 2014-01-11
7
339 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Balance After Payment 12 61
Out in left field or not to far off base 8 68
Looking for advice on how to develop a project database 2 47
Handle Apostrophes in SQL Parameter 16 67
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

732 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