Colour dependent on criteria

[skaurd]

Hi

I would like to create a query which changed the colour of a field (name)to red if another field is null (sales) and if the sales field is not null or "0" then the name field comes out green.

Does anyone know how to do this?

thanks

S

 

[LittleSmudge]

There is no way to change the colour of a FIELD and if there was, there is no way you could tell anyway.

I assume you mean you want to change the colour of a CONTROL on a Form or Report that is bound to a FIELD in a table.

New question is WHEN do you want to change it ?

When the record becomes current - or when the value in the control is changed - or both

BOTH seems logical and if that's wrong you can delete some of what follows:-


Create a Sub as follows:

Private Sub SetColour()
If IsNull(NameControlName) Then
SalesControlName.BackColor = 255 ' Red
ElseIf NameControlName = 0 Then
SalesControlName.BackColor = 255 ' Red
Else
SalesControlName.BackColor = 65280 ' Green
End If
End Sub

In the Form_Current event procedure AND in the
NameControlName_AfterUpdate event procedure
call the sub like:-

Call SetColour



Job Done.

G LS

 

[nabiS2K]

You cannot do what you are asking in a query. However, if in a form you create two text boxes bound to the same number field on top of each other, you can accomplish this.

Set one of the boxes to a green font color with this control source:
=IIF(Val(nz([MyField],0))=0,"",[MyField])

Set the other of the boxes to a red font color with this control source:
=IIF(Val(nz([MyField],0))=0,[MyField],"&quot

Essentially, the green text box shows a value if the item is not null or zero. The red text box shows a value if the item is null or zero. When the two are placed on top of each other, the illusion is created that you have one box that changes colors by record. God Bless,
Mike

Yes, I believe in Jesus. ;-)
"Neither is there salvation in any other: for there is none other name under heaven given among men, whereby we must be saved." (Acts 4:12, KJV)

 

[Quehay]

Don't overlook the Conditional Formatting available from the menu. It allows for several levels of testing.

 

[AlexMidd]

Whossat? Conditional Formatting? I thought that was in Excel. Can you do it in Access? If so, is it only in later versions? I use Access 97 and have never come across that. Have fun! )

Alex Middleton

 

[Quehay]

I don't have 97 handy so I can't check. In 2K its in the "Format" menu in design view. I always use this to set the backcolor of controls to a different color when control has focus.

 

[LittleSmudge]

Conditional formatting is really powerful but ( as far as I've seen ) only works where the control that holds the condition is the SAME as the control you want to change the format of.

As skaurd wants one control to affect the control of another I think the code option still rests.


G LS

 

[Quehay]

Actually I've had it look for other control values and format accordingly--I had a control that I wanted to have turn yellow only if another control had a value and the control in question remained null. For example:
[tt]
[fraGender]=2 And IsNull([currentcontol])[/tt]

I've also used the forms!frmName!ctrlName(Value) syntax as well.

 

[AlexMidd]

Take a look at thread 705-305981, which may be of help. Have fun! )

Alex Middleton

 

[skaurd]

Hi

I tried both the first and the second idea and couldn't get them to work as I don't really understand.

Little Smudge - where do I put the code;

Private Sub SetColour()
If IsNull(NameControlName) Then
SalesControlName.BackColor = 255 ' Red
ElseIf NameControlName = 0 Then
SalesControlName.BackColor = 255 ' Red
Else
SalesControlName.BackColor = 65280 ' Green
End If
End Sub

???

And Mike I couldn't get yours to work either. I replace MyField with the name of the field ([name])

many thanks and sincerely yours

S

 

[AlexMidd]

Make the field you want to highlight transparent then add a Boolean field behind it. Set the font to something that will display solid squares if the Boolean value is true (e.g. where the sex is 'Male' in your example), I use Terminal font at point size 14 - you may have to fiddle with this to get the display looking right.

Set the format box to ;"ÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛ" (this is the character in Terminal font that displays solid squares) - you will need more characters the larger the box is. Then set the colour to the colour you want to use as a highlight background and set the ControlSource to a Boolean field or a Boolean expression, e.g. IIf([Sales]=Null,True,False). As you want it to be red or green you will need to set the BackColor to green, then the font to red.

This will (should as I have not tested this example) show through the backcolor (green) when the Sales field is not null and red (the font colour) when the Sales field is null, ie the squares (all merging together to form a virtual background to the text box) show up when the expression is true. Hope it makes sense. Have fun! )

Alex Middleton

 

[nabiS2K]

skaurd,

Make sure the textbox does not have the same name as a field. If your text box is named MyField and the field it references previously was MyField, when you use the IIF statement that I created, Access will think you are creating a circular reference. The textbox will display #Error or #Name? because textbox is trying to reference itself, instead of the field in the underlying table or query.

This might be why it didn't work... especially if you used an AutoForm to initially create the form.

I am 100% sure this works to color the fields. God Bless,
Mike

Yes, I believe in Jesus. ;-)
"Neither is there salvation in any other: for there is none other name under heaven given among men, whereby we must be saved." (Acts 4:12, KJV)

 

[Quehay]

This is easy. Do check the control name vis a vis the field name (you program for the control here).

Use the Conditional Formatting.

Test Condition 1: Is Null([ctrlName]) and set backcolor for TRUE

Test Condition 2: Len([ctrlName]) and set backcolor

 

[LittleSmudge]

Create a Sub as follows:

Private Sub SetColour()
If IsNull(NameControlName) Then
SalesControlName.BackColor = 255 ' Red
ElseIf NameControlName = 0 Then
SalesControlName.BackColor = 255 ' Red
Else
SalesControlName.BackColor = 65280 ' Green
End If
End Sub

In the Form_Current event procedure AND in the
NameControlName_AfterUpdate event procedure
call the sub like:-

Call SetColour




You put the subroutine Private Sub SetColour() in the form's own code module then you CALL it in the Form_Current event procedure and the AfterUpdate event procedure of the control that is causing the effect.
eg
Private Sub Form_Current()
Call SetColour
End Sub



If any of this still isn't clear - try to be specific about which bit. - Where are you stuck.

G LS

 

[skaurd]

Hi All

I understand why I am getting confused. I am trying this in reports and not forms.

Is it the same proceudre or different?

many thanks again

S

 

[LittleSmudge]

Slightly different.

Put the code in the Detail_Format event

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If IsNull(NameControlName) Then
SalesControlName.BackColor = 255 ' Red
ElseIf NameControlName = 0 Then
SalesControlName.BackColor = 255 ' Red
Else
SalesControlName.BackColor = 65280 ' Green
End If
End Sub



As you won't be changing the field contents, then the AfterUpdate option is not necessary.

( This, once again, points out the need to be clear in the terms you use when asking the question in the first place. )

'ope-that-'elps.

G LS

 

[skaurd]

Hi Little Smudge

Apologies for my lack of clarification.

I have put your code like so;

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If IsNull(Sales1) Then
name1.BackColor = 255 ' Red
ElseIf Sales1 = 0 Then
name1.BackColor = 255 ' Red
Else
name1.BackColor = 65280 ' Green
End If
End Sub
End Sub

And the sales1 and name1 are the names of my text fields. Is this correct or should be using the name of the actual fields in the table?

ANyway, I am getting a compile error. And the error hightlights the first line;

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

in yellow

many thanks for your patience

S

 

[nabiS2K]

You may not have the IsNull function referenced in a library. Try Val(nz(MyControl,0)=0) like this instead:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If Val(nz(sale1.Value),0) = 0 Then
    name1.BackColor = 255 ' Red
Else
    name1.BackColor = 65280 ' Green
End If
End Sub

Hope this helps. God Bless,
Mike

Yes, I believe in Jesus. ;-)
"Neither is there salvation in any other: for there is none other name under heaven given among men, whereby we must be saved." (Acts 4:12, KJV)

 

[skaurd]

Hi Mike

I am getting a compile error and the "Val"on the first line is highlighted.

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If Val(Nz(Sales1.Value), 0) = 0 Then
name1.BackColor = 255 ' Red
Else
name1.BackColor = 65280 ' Green
End If
End Sub

thanks

S

 

[nabiS2K]

Well... maybe that's because I typed it wrong... LOL

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If Val(Nz(Sales1.Value, 0)) = 0 Then
    name1.BackColor = 255 ' Red
Else
    name1.BackColor = 65280 ' Green
End If
End Sub

God Bless,
Mike

Yes, I believe in Jesus. ;-)
"Neither is there salvation in any other: for there is none other name under heaven given among men, whereby we must be saved." (Acts 4:12, KJV)