Taking one date from another to give number of days left

[kleaves]

HI, this should be relatively simple, but for some reason I cannot figure it out.

I have a field titled "DEADLINE" - a user inputs a date in this field (the datatype for this filed id date/time)

What I want to do is, on the form create a text box which takes the deadline date - todays date to give me the number of days to the deadline date. Once the dealine date has passed I then want to turn the counter to Red to show it is overdue. If there is no dealine then I want the field to say something like "No Deadline Set"

 

[ColinEssex]

Make the deadline field mandatory, that'll overcome the no deadline set bit.

I would just compare todays date with the deadline date by using code. You don't need to know how many days it is do you? Put this OnCurrent of the form.

If Me.Deadline = Date Then
 Msgbox "Deadline due today"
ElseIf Me.Deadline < Date Then
 Me.Deadline.Backcolor = VbRed
 MsgBox "Deadline Overdue"

End If

If you do need to know the number of days, use the DateDiff() function

Col

 

[kleaves]

Col

Unfortunately, I cannot make this field mandatory as its not always necessary to be filled in. Would this still work if the deadline date was blank?

Also in some cases, I would need to know when the deadline is passed by how many days - What would be nice is if it said something like:

"Deadline overdue by 21 days" and "Deadline due in 5 Days"

Any Ideas?

Karen

 

[KeithG]

Add anothe if statement to the above code to chech if the deadline is null. If it is you can display "No Deadline". Also you can use the dateDiff function to calculate days over due.

 

[kleaves]

OK, Thanks guys, will give it a try and see what i come out with.

Thanks for the help

Kleaves

 

[Jakboi]

If I wanted this same idea work on a report would it? I have business prospects and estimated closing date and would like to have the same:

0-90 Days 91-180 Days

Then have total the days left for those in a report...