Conditional Formatting Based on Number of Times Name Appears in Report

[MasterBIB]

Hi Everyone,

Second post in this forum ever - hoping to get anyone's expertise.

I have a report setup based on a query, one of the fields contains people's names, for example "John". This query can return multiple instances of the name "John", for example:

Contract AMT | Name
$100 | John
$300 | Toby
$150 | John
$400 | John
$500 | Sarah

Basically, I would like to add conditional formatting to any name that appears more than 2 times, for example.

I need help writing the expression because the normal formatting tools access has all assume the info I am working from is numbers, and that is clearly not the case in this example

Any help would be much appreciated!.

 

[GinaWhipp]

Hmmm, putting on my thinking cap...

- Create a query that counts the instances of a name.
- Add that query to the record source of your report linking on name? OR linking on something???
- Add the field that holds that number to your report.
- Apply conditional Formatting to the name field based on the number field that says if my number field greater then 1 (or whatever number you want) then color my name field Red (or whatever color you want)

...give that a try, sounds like it should work.

 

[MasterBIB]

I will give that a shot, thanks!

 

[GinaWhipp]

I'll be here waiting to hear the results! You're welcome!

 

[Pis7ftw]

I kind of ran into this issue before. What I have is a yes no field and I wanted to count how many times yes appeared in the query and how many times no appeared in the query under the same field. I used the expression:

=Sum(Abs([FieldName]="Yes"))

And I put that in a text box on my report.

Not sure if that's what your looking for though!

 

[vbaInet]

I kind of ran into this issue before. What I have is a yes no field and I wanted to count how many times yes appeared in the query and how many times no appeared in the query under the same field. I used the expression:

=Sum(Abs([FieldName]="Yes"))

And I put that in a text box on my report.

Not sure if that's what your looking for though!

Your question has been answered in your thread:

http://www.access-programmers.co.uk/forums/showthread.php?t=232608

And Abs() won't work in this case because your field is a Text field.

 

[Pis7ftw]

Your question has been answered in your thread:

http://www.access-programmers.co.uk/forums/showthread.php?t=232608

And Abs() won't work in this case because your field is a Text field.

Weird. It seems to work just fine that way. If I don't have the Abs() it returns a negative number, which I don't want. And thanks for your reply on the other thread. I'll push the button.