Hi,

The following query:
update People set [StartDate] = (
select DateValue([Actual Start Date])
FROM ActualStartDates
Where ActualStartDates.ID = People.StudentID
and [Actual Start Date] IS NOT NULL)

gives me the following error message:
operation must use an updatable query.

The following two queries work:
update People set [StartDate] = #1/1/2007#

and

select DateValue([Actual Start Date])
FROM ActualStartDates, People
Where ActualStartDates.ID = People.StudentID
and [Actual Start Date] IS NOT NULL

ID and StudentID are primary keys, therefore I select every time EXACTLY ONE Date.

Thanks for your help,
firla

What's wrong with the last iteration? That looks like the way to accomplish this task.

The reason the first doesn't work is that, although you and I know the subquery can only return one record, the database engine doesn't know that. I guess you could try adding "top 1" after the "select", but I'm not sure it will work. You could also try "group by". And with that said, see my first paragraph.

Thanks a lot George for your answer. Concerning the first query, I tried including "top 1", but it still doesn't work :-(

Thanks,
firla