Removing a character in a field

  • Thread starter Thread starter Ramesh
  • Start date Start date
R

Ramesh

Hi,

I have a table which contains an asterisk marks in some of the fields in
some records. What would be the easiest way to get rid of them? Wherever
there are asterisk, I need them to be Null fields. I tried with Find and
replace with "" mark, but it doesnt seem to work. Am I missign something?

Thanks
Ramesh
 
Hi,

I have a table which contains an asterisk marks in some of the fields in
some records. What would be the easiest way to get rid of them? Wherever
there are asterisk, I need them to be Null fields. I tried with Find and
replace with "" mark, but it doesnt seem to work. Am I missign something?

Thanks
Ramesh
Click to expand...

Update the field to NULL:

UPDATE mytable
SET myfield = Null
WHERE myfield = "*";

John W. Vinson [MVP]
 
Hi,

I have a table which contains an asterisk marks in some of the fields in
some records. What would be the easiest way to get rid of them? Wherever
there are asterisk, I need them to be Null fields. I tried with Find and
replace with "" mark, but it doesnt seem to work. Am I missign something?

Thanks
Ramesh
Click to expand...

Hi - if i understand correctly, you want any strings containing an
asterisk replaced with null. It's a little tricky since the asterisk
has special meaning. I think you need to do an update query. Try:

UPDATE MYTABLE SET MYTABLE.MYFIELD = Null
WHERE (((MYTABLE.MYFIELD) Like "*[*]*"));
 
Hi,

I have a table which contains an asterisk marks in some of the fields in
some records. What would be the easiest way to get rid of them? Wherever
there are asterisk, I need them to be Null fields. I tried with Find and
replace with "" mark, but it doesnt seem to work. Am I missign something?

Thanks
Ramesh
Click to expand...

You write "an asterisk marks" plural, yet later you write "*" mark
(singular).

If only one asterisk in the field:

Create an Update query.
Update YourTable Set YourTable.FieldName = Null
Where YourTable.FieldName = "*";

If more than one * in the field, use:

Update YourTable Set YourTable.FieldName = Null
WHERE InStr([FieldName],"*") > "0";

Any record which contains one or more asterisks will be changed.
 
Thansk very much John, Kelsy and Fredgy.

The job got so easily done. Funny how i was struggling for so long.

Ramesh
 
Back
Top