Trouble with an Update query

[sagamw]

I have a form to update a specific field in a table of records i.e. the user writes a value to an unbound text field and presses a button to run an update query that uses the string from the unbound field.

I thought it was a simple task but... :-(

SQL of the update query goes like this (and it's obviously wrong):

UPDATE tblImages SET tblImages.ImagePath = [NewPath].[value] &
Right([ImagePath], Len([ImagePath]) - InStrRev([ImagePath], "\")) WHERE (((tblImages.DeleteMark)<0));

The Right([ImagePath], Len([ImagePath]) - InStrRev([ImagePath], "\")) I use it elsewhere in the VBA code and it works fine.

With my update query I am trying to "update" the [Imagepath] field of the tblImages to be the unbound text field [NewPath].[value] plus the right part of [ImagePath]

What I am doing wrong?

 

[sagamw]

Well I found it!

If you write the right function in VBA you use "," like right (string,5) BUT in SQL you need semicolon ";" like right (string;5)

Maybe it's my non-english version of Access...

All OK now!

 

[MajP]

How would it know which form your unbound control is on? You need to specify.

[Forms]![YourFormName].[NewPath]

I would probably build a custom function and use it in my query
... tblImages.imagepath = getNewPath([imagePath])

Untested, something like:

Public Function getNewPath(imagePath as variant) As String
dim strNewPath
if not isnull(imagePath) then
strNewPath = forms("yourForm").newpath
getNewPath = strNewPath &
Right([ImagePath], Len([ImagePath]) - InStrRev([ImagePath], "\"))
end if
End Function

 

[sagamw]

Yes. MajP thanks!
I had corrected that but I didn't wrote it in my post.

P.S. Why can't we EDIT our previous posts in the forum. Or am I missing something..