IIf, And, Or combination in Access Query

[sparkes84]

Dear all,



This is my first post.

I am working within an Access query and cannot get the following code to work. Can anybody help me? It is saying that if the Project site actual recruitment end date is null and the Project_Site_Status is neither Open or Suspended, then put Yes, else put No.

IIf([Project site actual recruitment end date] Is Null And ([Project_Site_Status]<>"Open" Or [Project_Site_Status]<>"Suspended"), "Yes", "No")

Many thanks,
sparkes84

[ranman256]

When you start nesting IIFs it can give weird results. (OR should be AND here)
Is there no way you can just use these settings in criteria of the query rather than in an IIF?

in the query:
[Project_Site_Status]
not "Open" and not "Suspended"


[Project site actual recruitment end date]
Is Null

MyField: YES

[CJ_London]

a little bit of code on its own isn't much help. What does 'cannot get the following code to work' mean? you get an error? - if so what is the error description? or it returns the wrong value? or something else?

Suggest post the entire sql to your query together with a clearer explanation of what the problem is

Only thing I can see at the moment is you have an inconsistency in your field naming convention, sometimes you have spaces, sometimes underscores, perhaps they should all be one or the other

[sparkes84]

Thanks Ranman256. I changed the OR to And, making it work. I copied only a small part of a nested IIF statement so cannot make use of the criteria option in this case.

[CJ_London]

I changed the OR to And

([Project_Site_Status]<>"Open"AND [Project_Site_Status]<>"Suspended"),

am I missing something - how can Project_Site_Status be 'Open' and 'Suspended' at the same time?

answered my own question not =, it's <>