Prev: What is wrong with this equation?
Next: SQL searches
From: jim on 24 Mar 2010 23:37
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
From: RonaldoOneNil on 25 Mar 2010 07:53
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))))

"jim" wrote:

> 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
 | 
Pages: 1
Prev: What is wrong with this equation?
Next: SQL searches