Arithmatic on Dates

[rich]

Hello,

I am developing an application where I need to do some addition on dates.
I have an expiration date.

Case 1:
If the expiration date (a future date) - today's date is less than 30
days, display the field in red.

Case 2:
If the expiration date (a future date) - today's date is 31-45 days,
display the field in Yellow.

Case 3:
If the expiration date (a future date) - today's date is greater than
45 days, display the field in Green.

This code should execute anytime the form loads, or when moving to a
different record.

Can anyone help me with the code?

TIA,
Rich

 

[Dirk Goldgar]

Hello,

I am developing an application where I need to do some addition on dates.
I have an expiration date.

Case 1:
If the expiration date (a future date) - today's date is less than 30
days, display the field in red.

Case 2:
If the expiration date (a future date) - today's date is 31-45 days,
display the field in Yellow.

Case 3:
If the expiration date (a future date) - today's date is greater than
45 days, display the field in Green.

This code should execute anytime the form loads, or when moving to a
different record.

Can anyone help me with the code?

Something like this in the form's Current event:

'----- start of untested "air code" -----
Private Sub Form_Current()

Dim lngDaysToExpiration As Long

With Me!ExpirationDate

If IsNull(.Value) Then
.BackColor = vbWhite
Else
lngDaysToExpiration = DateDiff("d", Date(), .Value)

Select Case lngDaysToExpiration
Case Is <= 30
.BackColor = vbRed
Case Is <= 45
.BackColor = vbYellow
Case Else
.BackColor = vbGreen
End Select

End If

End With

End Sub
'----- end of code -----

 

[John W. Vinson]

Hello,

I am developing an application where I need to do some addition on dates.
I have an expiration date.

Case 1:
If the expiration date (a future date) - today's date is less than 30
days, display the field in red.

Case 2:
If the expiration date (a future date) - today's date is 31-45 days,
display the field in Yellow.

Case 3:
If the expiration date (a future date) - today's date is greater than
45 days, display the field in Green.

This code should execute anytime the form loads, or when moving to a
different record.

Can anyone help me with the code?

You can use Format... Conditional Formatting on the menu. Select "Field Value
Is" in the left hand dropdown, and use Between Date() AND Date() + 30; Date()
+ 31 and Date() + 45 and so on in three different condition rows.