iff(mid(expr),len(expr)-1,1)=" ",1,iif(mid(expr),len(expr)-2,1)=" ",2,3)

can anyone please tell me what is wrong with the syntax on this expression
it is supposed to identify the last space in the expr field
if the last space is one charechter from the end it is supposed to return one, if the last space is two digits frm the end it is supposes to return two and for any other answer it is supposed to return three

thanx a lot

You're using the Mid function in a wrong way.
Here's the right syntax:

IIF(mid(expr,Len(expr)-1,1)=" ",1,
IIF(mid(expr,Len(expr)-2,1)=" ",2,3))

Another way (among other ways) is to use the InStr function:

IIF(Instr(1, expr," ")=0,3,Len(expr)-Instr(1, expr," "))

In Access Help is perfectly described how these functions operate.


RV