I've created a database (MS Access 2003) to help monitor staffing at my work.
I now need to transcribe data from one table (tblPositions) into another (tblTeams) based on a common column (Position ID). Essentially I wish to copy the [Position title] and [Position description] of different positions from tblPositions, into tblTeams according to their already recorded [Position ID] (this is to make the data more readable).
Having looked through this and other forums I've been attempting to automate this process using an UPDATE statement. It is as follows:
Expand|Select|Wrap|Line Numbers
- UPDATE tblTeams SET tblTeams.[Position description] = (DLookUp("[Description]","tblPositions","(tblPositions.[Position code] = " & tblTeams.[Position code] & ""))
- WHERE (((tblTeams.[Position code])=30471524));
Every time I've trialled this query however I receive an error box citing a Conversion error as my problem. I've checked and re-checked again and again and as far as I can tell all the fields (description, title, position ID) all have identical formatting.
Is there any other way a Conversion error might be generated?
Is there a better way of doing this?
As a worst case scenario I can manually re-type the data (only about 200-250 records) but I think solving this now will lead to many time saving gains in the future.
Many thanks!
James