Help with Update Query

[gchichester via AccessMonster.com]

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.
The data in the field is SERVICE CONTRACT NUMBER 5035764A09, I just need the
contract number not the text.
But some contract #s my contain text. All records have the text "SERVICE
CONTRACT NUMBER" in the field.
I'm not sure If a update query will work or not.

Thanks for any and all suggestions

Gil

 

[Jeff Boyce]

Does the "text" always come at the same place in the string?

Are all the strings the same length?

Are you trying to remove the "A" from inside the end of the number, or only
the first 24 characters?

More info, please...

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or psuedocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[Jeff Boyce]

Please note ... for comparison purposes, you may need to preserve what you
have. Instead of updating, perhaps it would be sufficient to build a query
that only returns that part you wish to see/use.

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or psuedocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[gchichester via AccessMonster.com]

Jeff,
Yes the text is always in the first part of the string.
only the words "Service Contract Number"
The actual contract 3 may have text in it
Gil

Does the "text" always come at the same place in the string?

Are all the strings the same length?

Are you trying to remove the "A" from inside the end of the number, or only
the first 24 characters?

More info, please...

Regards

Jeff Boyce
Microsoft Access MVP

I have a imported table from a SQL DB that I need to remove part of the
data

[quoted text clipped - 9 lines]

Gil

 

[gchichester via AccessMonster.com]

No,
All I want is to end up with a all records with just the contract # in it.

Gil

Please note ... for comparison purposes, you may need to preserve what you
have. Instead of updating, perhaps it would be sufficient to build a query
that only returns that part you wish to see/use.

Regards

Jeff Boyce
Microsoft Access MVP

I have a imported table from a SQL DB that I need to remove part of the
data

[quoted text clipped - 9 lines]

Gil

 

[J_Goddard via AccessMonster.com]

Hi -

You can use an update query with the "replace" function. The SQL might be:

UPDATE TableName set FieldName = trim(Replace(Fieldname,"Service Contract
Number",""))

with "Trim" used to get rid of leading/training blanks in FieldName.

HTH

John

 

[J_Goddard via AccessMonster.com]

Hi -

You can use an update query with the "replace" function. The SQL might be:

UPDATE TableName set FieldName = trim(Replace(Fieldname,"Service Contract
Number",""))

with "Trim" used to get rid of leading/training blanks in FieldName.

HTH

John

 

[John W. Vinson]

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.
The data in the field is SERVICE CONTRACT NUMBER 5035764A09, I just need the
contract number not the text.
But some contract #s my contain text. All records have the text "SERVICE
CONTRACT NUMBER" in the field.
I'm not sure If a update query will work or not.

Thanks for any and all suggestions

Gil

If you just want to remove the text "SERVICE CONTRACT NUMBER " from all the
records containing it, leaving only "5035764A09", you could run an update
query. Either of these would work:

UPDATE yourtable
SET fieldname = MID([fieldname], 25)

or

UPDATE yourtable
SET fieldname = Replace([fieldname], "SERVICE CONTRACT NUMBER ", "")

It's obviously a Text field already, so it won't matter that the contract
numbers contain alphabetic characters; just *don't* try to change it to a
Number datatype field.

 

[gchichester via AccessMonster.com]

John,
Thanks allot query works like a champ!

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 - 7 lines]

Gil

If you just want to remove the text "SERVICE CONTRACT NUMBER " from all the
records containing it, leaving only "5035764A09", you could run an update
query. Either of these would work:

UPDATE yourtable
SET fieldname = MID([fieldname], 25)

or

UPDATE yourtable
SET fieldname = Replace([fieldname], "SERVICE CONTRACT NUMBER ", "")

It's obviously a Text field already, so it won't matter that the contract
numbers contain alphabetic characters; just *don't* try to change it to a
Number datatype field.

 

[gchichester via AccessMonster.com]

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 - 7 lines]

Gil

If you just want to remove the text "SERVICE CONTRACT NUMBER " from all the
records containing it, leaving only "5035764A09", you could run an update
query. Either of these would work:

UPDATE yourtable
SET fieldname = MID([fieldname], 25)

or

UPDATE yourtable
SET fieldname = Replace([fieldname], "SERVICE CONTRACT NUMBER ", "")

It's obviously a Text field already, so it won't matter that the contract
numbers contain alphabetic characters; just *don't* try to change it to a
Number datatype field.

 

[J_Goddard via AccessMonster.com]

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

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
[quoted text clipped - 17 lines]

numbers contain alphabetic characters; just *don't* try to change it to a
Number datatype field.

 

[John W. Vinson]

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.

 

[gchichester via AccessMonster.com]

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,

[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.

 

[John W. Vinson]

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*";

 

[gchichester via AccessMonster.com]

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"
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*";

 

[gchichester via 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,
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*";

 

[J_Goddard via AccessMonster.com]

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

John,
Query window.
I think you can see the pics without a AM Account at
http://advenet.com/csx/photos/default.aspx
[quoted text clipped - 11 lines]

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