Help with deleting specific characters within text fields in a table.

[Neal_F]

Hello Access World Forums!

I've just started a new job looking after a database of product record cards for making cables.

I'm trying to create an update query that will remove all the ***'s from my text fields in a table. The trouble i'm having is, the '*' character is used as the 'wildcard' character for searching for text 'like/similar’ to what you type in.

I understand that you can use [ square brackets ] to negate the wildcard function, but i'm still struggling as to how to get this query to work in my situation.

Here's some examples of the data and how I would like it to be:

Data in Field: How I would like it:
'** OPEN & CLOSE **' 'OPEN & CLOSE'
'** USE DN68 **' 'USE DN68'
'**** NOSE F1 TO EE7 SET @ 3.875" ****' 'NOSE F1 TO EE7 @ 3.875"'

There are lots of different situations for each cable and each one has its own text so the location of the ***’s will be different for each.

I need the query to search for any *'s in the text field and then delete them, but preserve the rest of the text stored in the field. I would also like any spaces removed before and after the text, but not in the middle of the text.

I hope I have made sense with my explanation. Any help would be much appreciated.

Please be aware I do not have advanced knowledge of access scripting, but have done some programming in visual basic several moons ago.

Neal

 

[MarkK]

Check out the Replace() function. In a query you can use it like ...

UPDATE tTestTable SET FieldValue = Replace(FieldValue, "***", "")

... which removes every instance of "***" from the field.

 

[John Big Booty]

The following code will do the trick;

[COLOR="DarkGreen"]'This portion of the code will remove all asterisks[/COLOR]
     Me.YourFieldName = [URL="http://www.techonthenet.com/access/functions/string/replace.php"]Replace[/URL](Me.YourFieldName, "*", "")

[COLOR="DarkGreen"]'This portion of the code will cycle until all leading spaces are removed
'Access will automagically remove all trailing spaces   [/COLOR]
     While [URL="http://www.techonthenet.com/access/functions/string/left.php"]Left[/URL](Me.YourFieldName, 1) = " "
          Me.YourFieldName= Replace(Me.YourFieldName, " ", "", 1, 1)
     Wend

 

[Neal_F]

Thank you for the speedy reply! I haven't had a chance to test the code yet at work, as I get stuck on phone all day answering customer queries.

Will let you know how I get on once I get a chance...