IsBlank?/IsNull/<>"" - Nothing is working

[sellis]

Here is the code I am currently working with:

Days Worked: IIf([Employee Basic Info]![Status]="A" Or "L",IIf([Emp Date]![Term Date]<>""),DateDiff("d",[Emp Date]![Rehire Date],"5/31/2011"),DateDiff("d",[Emp Date]![Hire Date],"05/31/2011")))

I have tested each item individually, but once I put them together, the whole thing blows up. The answers for everything are correct except the DateDiff Rehire Date portion just after the second IIf statement. Those are coming back as #error. Myself and our Access person have narrowed it down to the IIf statement. I have tried using a null statement, have tried the <>"" and we have even started trying the IsEmpty and IsMissing statements. Nothing seems to be working for me. The general idea here is that I have a couple colomns of dates for my employees - a hire date a term date and a rehire date. I want to count the number of days between the hire date and a given date (05/31/2011) in this case UNLESS they have been termed and then rehired. If they were termed and rehired, then I need to count the difference in days between the rehired date and the given date (05/31/2011). I am in Access 2007. Any help is greatly appreciated as always.

 

[boblarson]

This is invalid syntax:

([Employee Basic Info]![Status]="A" Or "L",

You have to repeat the field:

([Employee Basic Info]![Status]="A" Or [Employee Basic Info]![Status]="L",

Unless you use the IN keyword

[Employee Basic Info]![Status] In ("A", "L")


A date field will never be an empty string "" so <>"" doesn't work.

So

[Emp Date]![Term Date] Is Not Null

 

[sellis]

The is not null was not working, but I think it was a parenthesis problem, because I just removed them and put them back in where they belong, and now it works just fine. Gosh I love Access. Picky thing. Thanks as always for your speedy help, and I did fix the other thing. I do not currently have anything in there but A and L, but I will soon, so I am glad you helped me fix it now.

 

[alansidman]

The syntax for an IIF statement is =iif(Statement to test, Result if true, result if False)

When you add an or to an iif statement it should read:

=iif(Field1=x or Field1=y,result if true, result if false)

If you need to nest them, then you need to give a true result then start your nesting for the false result.

An example would be

=iif(Field1=x or Field1=y, True Result, iif(Field1=a, True Result, False Result))

This should get you on the right track for your nested iif statment. MOre information at

http://www.techonthenet.com/access/functions/advanced/iif.php

Alan

 

[sellis]

This is how I am used to doing them, but this was a nested statemend in the true result, which, yes, should be the same, but for some reason was driving me batty. Thanks for the link. More info, means more knowledge.