Nested IIf and IsNull

  • Thread starter Thread starter jim
  • Start date Start date
J

jim

I can't seem to get to syntax right on this query field. The nesting goes
this way:
find City Name by: if JobAddress4 and JobAddress3 is blank then get leftmost
characters of JobAddress2(always populated) up to ",", if JobAddress3 not
null get leftmost characters of JobAddress3 up to ",", and if JobAddress4 not
null get leftmost characters up to ",".

City:IIf(IsNull([tblClientBuildings].[JobAddress4]),IIf(IsNull([tblClientBuildings].[JobAddress3]),Left(([tblClientBuildings].[JobAddress2]),Inst([tblClientBuildings].[JobAddress2]),
",",-1),Left(([tblClientBuildings].[JobAddress3]),Inst([tblClientBuildings].[JobAddress3]),
",",-1),Left(([tblClientBuildings].[JobAddress4]),Inst([tblClientBuildings].[JobAddress4]), ",",-1)))
I get message function I've entered has wrong number of arguments. Any ideas?
TIA
 
Not tested but try this

City:IIf(Not
IsNull([tblClientBuildings].[JobAddress4]),Left([tblClientBuildings].[JobAddress4],Instr([tblClientBuildings].[JobAddress4],
",",-1)),IIf(Not
IsNull([tblClientBuildings].[JobAddress3]),Left([tblClientBuildings].[JobAddress3],Instr([tblClientBuildings].[JobAddress3],
",",-1)),Left([tblClientBuildings].[JobAddress2],Instr([tblClientBuildings].[JobAddress2], ",",-1))))
 
Back
Top