#Error with simple IIF Statement in MS-Access

[tomisin]

I am currently trying to write a simple function in MS-Access 2003 using IIF and a few other functions (LEN and LEFT). However, I get the #Error in the required fields. Please, find below the function I wrote. I will greatly appreciate your assistance -

Unique_ID: IIf(Len([Airline Activity - Detail - Jan]![Middlename_IA]=1),Left([Airline Activity - Detail - Jan]![Firstname_IA],2) & [Airline Activity - Detail - Jan]![Lastname_IA],Left([Airline Activity - Detail - Jan]![Firstname_IA],2) & [Airline Activity - Detail - Jan]![Middlename_IA])

 

[Joe4]

Welcome to the Board!

Looks like you have the right parend on your LEN function in the wrong place. It should be before the equal sign, not after, i.e.:

Unique_ID: IIf(Len([Airline Activity - Detail - Jan]![Middlename_IA])=1,Left([Airline Activity - Detail - Jan]![Firstname_IA],2) & [Airline Activity - Detail - Jan]![Lastname_IA],Left([Airline Activity - Detail - Jan]![Firstname_IA],2) & [Airline Activity - Detail - Jan]![Middlename_IA])

 

[Phildaburn]

Comment Deleted.

 

[revans]

You may also want to check your fields for nulls which may also be causing issues. The handy Nz() function works well for this. Something like

Unique_ID: IIf(Len(Nz([Airline Activity - Detail - Jan]![Middlename_IA],""))=1, ...

hth,

Rich

 

[tomisin]

Thank you all. Your comments have been very useful. I will also check for Nulls because the data field definitely has some nulls