IIF Statement Help -- Returning #ERROR 1

[iuianj07]

Hello guys,

I am trying to create a query, that one column has this IIF statement... First Off, I have a field named Est_Date_Of_Completion which is a date field, and a checkbox IndexingOnHold...

I created this IIF statement in a query

IIf([Situs_Est_Date_Of_Completion] Is Null And [IndexingOnHold]=0,[Situs_Est_Date_Of_Completion],IIf([Situs_Est_Date_Of_Completion] Is Null  And [IndexingOnHold]-1,"ON HOLD",[Situs_Est_Date_Of_Completion]))

and whenever I run the query,, for the fields that have Est_Date_Of_Completion IsNull and IndexingOnHold = -1 (checked) it returns #ERROR...

But whenever I try to have the iif statement the other way around:

IIf([Est_Date_Of_Completion] Is Null And [IndexingOnHold]-1,"On Hold", IIF([Est_Date_Of_Completion] is null and [IndexingOnHold] 0,[Est_Date_Of_Completion],[Est_Date_Of_Completion])

it doesn't give the #ERROR but for Est_Date_Of_Completion IsNull and IndexingOnHold = 0... it still gives the ON HOLD result...

any help is greatly appreciated.

Thanks

 

[dhookom]

You have a minus sign rather than = and no sign where you need =

IIf([Est_Date_Of_Completion] Is Null And [IndexingOnHold][b][red] = [/red][/b]1,"On Hold", IIF([Est_Date_Of_Completion] is null and [IndexingOnHold][b][red] = [/red][/b]0,[Est_Date_Of_Completion],[Est_Date_Of_Completion])

IMO, you should not have a function/expression that might return a date or might return a string.

Duane
Hook'D on Access
MS Access MVP

 

[iuianj07]

hello dhookom,

Pardon for my little confusion, but I always thought that

-1 represents as "checked" on a yes/no (checkbox) data type

all of my queries that is filtered to checkbox as checked... I write -1... can you please explain the difference.

I apologize if I caused confusion, or if I misunderstoon on that concept.

thanks

 

[PHV]

So, you simply wanted this ?
IIf([Est_Date_Of_Completion] Is Null And [IndexingOnHold] = True, "On Hold", [Est_Date_Of_Completion])

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