Combine iif statements, is null and is not null 1

[HEMASUR]

I have a query with the following statement

IIf(IsNull([DeceasedDate]![qry Compare 2006]) And (IsNull([Enrolled]![qry Compare 2007])),"New",IIf([DeceasedDate]![qry Compare 2006]="*12*","DECEASED"))

The query is getting data from 2 other queries.

Query1 has member #s and deceased date
Query2 has member #s and other data.

Need to combine the two queries.
The first part of the above query works, showing the status "NEW".
The second part does not seem to work.
where I want to show "Deceased", for members with a Deceased date.
I tried to use "IS NOT NULL", but that does not seem to work, so I had to use "*12*" as it appears in the table for the time in the date field,
Would really appreciate any help I can get.
Thank you!!

 

[PHV]

Something like this ?
IIf(IsNull([DeceasedDate]![qry Compare 2006]), IIf(IsNull([Enrolled]![qry Compare 2007]),"New","STILL ALIVE"), "DECEASED")

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[HEMASUR]

Thanks, but the Status column should either be null or have the deceased date in it

If both columns Deceased and Enrolled are null then "NEW", if Deceased has a Date, then Deceased Date, from Deceased column, else it should be null

 

[PHV]

You'll likely get a type mismatch error mixing text and date, but here a starting point:
IIf(IsNull([DeceasedDate]![qry Compare 2006]), IIf(IsNull([Enrolled]![qry Compare 2007]),"New",Null), [DeceasedDate]![qry Compare 2006])

Another way:
IIf(IsNull([DeceasedDate]![qry Compare 2006]) AND IsNull([Enrolled]![qry Compare 2007]),"New",[DeceasedDate]![qry Compare 2006])

Yet another way:
Nz([DeceasedDate]![qry Compare 2006], IIf(IsNull([Enrolled]![qry Compare 2007]),"New", [DeceasedDate]![qry Compare 2006]))

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[HEMASUR]

Thanks PHV,
But it did not work, I get an error message saying the expression is too complicated to evaluate.
I will have to try something else.
I am using Access 2007.

 

[PHV]

What is the SQL code of your not working query ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[HEMASUR]

I modified the query with only the required fields, now I don't get the error message, but the status is "NEW" for both criterias' i.e., when Enrolled is null, and when DeceasedDate has a date.
Thanks for working with me on this!!!