How to make Date shown under multiple condition?n

[msacswt]

Hi, Hopefully I may get solution form expert quickly.

I need to show date based multiple condition. I have 4 dates: They are: Date 1 =>Project Days Late, Date 2=> Project due date, Date3=> Project extension date, Date 4=> Project closed date.

The day Late on form needs to display under following condition:

days late (Date 1) = today - project due date (Date 3) when there is no extension date ( Date2 is blank). If there is extension date ( Date 2 is not blank). Days Late = Today - extension date. But results need to be: show number of days if today is later than due date, otherwise it show " On Track".


Really appreciate help



Jim

[ranman256]

build a custom function , then use it in a query.
in the query call:
select DueDateStatus([ProjDueDate]) as ProjectDue, DueDateStatus([ExtDate]) as ExtensionDate from table


Paste this code into a module:

public function DueDateStatus(pvDueDate)
if isNull(pvDueDate) then
       DueDateStatus= "no date set"
else
  if Date() < pvDueDate then
      DueDateStatus="On Track"
  else
      DueDateStatus=Abs(DAteDiff("d",pvDate,Date()) & " days late"
  endif
endif
end function

[msacswt]

HI Ranman,

Actually there are total 5 fields about Date.

1) Open Date
2) Due Date
3) Extension Date
4) Close Date
5) Days Late

Requirement:

Due date = Open Date + 25 working days
Days Late = today minus "Due Date" if "Extension Date" is blank, or today minus "Extension date" if "Extension Date" is not blank. If today minus the date>0, show actual number, otherwise show " On Track"

It looks simple, but it is very challenging to me.


It requires number or word on [Day Late] field.

When [ExtensionDate] is blank:
if today is later than [DueDate], [DayLate]=(today - [DueDate]),
If [today is earlier than [DueDate] [DayLate] shows "ON Track"

When [ExtensionDate] is not blank:
If Today is later than [DueDae], [DayLate]= (Today -[DueDate]),
If Today is earlier than [DueDate], [DayLate] = " ON Track"

[DueDate]=([OpenDate]+25 business days)



Many thanks !