query iif statement help

[swat]

Hello again all,

Looking for help with my nested if statement, it works correctly for the "if true" and "then" parts. Here is what I have for a query field:

Gas_Capacity: IIf(Year([Equip_Add_Rem_Log].[Date_Changed])>[year],IIf(([Equip_Add_Rem_Log].[Removal]=False),[Equipment].[Gas Capacity],"0"))


My problem is, if there is no "year(...)" greater than "[year]" on the table, the query returns a blank field (is this a null). How do I get the query to recognize a "" as a false return on the first "if true"?

Hmm, now that I think of it, it could possibly be an infinite return of falses if set up wrong. My head is spinning...any help would be greatly appreciated.



Shawn

[swat]

I have also tried to leave it as a blank using the same iif statement, then creating another query with:
1st query:
Gas_CapacityAdded: Sum(IIf(Year([Equip_Add_Rem_Log].[Date_Changed])>[year],IIf(([Equip_Add_Rem_Log].[Removal]=False),[Equipment].[Gas Capacity],"0")))
2nd Query:
EquipAddedAfter: IIf([EquipAfterCurrent].[Gas_CapacityAdded]="","0",[EquipAfterCurrent].[Gas_CapacityAdded])

But that returns an error in the field, no pop up though.

[boblarson]

How about:

Gas_Capacity: IIf(Year(Nz([Equip_Add_Rem_Log].[Date_Changed],0))>[year],IIf(([Equip_Add_Rem_Log].[Removal]=False),[Equipment].[Gas Capacity],0))

By the way, I took the quotes off of the 0 at the end because I am assuming based on the field name that it is a NUMBER and not TEXT. But you can correct me if wrong.

[swat]

Thank you for the quick reply Bob. You were correct for the 0. The Nz statement didn't work, but now that I am reading up on it, it looks like it should work. I will keep reading about it and see what I can come up with. Thank you again.

[swat]

I think I got it to work, I used the nz in the second query and got it to work.

EquipCapAddedAfter: Nz([EquipAfterCurrent].[Gas_CapacityAdded],0)

I just hate having so many queries to get to the end, but if it works then that is what matters I guess.

Thank you again for your help sir.