Update-Set for a Date type not processing query part correctly in MS Access 2007 -

i'm trying extract text date date type field , change format yyyymmdd ddmmyyyy in process. have set simple select statement checking dates valid , if not setting default date , worked fine, no bad dates.

select iif(isdate(format(left([effectivedate],10),"dd/mm/yyyy")),format(left([effectivedate],10),"dd/mm/yyyy"),#01/01/1900#) expr1 relationships; 

but when embed exact same select statement in update set query:

update relationships  set msdate = iif(isdate(format(left([effectivedate],10),"dd/mm/yyyy")),format(left([effectivedate],10),"dd/mm/yyyy"),#01/01/1900#); 

the dates formed mmddyyyy , not ddmmyyyy select query does.

interestingly, when tried change format type "long date"

update relationships  set msdate = iif(isdate(format(left([effectivedate],10),"long date")),format(left([effectivedate],10),"dd/mm/yyyy"),#01/01/1900#); 

i got default 01/01/1900 result suggesting extracted not valid date. way, using query in select statement worked fine.

i can't thinking happening in conversion date type. tried datevalue on query still no joy.

since you're using left([effectivedate],10), assume text field contains yyyy-mm-dd (the iso format).

you should leave string in format (access understands iso , format mm/dd/yyyy best), , convert cdate() function.

update relationships  set msdate = iif(isdate(left([effectivedate],10)),                    cdate(left([effectivedate],10)),                    #1900-01-01#);