Dlookup and Update Query. Err: MS access cant update all records in the update query.

[pedie]

Hi, Currently 'm trying to update a huge table with emp. information. Whn i use below query it does not work.
Can we use dlookup in this Update Query?

Thanks

UPDATE MYTAB SET MYTAB.EMPSNAME = DLookUp([EMPSNAME],"EMPES",[EMPSID]=[EMPSID]);

 

[Montez659]

Try this and see if it works. I created 2 tables (EMPES and myTab) and created an update query. I tried to match your field names best I could, but double-check for accuracy.

UPDATE EMPES INNER JOIN myTab ON EMPES.EmpsID = myTab.EmpsID SET myTab.EmpsName = [EMPES].[EmpsName]
WHERE (([myTab].[EmpsID]=[EMPES].[EmpsID]));

Btw, I used the query design in MS Access to come up with this. You can always select the Update tab in Query design to work through this.

 

[Norie]

Pedie

You could use a DLookup, and you've almost got the syntax right.

I won't post what it should be because you shouldn't use DLookUp for this sort of thing.

Use a query like Montez has posted.

 

[pedie]

Thanks Motex, thanks Norie...that worked fine. I tried a lot of time and couldnt make it work...
Now with that it works perfect!

Thanks again.