Help with Update Query

Hi -

In what context are you using the line that gives you the compile error? If
you are assigning it to a variable, e.g. strSQL = ...., then the quotation
marks have to be doubled as they are part of the string:

strSQL="UPDATE Master_House SET ExRef1 = Replace([ExRef1],""SERVICE CONTRACT
NUMBER "","""")"

The same is true if you are using it in db.execute ...

Hope this helps

John



gchichester wrote:
John,

For some reason this SQL statement is getting this error "compile error. in
Replace([ExRef1],"SERVICE CONTRACT NUMBER ","".
even after working the first time. Also it seems odd to me that the error
message is truncating the ending ) from the statement.

This is the statement from my query
UPDATE Master_House SET ExRef1 = Replace([ExRef1],"SERVICE CONTRACT NUMBER ",
"");

Thanks again for you thoughts.
Gil

I have a imported table from a SQL DB that I need to remove part of the data
from a field and leave the rest.
[quoted text clipped - 17 lines]
numbers contain alphabetic characters; just *don't* try to change it to a
Number datatype field.

--
John Goddard
Ottawa, ON Canada
jrgoddard at cyberus dot ca

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/200911/1

On Thu, 12 Nov 2009 15:51:48 GMT, "gchichester via AccessMonster.com"
u46492@uwe wrote:

John,

For some reason this SQL statement is getting this error "compile error. in
Replace([ExRef1],"SERVICE CONTRACT NUMBER ","".
even after working the first time. Also it seems odd to me that the error
message is truncating the ending ) from the statement.

This is the statement from my query
UPDATE Master_House SET ExRef1 = Replace([ExRef1],"SERVICE CONTRACT NUMBER ",
"");

Thanks again for you thoughts.

Please post your actual code or the SQL view of the complete query. John
Goddard's suggestion about quotemarks is likely the cause.
--

John W. Vinson [MVP]

John,
This is a copy of the exact statement from my "Update Query"
UPDATE Master_House
SET ExRef1 = Replace([ExRef1],"SERVICE CONTRACT NUMBER ","");
I have tried removing the quote marks but still received error message.
I thought it might help so I published 2 pics of the error messages
Gil

John W. Vinson wrote:
John,

[quoted text clipped - 8 lines]

Thanks again for you thoughts.

Please post your actual code or the SQL view of the complete query. John
Goddard's suggestion about quotemarks is likely the cause.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/200911/1

On Fri, 13 Nov 2009 18:37:58 GMT, "gchichester via AccessMonster.com"
u46492@uwe wrote:

John,
This is a copy of the exact statement from my "Update Query"
UPDATE Master_House
SET ExRef1 = Replace([ExRef1],"SERVICE CONTRACT NUMBER ","");
I have tried removing the quote marks but still received error message.
I thought it might help so I published 2 pics of the error messages

Are you running this query from the Query window, or from code? I can't see
your pics (and no, I'm not going to get an AccessMonster account thank you).

You might try

UPDATE Master_House
SET ExRef1 = Replace([ExRef1],"SERVICE CONTRACT NUMBER ","")
WHERE [ExRef1] LIKE "*SERVICE CONTRACT NUMBER*";

--

John W. Vinson [MVP]

John,
Query window.
I think you can see the pics without a AM Account at
http://advenet.com/csx/photos/default.aspx


John W. Vinson wrote:
John,
This is a copy of the exact statement from my "Update Query"
UPDATE Master_House
SET ExRef1 = Replace([ExRef1],"SERVICE CONTRACT NUMBER ","");
I have tried removing the quote marks but still received error message.
I thought it might help so I published 2 pics of the error messages

Are you running this query from the Query window, or from code? I can't see
your pics (and no, I'm not going to get an AccessMonster account thank you).

You might try

UPDATE Master_House
SET ExRef1 = Replace([ExRef1],"SERVICE CONTRACT NUMBER ","")
WHERE [ExRef1] LIKE "*SERVICE CONTRACT NUMBER*";


--
Message posted via http://www.accessmonster.com

John,
Sorry I should have tried your new statement before send the last post.
It is working like I expected.
Thanks again
Gil

John W. Vinson wrote:
John,
This is a copy of the exact statement from my "Update Query"
UPDATE Master_House
SET ExRef1 = Replace([ExRef1],"SERVICE CONTRACT NUMBER ","");
I have tried removing the quote marks but still received error message.
I thought it might help so I published 2 pics of the error messages

Are you running this query from the Query window, or from code? I can't see
your pics (and no, I'm not going to get an AccessMonster account thank you).

You might try

UPDATE Master_House
SET ExRef1 = Replace([ExRef1],"SERVICE CONTRACT NUMBER ","")
WHERE [ExRef1] LIKE "*SERVICE CONTRACT NUMBER*";


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/200911/1

Hi -

Glad to see you have it working. Looking at your posted screenshots, in both
compile errors you had unbalanced parentheses - you were missing a closing ")
"

John


gchichester wrote:
John,
Query window.
I think you can see the pics without a AM Account at
http://advenet.com/csx/photos/default.aspx

John,
This is a copy of the exact statement from my "Update Query"
[quoted text clipped - 11 lines]
SET ExRef1 = Replace([ExRef1],"SERVICE CONTRACT NUMBER ","")
WHERE [ExRef1] LIKE "*SERVICE CONTRACT NUMBER*";

--
John Goddard
Ottawa, ON Canada
jrgoddard at cyberus dot ca

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/200911/1