Replace function

[EMILYTAN via AccessMonster.com]

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

 

[John W. Vinson]

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

For example:-


mySQL7 = "UPDATE WIPRawDetails replace([SerialNumber]'" & strSerialNumber1 &
"',"")WHERE JobNumber= '" & Me.txtJobNumber & "' and PartNumber = '" &
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]

 

[EMILYTAN via AccessMonster.com]

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

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]