Conditional Formatting

[Shazz]

Hi,

Can anyone help me, I want to use Conditional Formatting to flash up the Words "Payment Overdue" or "Paid" depending on what the Balance Outstanding field reads, ie £00.00 or £1.50 or more.

Basically I have a Database that after 7 days a Payment become due and I would like to be able to use Conditional Formatting in a field that will tell me if a payment is overdue or Paid.

Below is a screen shot of the Form.

Can anyone help at all.

Shazz
x

 

[Kryst51]

Where do you want this to "flash", it can probably be done, but you'll have to use vba, and an If/Then kind of deal. depending on exactly what you want and when it will determine on which event you put your code.

 

[vbaInet]

The title is misleading, it's not conditional formatting you're after. So as Kryst mentioned you can use the IIF() function in the control source of a textbox.

=IIF(IsNull([FieldName]), Null, IIF([FieldName] = 0, "Payment Overdue", "Paid")))

You may need to deepen the IIF() but that gives you an idea.

 

[Shazz]

Hiya, anywhere on the Form really, maybe under the Needs Urgent Attention field??

Shazz

 

[Kryst51]

As vbaInet said, you can put an unbound text box on your form, format it how you want, and include the If/Then statement as the control source. I was suggesting doing it in code so you could make the box visible or not depending. If it's not paid and not past due then you wouldn't need it to be visible. Or (as part of vbaInet's suggestion) you could make the border transparent, and put an empty string in the if/then for cases where it doesn't meet either condition (Unpaid, but not past due).

If my way then this might work.

So, maybe in the on current event (I don't remember how your form is set up, so this is only an example, but it should give you the idea):

If Now() - Me.datefield > 7  AND Me.PaidField = 0 Then
Me.UnboundTextBoxName = "OVER DUE"
Me.UnboundTextBoxName.Visible = True
 
ElseIF Now() - Me.datefield > 7 ABD Me.PaidField = -1 Then
Me.UnboundTextBoxName = "PAID"
Me.UnboundTextBoxName.Visible = False
 
ElseIf Now() - Me.datefield <= 7 AND Me.PaidField = 0 Then
Me.UnboundTextBoxName.Visible = False
 
ElseIf Now() - Me.datefield <= 7 AND Me.PaidField = -1 Then
Me.UnboundTextBoxName = "PAID"
Me.UnboundTextBoxName.Visible = True
 
End If

You might be able to use a select case, but as I don't know your set up exactly I am not sure. Again, this is air code, I am not even certain that the if portions work exactly like I have it.

 

[vbaInet]

I'm thinking the OP is approaching this the wrong way. He/she should just change the forecolor property of the Balance Outstanding field to red if the amount is > 0.

Or make the forecolor white and the back color red to make it stand out. That's where the thread's title becomes relevant to the thread.

 

[Kryst51]

I'm thinking the OP is approaching this the wrong way. He/she should just change the forecolor property of the Balance Outstanding field to red if the amount is > 0.

Or make the forecolor white and the back color red to make it stand out. That's where the thread's title becomes relevant to the thread.

I didn't think about that, though, in dealing with people in my own office, I have discovered that they need things spelled out for them sometimes, more clearly then what you suggest.

 

[vbaInet]

I think with something that simple a red backcolor would draw their attention to that control. Maybe a control tip text explaining what red stands for or Keys at the footer of the form.

 

[Shazz]

Ok, How do I do that Then??

Shazz

 

[vbaInet]

Use conditional formatting for that control. There's a button in one of your menus that reads Conditional Formatting.

Field Value Is Greater Than 0 - Set the backcolor to red and forecolor to white or black.

Remember to set the default.

 

[Shazz]

Thanks for that, but this shows all money outstanding, I do just want anything over 7 days to be highlighted??

Sorry to be a pain.

Shazz

 

[Shazz]

Does anyone have any ideas how to do this please??

Shazz

 

[Kryst51]

I don't see any way in conditional formatting to aply multiple conditions in an "AND" like fashion, I think you might have to use vba.

 

[Dairy Farmer]

You can use AND in conditional formating

Expression Is
[Date123] < Date()-7 AND [AmountDue] > 0

Then make an unbound text box next to Amount Due and use the code from Kryst51 to display the message.