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
Add a comment
|
1 Answer
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 :)
