"IF" code needed

[Matizo]

Hello, I am looking for a code that will be showing the status of the work I’ve got.
I have 4 text boxes on the form: StartDate ; EndDate ; CurrentDate and WorkStatus. The StartDate and EndDate are linked to the tblWorks.
As the default value for CurrentDate I have =Date() .
I need a code that will do something like this:

If

StartDate < CurrentDate and EndDate < CurrentDate THEN WorkStatus = “Work Done”

If

StartDate < CurrentDate <= EndDate THEN WorkStatus = “Work In Progress”

If

StartDate > CurrentDate THEN WorkStatus = “FutureWork”

Thanks in advance,

 

[KeithG]

Something like below should work

IIF([Start Date]<Date(),IIF([End Date]<Date(),"Work Done","Work In Progress"),"Future Work")

 

[Matizo]

Thanks! I used that in my query and it works
But I still would appreciate if you could give me the VB code for my form.
Thanks again!
Matt

 

[KeithG]

You can put the expression as the control source of a textbox. Are you saying you want to create a function to do this? Why does this expression not suite your needs?

 

[Matizo]

Hi,

The expression you gave me is great and I am going to use it anyway I just wonder to find out what the function for that is. The use of “Case statement”.

Thanks a lot!,
Regards

Matt

 

[MarkK]

Function GetStatusText(dStart As Date, dEnd As Date) As String
   Select Case Date
      Case Is < dStart
         GetStatusText = "Future"
      Case Is > dEnd
         GetStatusText = "Past"
      Case Else
         GetStatusText = "Current"
   End Select
End Function