I have "project number","start date", and "End date" text boxes. I want to diplay an alert, let say for example 2 weeks before the "End date".
I have lots of "project number" records. For example, prj1, prj2, prj3,...Each "project number" has different "start date" and "End date"...but they can be same sometimes.
How can i diplay an alert 2 weeks before the "end date" for each project?

Thanks in advance.

On the OnCurrent Event of the form you could do something like:

If Me.EndDate = Date + 14 Then
MsgBox "Two weeks to go"
End If

You'll need to refine it, what happens if the date falls at the weekend or no-one looks at the record on that date?



[This message has been edited by D B Lawson (edited 01-29-2002).]

Could it be refined by saying..


If Me.EndDate > EndDate + 14 Then
MsgBox " The end date has passed! "
End if

Lawson,
you are right, If the date falls at the weekend then how it can be solved...I have never done such a thing.
Also, each project has start date and end date, for example, lets say i just got an alert, then how can i tell to which project does this alert belong.

Any help.

Put this code on the OnCurrent event of your form. You can jazz it up a bit later.

Dim ExpDate As Integer
ExpDate = DateDiff("d", Me.StartDate, Date)

If ExpDate > 14 Then

MsgBox "This project is overdue"

Else

MsgBox "This job has " & Str(ExpDate) & " days until the end date."

End If

Jwindon,
We are almost there but
can you explain to me this sentence:
DateDiff("d", Me.StartDate, Date).
what is "d"

I am getting wrong numbers.
what i want is for example,
if the "end date" is 2/16/2002 and today is 1/29/2002 then i should get the difference and if its passed the "end date" then it should say "overdue"

"d" tells the function to count "days"

The first criteria of Me.StartDate would be the value in the field StartDate.....the Date refers to the current date.

So .....count the difference between 1/29/02 and today's date.

If your value in StartDate is 1/20/02 and today is 1/29/02 then the function returns 9 and checks it against the greater than 14 days.

Are you getting warmer now?

Jwindon,
It works but I still have one problem.
I got the message before i open the form or i see the first record..Is there a way that i can attach my "mdb" file?

I've sent you my email address. There are several ways to skin this cat. Think about perhaps adding a little box on your form somewhere that shows red for overdue and blue if not....something along those lines...so they are not so interfering with data entry or scrolling thru forms as the message boxes will be.


Try this:

Put an unbound text box on your screen, set its font to white and make sure it is of significant size. Modify the code as such:

Private Sub Form_Current()

Dim ExpDate As Integer
Dim LeftDays As Integer

ExpDate = DateDiff("d", Me.StartDate, Date)
LeftDays = 14 - ExpDate

If ExpDate > 14 Then
Me.UnboundTextBoxName.BackColor = vbRed
Me.UnboundTextBoxName = "Overdue!"
Beep


Else

Me.UnboundTextBoxName.BackColor = vbBlue
Me.UnboundTextBoxName = "This job has " & Str(LeftDays) & " days left before completion is due."

End If

End Sub


[This message has been edited by jwindon (edited 01-30-2002).]

Sent your db back to you. For anyone else following the post...this is the code that I put on the form. The unbound text box is named "notebox"

Private Sub Form_Current()

If Me.NewRecord Then

Me.notebox.BackColor = vbBlue
Me.notebox = ""
Exit Sub

Else

If IsNull(Me![End Date]) Then

MsgBox "End date field is empty."
Me.notebox.BackColor = vbBlue
Me.notebox = ""
Exit Sub

End If

Dim ExpDate As Integer

ExpDate = DateDiff("d", Date, Me.[End Date])

Select Case ExpDate

Case 0 To 14

Me.notebox.BackColor = 0
Me.notebox = "Better Hurry,,,This job has " & Str(ExpDate) & " days until the end date."

Case Is < 0

Me.notebox.BackColor = vbRed
Me.notebox = "This project is overdue."

Case Is > 14

Me.notebox.BackColor = vbBlue
Me.notebox = "You are safe, This job has " & Str(ExpDate) & " days until the end date."

End Select

End If

End Sub

Perfect. Thats what i want http://www.access-programmers.co.uk/ubb/redface.gif)

Thanks a lot J.Windon