Replace function

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

EMILYTAN - 17 Jul 2007 01:10 GMT

Is there any way to delete a string out of many string under the same column?
For example:-

In a serial number column consists of MY111, MY 123, MY456
If user specify to delete MY456, it will delete MY456 while retaining the
value of MY111, MY123 using update statement?

How am I suppose to use replace function..?

For example:-

mySQL7 = "UPDATE WIPRawDetails replace([SerialNumber]'" & strSerialNumber1 &
"',"")WHERE JobNumber= '" & Me.txtJobNumber & "' and PartNumber = '" &
strPartNumber & "'"
DoCmd.RunSQL mySQL7

Reply to this Message

John W. Vinson - 17 Jul 2007 07:08 GMT

>Is there any way to delete a string out of many string under the same column?
>For example:-
>
>In a serial number column consists of MY111, MY 123, MY456
>If user specify to delete MY456, it will delete MY456 while retaining the
>value of MY111, MY123 using update statement?

You're storing *three serial numbers* in one field? That is a rather bad idea!
The problem you're having is just one reason why it's a bad idea.

>How am I suppose to use replace function..?
>
[quoted text clipped - 4 lines]
>strPartNumber & "'"
>DoCmd.RunSQL mySQL7

You can use

mySQL7 = "UPDATE WIPRawDetails SET [SerialNumber] =
Replace([SerialNumber] & ",", """ & strSerialNumber1 & ",""")
WHERE JobNumber= '" & Me.txtJobNumber & "' and PartNumber = '" &
strPartNumber & "'"

This assumes that the job number and part number criterion reliably identifies
the records to be fixed. I'm appending a comma to the end of the string in
case strSerialNumber1 comes last in the string - which would cause it to be
missed in the replace.

You would REALLY REALLY be *much* better off normalizing your tables, rather
than storing multiple values in one field!!!

John W. Vinson [MVP]

Reply to this Message

EMILYTAN - 17 Jul 2007 07:45 GMT

Ya...thanks for your advice....
I have think twice..it is not good to do it..So, I decide not to do..
But since you have given me the solution, I will learn on the replace
function as well...
Thanks

>>Is there any way to delete a string out of many string under the same column?
>>For example:-
[quoted text clipped - 28 lines]
>
> John W. Vinson [MVP]

Reply to this Message