Color Coding Report Detal (1 Viewer)

[RHubbard]

I have a simple database but with roughly 30,000 name records in it. Each name record consists of three elements Name1; Name2; Name3 (similar to first, middle and last).

Not all three elements are present in every record. The possible combinations for any given record are as follows:

[Hussein] [al] [Tikrit]
[Hussein] [al] [____]
[Hussein] [_] [Tikrit]
[Hussein] [_] [____]
[______] [al] [____]
[______] [al] [Tikrit]
[______] [_] [Tikrit]

What I want to do is to provide color coding in a report so that the color of the control(s) is determined by whether or not a data element is present. For example:

[Hussein] [al] [Tikrit] would be:
[Blue] [Blue] [Blue]

[Hussein] [al] [____]would be:
[Green] [Green] [Blank]

[Hussein] [_] [Tikrit] would be:
[Yellow] [blank] [Yellow]

Etc.

I suspect this can be achieved via some sort of Select Case inserted in the On Format of the report detail section, but for the life of me, I can’t figure out how to do it.

Any ideas/suggestions would be gratefully accepted.

 

[pbaldy]

For anyone to help, you'd need to specify the conditions under which you want a given color displayed for each control. It does not appear to be as simple as one color for empty and another for filled.

 

[RHubbard]

For anyone to help, you'd need to specify the conditions under which you want a given color displayed for each control. It does not appear to be as simple as one color for empty and another for filled.

Ah yes, good point!

The conditionas are straightforward.

When a field field is populated with text, it needs to be colored. That is simple enough, however the colors need to vary. If, for example, fields for Name1, Name2 and Name3 are all populated, the color of all three shouldbe the same color (blue, for instance). If Name1 and Name3 are populated, but Name 2 is not, then Name1 and Name3 would be a specified color (green, maybe) while the blank field should remain uncolored. This basic scheme needs to be followed through the range of possible combinations.

Perhaps this explanation will help.

Thanks,

Rick

 

[pbaldy]

That's still generic, but based on that (this is the brute force method, since I see no pattern):

If Len(Me.Name1 & vbNullString) > 0 AND Len(Me.Name2 & vbNullString) > 0 AND Len(Me.Name3 & vbNullString) > 0 Then 'they all have values
  Me.Name1.Backcolor = 16711680
  Me.Name2.Backcolor = 16711680
  Me.Name3.Backcolor = 16711680
ElseIf Len(Me.Name1 & vbNullString) > 0 AND Len(Me.Name2 & vbNullString) = 0 AND Len(Me.Name3 & vbNullString) > 0 Then  '1 & 3 have values
  Me.Name1.Backcolor = 65280
  Me.Name2.Backcolor = 16777215
  Me.Name3.Backcolor = 65280
ElseIf...'you get the idea
End If

 

[RHubbard]

Thanks for the help Paul. Sorry I coudn't explain what I was trying to achieve a little more clear, but nevertheless, you NAILED the solution for me.

Rick

 

[pbaldy]

Happy to help Rick. It felt like one of those times when the code would almost write itself once the parameters were defined.