Need to create a tickler to indicate when a project is over due. 2

[Sherman6789]

I have set up a database which is being designed to record and track small to medium projects and tasks. The task name, brief description, person assigned to the task, begin date, due date and actual completion date are records along with other information.

What I need is a simple tickler system which will indicate when a project is overdue or within 5 days of being due according to the due date.

How can I:

1. Have the due date turn "yellow" when its due date is within 5-days and blink and/or turn red when the due date has
been reached. Otherwise the due date will be black.

2. Make a report which will list all projects or tasks that or over due or within 5-days of being due.

I know how to make forms and reports but don't know how to write the code necessary to detect when a project is over due or within 5-days of being due. I guess that the code will automatically compare the current date to the due date and follow a rule. I also guess that this will be in a query for the report.

Any help you can give will be appreciated.

Thanks!

 

[PHV]

Have a look at the DateDiff function.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[fneily]

I created a dynamic timeline graph that shows a project startdate, duedate, breached date, etc. that changes color based on the situation. It also will tell when the project has expired or not. Quite impressive. The only trouble is that Tek-tips doesn't allow email addresses to be posted. It'll take awhile for me to consolidate the code, and explain it then post it. If you want, I'll try to get it to you today. If you search on fneily, you might find a post with my email on it. I still get emails from people who did this and wanted to be sent an actual database.

 

[fneily]

I'm sure this will be hard to visualize, but I'll try to convey what I did in words. You'll be creating a timeline going left to right.
You'll find some redundant and/or extra coding in this. The reason is that this was created over three months and the client kept changing his mind, adding new conditions, etc. and I didn't feel like rewriting everything.
The code shown is for only one bar. My project had four bars being present in one record. So I'm showing you just a part. You'll get the idea from it.
In the Page Header, over the month headings, I created an Invisible textbox whose width was nearly as long as the page. The name of the box in the code is called
Me!boxTimeLine. It represents a year. You'll see that I divided this by 365 to break it into days. You'll have to change the distance from the left margin of the page to the left border of the texbox into twips from inches. There's 1440 twips to the inch.
For the month headings in the Page Header section, I created textboxes. The first was called txtMth0 with Control Source =Now(). Then the next textboxes had
=DateAdd("m",2,[txtMth0]) as the Control Source, changing the second parameter to create the neccessary month. This allowed the months to automatically update every new month, so you always saw a year worth of months on the chart.
To draw the bars, I created a Label. In the code, the name is Modal.
All measurements are in twips. You'll see a +5 on some lines. I add to add 5 twips to get things lined up evenly, so it's a fudge factor.
Notice the code goes on the Format event of the Detail bar in Design view.

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Dim lngDuration As Long 'days of tour
Dim lngDuration1 As Long
Dim lngDuration2 As Long
Dim lngDuration3 As Long
Dim lngStart As Long 'start date of tour
Dim lngStart1 As Long
Dim lngStart2 As Long
Dim lngStart3 As Long
Dim lngStartA As Long
Dim lngLMarg As Long
Dim dblFactor As Double
Dim holda As Long
Dim holdb As Long
'put a line control in your page header that starts 1/1 and goes to 12/31
lngLMarg = Me!boxTimeLine.left
dblFactor = Me!boxTimeLine.Width / 365
lngStart = 0

If IsNull(Me![Modal_Clearance_Table.ClearanceDate]) Then
lngDuration = 0
Me![Modal].BackStyle = 0
Me![Modal].Caption = ""
Me![Modal].BorderStyle = 0
Me![Modal].BorderWidth = 0
Else
Me![Modal].BackStyle = 1
If Me![Modal_Clearance_Table.Code] = "MCL" Then
Me![Modal].BackColor = 16777215
Me![Modal].SpecialEffect = 0
Me![Modal].BorderStyle = 0
Me![Modal].Caption = " Cleared"
Me![Modal].ForeColor = 4259584
GoTo jump
ElseIf Me![Modal_Clearance_Table.Code] = "MCR" Then
Me![Modal].BackColor = 16777215
Me![Modal].SpecialEffect = 0
Me![Modal].BorderStyle = 0
Me![Modal].Caption = " Cleared With Restrictions"
Me![Modal].ForeColor = 4259584
GoTo jump
ElseIf Me![Modal_Clearance_Table.Code] = "MCX" Then
Me![Modal].BackColor = 16777215
Me![Modal].SpecialEffect = 0
Me![Modal].BorderStyle = 0
Me![Modal].Caption = " Cleared - Exempt"
Me![Modal].ForeColor = 4259584
GoTo jump
Else
Me![Modal].BackColor = 8453888
Me![Modal].SpecialEffect = 4
Me![Modal].BackStyle = 1
Me![Modal].BorderWidth = 3
Me![Modal].BorderColor = 0
Me![Modal].BorderStyle = 1
Me![Modal].Caption = ""
End If
If Me![Date_of_Receipt] > Date Then
lngDuration = DateDiff("d", Me![Date_of_Receipt], Me![Modal_Clearance_Table.ClearanceDate]) + 5
lngStart = DateDiff("d", Date, Me![Date_of_Receipt])
ElseIf Me![Modal_Clearance_Table.ClearanceDate] > Date Then
lngDuration = DateDiff("d", Date, Me![Modal_Clearance_Table.ClearanceDate]) + 5
lngStart = 0
Else
If (Me![Modal_Clearance_Table.Code] = "MIP") Or (Me![Modal_Clearance_Table.Code] = "MUR") Then
Me![Modal].BackStyle = 1
Me![Modal].BackColor = 16777215
Me![Modal].SpecialEffect = 0
Me![Modal].BorderStyle = 0
Me![Modal].Caption = " EXPIRED"
Me![Modal].ForeColor = 0
lngDuration = 30
lngStart = 0
Else
jump: lngDuration = 65
lngStart = 0
Me![Modal].SpecialEffect = 0
Me![Modal].BackStyle = 0
Me![Modal].BorderStyle = 0
Me![Modal].BorderWidth = 0
End If
End If
End If


Me![Modal].Width = 10 'avoid the positioning error
Me![Modal].left = (lngStart * dblFactor) + lngLMarg
holda = (lngDuration * dblFactor)
If holda > 12200 Then
Me!Modal.Width = 12200
Else
Me![Modal].Width = (lngDuration * dblFactor)
End If

End Sub

Clear as mud.