Status Of Employee!

[cap.zadi]

Hi, I am trying to find the status of employee if he is available or on vacation or already applied for vacation by using the below Iff statement which seems poor solution.

EmpStatus: IIf(IsNull([AVStartDate]) Or IsNull([AVEndDate]),"Available",IIf([AVStartDate]<=Date() And [AVEndDate]>=Date(),"On Vacation","Already Applied "))

Because if some already spend his vacation back to work so it will show he already applied.

The AVStartDate and AVEndDate are actual dates which are filled in the leave application and he traveled.

Any logical solution for this statement?

[June7]

Simplify expression by using BETWEEN AND instead.

IIf(Date() BETWEEN [AVStartDate] AND [AVEndDate])

What table are you querying?

Probably need to apply filter to table where leave requests are stored or use DLookup().

[Micron]

Sometimes, complex expressions are handled better by functions that your query can call. e.g.

Function Vacations(dteStart As Variant, dteEnd As Variant) As String
Select Case Date
    Case dteStart To dteEnd
        Vacations = "On Vacation"
    Case Is >= dteEnd
        Vacations = "Already Applied"
    Case (IsNull(dteStart) And IsNull(dteEnd))
        Vacations = "Available"
End Select
    
End Function

You include the date fields in your query and have a calculated field like Status: Vacations([AvStart],[AvEnd]) - using your own table and field names of course. Perhaps you'd agree that this would certainly be a better way if you had many possibilities, rather than having a mind-bending set of nested IIF's.
Not saying this would be better in your case, just putting it out for consideration.

[cap.zadi]

Hi,

Thanks. I will query the main table of leave applications along (fk) table employees to get employee full information.

[cap.zadi]

Hi,

Thanks.

Yeah i do agree with your opinion. Its more easy and can offer more easy handling to complex scenarios. The nested Iff will be to complex for this kind of requirements.

I will try your given suggestion and lets see the results.

[cap.zadi]

Hello Micron

Thanks for the help to show a new way to create such wonderful functions.

I modified it little bit but its not showing any results for Null fields.

Function Vacations(AVStartDate As Variant, AVEndDate As Variant) As String
Select Case Date
Case AVStartDate To AVEndDate
Vacations = "On Vacation"
Case Is < AVStartDate
Vacations = "Already Applied"
Case Is >= AVEndDate
Vacations = "Available"
Case (IsNull(AVStartDate) And IsNull(AVEndDate))
Vacations = "Available"

End Select

End Function

Let me explain the requirements again and put it in two categories only:

* The purpose of this function is to show the current status of employee which could be "On Vacation" or Available" regarding the two fields taken from TblEmpLeave AVStartDate and AVStartDate

a. If the today's date between the both AVStartDate and AVEndDate then its "On Vacation".
b. If today's date is not between both either its earlier than AVStartdate means he did not started his vacation or later than AVEndDate means he came back already. Then status should be "Available"
*This is only if may he applied last year or some other time and the both fields are have data.

c. If both fields are null then for sure he neither applied and nor on vacation so status should be "Available"

[Micron]

Not only did I forget you can't handle Null in a Select Case in that manner, my comparison went from evaluating Date() to evaluating the variables in the same Select. That's just plain dumb.
You could handle this in more than one way. Change the Select block to

Select Case Date
    Case dteStart To dteEnd
        Vacations = "On Vacation"
    Case Is >= dteEnd
        Vacations = "Already Applied"
    Case Else
        Vacations = "Available"
End Select

or use

If Not IsNull(dteStart) And Not IsNull(dteEnd) Then
  Select Case Date
    Case dteStart To dteEnd
        Vacations = "On Vacation"
    Case Is >= dteEnd
        Vacations = "Already Applied"
  End Select
Else
  Vacations = "Available"
End If

Either seems to work if there are no dates in either field, or one date in either field regardless of which one it is. In testing that scenario, I may not have used dates that reflect your situation so you'll have to do some testing

As for dealing with Null in Select blocks, one can convert with Nz, but that wouldn't have helped in this case.

[cap.zadi]

Hi Thanks for the efforts. Appreciated.

Lets have the simple and two way requirement either "On Vacation" or "Available". If the dates are between

I used this code:

Function Vacations(AVStartDate As Variant, AVEndDate As Variant) As String


If Not IsNull(AVStartDate) And Not IsNull(AVEndDate) Then
Select Case Date
Case AVStartDate To AVEndDate
Vacations = "On Vacation"
End Select
Else
Vacations = "Available"
End If
End Function

This is given results fine for both cases but if there is any past dates in these two fields, its returning empty field in the results.

Kindly advise for the above two way requirement only.

[Micron]

You'll have to provide data along with the expected results so I can figure out what you're really saying. Here's an example where past date gives a result, which doesn't appear to agree with what you're saying.

EmplID	AvStart	AvEnd	Status
777	10/01/18	10/12/18	Already Applied

A select case with only one case isn't what I'd do, and that's where you seem to be now.

[cap.zadi]

Hi

Thanks for the efforts. Its already done after few corrections.

Appreciated your time and concern to guide through this all.