If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
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 |
#12
|
|||
|
|||
Help with Update Query
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] |
#13
|
|||
|
|||
Help with Update Query
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 |
#14
|
|||
|
|||
Help with Update Query
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] |
#15
|
|||
|
|||
Help with Update Query
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 |
#16
|
|||
|
|||
Help with Update Query
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 |
#17
|
|||
|
|||
Help with Update Query
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 |
|
Thread Tools | |
Display Modes | |
|
|