MS access update query to update part of string

Question

Hi, I am trying to use an update query to update the first 3 characters of a string. but i am getting a syntax error

my query is

update table1, table 2 set left(table1.stringfield, 3) = table2.stringfield

thankstksy

Answer 1

OK, there's a couple of things

Firstly, your set statement should look something like this

set table1.stringfield = Left(table2.stringfield, 3) _
                            + mid$(table1.stringfield, 4)

This will take the first 3 chars from table2.stringfield, and the chars from 4 onwards in table1.stringfield.

This will run into problems if table1.stringfield is less than 4 characters long, you might want something like

set table1.stringfield = Left(table2.stringfield, 3) + 
iif(len(table1.stringfield) > 3, mid$(table1.stringfield, 4), "")

Secondly, you need to join the two tables together, I don't have MSAccess handy at the moment, the easiest way to do this is to put a straight forward update query together with the designer, and then look at the SQL view for that query.

Hope this helps :)