Query: Check two fields and pick one value based on Iif statement

[SilverLining]

I have two queries that are decoding one field from a table each (table for wildland fires, and table for all the other fires). I should note that I didn't build this original database and I cannot change any of the tables themselves.

I have a query called Pivot_WildlandB with a field FRWFireCause and another query called Pivot_SummaryB with a field called FireCause. Both those queries feed into a large append query. Currently the large append query displays both FRWFireCause and FireCause. I am trying to build a query that would combine them into one field named Cause. I've tried using an iif statement like this> Cause: Iif(Not IsNull[FRWFireCause] & [FireCause] = "All Other Causes", [FRWFireCause], [FireCause] but it keeps saying expression has invalid syntax and then highlighting the FRWFireCause right after Not IsNull.

I need the iif statement to return the value in FRWFireCause if FRWFireCause is not null and if FireCause is "All Other Causes". Otherwise, I need the query to return the FireCause value. I've done them before but hitting a brick wall here. Any help would be much appreciated!

[pbaldy]

Well for starters, IsNull() is a function requiring parentheses:

IsNull(FieldName)

[SilverLining]

Thank you Paul. Now it will at least run but it won't return FireCause if the criteria fail. It just puts a blank now. It's also returned FRWFireCause as Cause even if it doesn't meet the "All Other Causes" criteria.

[pbaldy]

I suspect you want And rather than &.

[SilverLining]

That solved it! Thank you. The final solution was this

Cause: IIf(Not IsNull([FRWFireCause]) And [FireCause]="All Other Causes",[FRWFireCause],[FireCause])

[pbaldy]

Happy to help and welcome to the site by the way!