Nested Iif problem

[GOVMATE]

Hello,

I'm having a problem writing a query with nested iif's. This query is suppose to manipulate a text field that with #'s. Each field is either 5 or 6 characters containing a date such as "20607" or "10607" which is in the mm,dd,yy format. Can you have a nested iif statement such as below with multiple with 3 possible true conditions and only one false condition?

SELECT [date last stmt]
Iif(Instr(1,[date last stmt],"0")=1,Left([date last stmt],2)+"/"+Mid([date last stmt],3,2)+"/"+Right([date last stmt],2),
IIf(Len([date last stmt])=6,Left([date last stmt],2)+"/"+Mid([date last stmt],3,2)+"/"+Right([date last stmt],2),
IIf(Len([date last stmt])=5,Left([date last stmt],1)+"/"+Mid([date last stmt],2,2)+"/"+Right([date last stmt],2,"error")))) AS Newdate
FROM Exercise1;

Each time I attempt to run this query I receive a syntax error in query expression"[date last stmt]"

Any help is appreciated!!!!

 

[KeithG]

try this

IIf(Len([date last stmt])=6,Left([date last stmt],2) & "/" & Mid([date last stmt],3,2) & "/" & Right([date last stmt],2),IIf(Len([date last stmt])=5,Left([date last stmt],1) & "/" & Mid([date last stmt],3,2) & "/" & Right([date last stmt],2),"Error"))

 

[GOVMATE]

try this

IIf(Len([date last stmt])=6,Left([date last stmt],2) & "/" & Mid([date last stmt],3,2) & "/" & Right([date last stmt],2),IIf(Len([date last stmt])=5,Left([date last stmt],1) & "/" & Mid([date last stmt],3,2) & "/" & Right([date last stmt],2),"Error"))

I used the above statement and it ran, but the results where incorrect. For example if date last stmt = string "13107" the result the above query gives is "1/10/07" and if the date last stmt = string "122906" the result is correctly displayed as "12/29/06"

Any suggestions?????????

 

[GOVMATE]

Thanks Keith for your help....I got it to work, I just needed to modify the mid function.