Status field in Query!

[cap.zadi]

Hi



I am looking for help to correct the below expression for query?

It is required to identify the status of employee if;

Available = If any of AStartDate or AEndDate is Null
On Vacation: If current date is in between AstartDate and AEndDate
Already Applied: if current date is still not reached to Astart Date.

Status: IIf(Nz([AStartDate],0)=0 Or Nz([AEndDate],0)=0,"Available",IIf([AStartDate] >= Now()," and [AEndDate] < = Now(), "Vacation","Already Applied "))

[ranman256]

Remove the IIFs, and make 2 queries:
1 for the Nulls,
1 for the rest.

[cap.zadi]

Sir

Thanks like? can you show example?

[June7]

If you want to show all employees and their status, just one query.

Remove comma and quote mark before the and operator.

Now() function returns date and time. You should probably use Date() which returns only date.

I think the operators are backwards for the "Vacation" calc.

Status: IIf(IsNull([AStartDate]) Or IsNull([AEndDate]), "Available", IIf([AStartDate] <= Date() And [AEndDate] >= Date(), "Vacation", "Already Applied "))

Save query and then can apply filter criteria to the constructed Status field for report output.

[cap.zadi]

Hi

Thanks and it worked.

regards