Formatting dependent on days elapsed

[Alan R]

Hi all,

i am fairly new to the use of VB so (1) I am not sure if this is possible and (2) i wouldn't know where to start really so any help is greatly appreciated. I guess that in order to achieve whet i eventually want to (assuming it is possible that is) will be based on using some element of DateDiff. I have searched the forum and whilst i now have a basic understanding of the DateDiff function, i could find nothing that would help solve my problem.

On my form - which is a continuous Form displaying a number of the same type of records - i have a number of fields including InvoiceDate and TaxInvoiceDate. What i have been trying to achieve is the following:

1. Should the number of days since the InvoiceDate and the current date exceeds a certain i would like to format the InvoiceDate field, for example, if more than 30 days but less then 45 days have elapsed then the field could be this and if greater than 45 days then this. This indicates overdue accounts, with me so far? This i have managed to implement this using Conditional Formatting.

2. However, if a date is included in the Tax Invoice Date (meaning that the account has been settled / paid) i want to return / leave the text normal colour / font. This second point i guess will not be achievable when using conditional Formatting?

So, i believe that in order to achieve both points i need to use VB? If so, can anybody help me by suggesting suitable coding and where on the form / fields this code should go.

(Grovel Time) I should add that it only recently that i started using Access and developing my own databases but i have learn't so much from this forum - who knows sometime in the future i may also be able to partake by answering queries myself

Regards

Alan

 

[KeithG]

I believe you could do this with conditional formating. Instead of basing your condition of a field base if on an expression.

 

[pbaldy]

You should still be able to use conditional formatting. You can have a compound condition, combining your DateDiff with a test of the other field:

ThisTest AND ThatTest

 

[Alan R]

Keith,

Thanks for the swift reply, so are you saying create an expression within the Conditional Formatting 'function'?

Will give it a go but any further pointers welcome.

Alan

 

[CEH]

If I am understanding you post..... heres a little code.... you'll need to add your second color change

Private Sub txtInvoiceDate_AfterUpdate()
If Not IsNull(txtTaxInvoiceDate) Then
If Me.txtInvoiceDate > (Date) + 30 And _
Me.txtInvoiceDate < (Date) + 45 Then
Me.txtInvoiceDate.ForeColor = vbRed
Else
Me.txtInvoiceDate.ForeColor = vbBlack
End If
End If

End Sub

 

[pbaldy]

Curtis, I think you'll find that method will fail in this situation (continuous form). Conditional Formatting is the way to go.

 

[CEH]

My error. ..Didn't consider continous forms.....

 

[Alan R]

Guys,

Thanks for all the replies - much appreciated. Got it working using Conditional Formatting

Regards

Alan