Solved

Count instances in a record on a report

Posted on 2014-01-11
7
336 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Suggested Solutions

It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
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…

863 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